Error while converting Datatype due to DBNulls

Question

I am trying to load data using my c# application by calling a stored procedure from one database and would then need to insert that data into another database. I am planning to using SQL bulk insert for it. I did propose SSIS but unfortunately they don't have the software and infrastructure for it. I need to convert the datatype for some of my columns as it blows up when I do bulk insert into destination table. I am using a function to the conversion but unfortunately getting an error when trying to convert DBNull value. When I try to check for DBNull value prior to conversion it doesn't seem to enter the If condition block. Could somebody tell me where am I going wrong?

using (SqlConnection con = new SqlConnection(_SourceConnectionString))
{
    // con.Open();

    // Create a table with some rows. 
    DataTable table = new DataTable();

    var cmd = new SqlCommand("[EMR].[spOrderDailySummary]", con);
    var da = new SqlDataAdapter(cmd);

    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@OrderDate", SqlDbType.DateTime).Value = _businessDate;

    try
    {
        // LogManager.Log(LogSeverity.Info, $"Initiating report {rgc.ReportType}.");
        da.Fill(table);

        table.ConvertColumnType("Original_Qty", typeof(int));
        table.ConvertColumnType("Remaining_Qty", typeof(int));
        table.ConvertColumnType("Price", typeof(decimal));
        table.ConvertColumnType("OrderDate", typeof(DateTime));
        table.ConvertColumnType("Valid_to_Value", typeof(DateTime));
        table.ConvertColumnType("ClientID", typeof(int));
        table.ConvertColumnType("Trigger_Price", typeof(decimal));
        table.ConvertColumnType("Investment_Decision_Within_Firm", typeof(int));
        table.ConvertColumnType("Order_Priority_Timestamp", typeof(DateTime));
        table.ConvertColumnType("Transaction_Time", typeof(DateTime));
        table.ConvertColumnType("NewOrdDateTime", typeof(DateTime));

        StartDataMigration(table);
    }
}

Function that does the conversion

  public static void ConvertColumnType<T>(this DataTable dt, string columnName, Type newType) where T : Type, IConvertible
{
    using (DataColumn dc = new DataColumn(columnName + "_new", newType))
    {
        // Add the new column which has the new type, and move it to the ordinal of the old column
        int ordinal = dt.Columns[columnName].Ordinal;
        dt.Columns.Add(dc);
        dc.SetOrdinal(ordinal);

        // Get and convert the values of the old column, and insert them into the new
        foreach (DataRow dr in dt.Rows)
            if (dr[dc.ColumnName] != DBNull.Value)
            {
                dr[dc.ColumnName] = Convert.ChangeType(dr[columnName], newType);
            }

        // Remove the old column
        dt.Columns.Remove(columnName);

        // Give the new column the old column's name
        dc.ColumnName = columnName;
    }
}

Answer 1

This line is problematic:

if (dr[dc.ColumnName] != DBNull.Value)

The reason is two-fold. First, weird things can happen with comparing to DBNull.Value using the != and == operators. This would be better:

if (!DBNull.Value.Equals(dr[dc.ColumnName]))

Additionally, the code above is looking at the new column name, which doesn't yet have a value set. We still need to reference the old column at this point, and so we want this:

if (!DBNull.Value.Equals(dr[columnName]))