Column does not allow DBNull.Value - No KeepNulls - Proper Column Mappings

Question

I am using c# with .NET 4.5.2, pushing to SQL Server 2017 14.0.1000.169

In my database, I have a table with a DateAdded field, of type DateTimeOffset.

I am attempting to BulkCopy with the following code:

private Maybe BulkCopy(SqlSchemaTable table, System.Data.DataTable dt, bool identityInsertOn)
{
    try
    {
        var options = SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction; //  | SqlBulkCopyOptions.CheckConstraints; // Tried CheckConstraints, but it didn't change anything.
        if (identityInsertOn) options |= SqlBulkCopyOptions.KeepIdentity;
        using (var conn = new SqlConnection(_connString))
        using (var bulkCopy = new SqlBulkCopy(conn, options, null))
        {
            bulkCopy.DestinationTableName = table.TableName;
            dt.Columns.Cast<System.Data.DataColumn>().ToList()
                .ForEach(x => bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(x.ColumnName, x.ColumnName)));

            try
            {
                conn.Open();
                bulkCopy.WriteToServer(dt);
            }
            catch (Exception ex)
            {
                return Maybe.Failure(ex);
            }
        }
    }
    catch (Exception ex)
    {
        return Maybe.Failure(ex);
    }

    return Maybe.Success();
}

The two possible reasons I know of for the does not allow DBNull error are:

1. Columns are in the wrong order, which is solved by either putting them in the same order as their Database Ordinal, or by performing a Column Mapping.
2. KeepNulls is enabled, and DBNull.Value (or null?) are set in the DataTable.

But I am Mapping correctly and NOT ever setting KeepNulls.

Yet I am receiving the error:

Column DateAdded does not allow DBNull.Value

EDIT I also tried just NOT SETTING anything, including null, DBNull.Value, and DefaultValue... just literally not setting that column at all.

Also, if I Remove the DateAdded column from the DataTable, it Works. But I don't want that. Of the 100,000 records, maybe 20 of them have data. So in my batches of 500, sometimes None have data in the DateAdded field, sometimes one or two have data.

So I'd like to keep the column in my DataTable but let it use the DefaultValue.

One last note: I have alternated between setting the DataColumn's Value to DBNull.Value versus dt.Columns[x.ColumnName].DefaultValue. Both ways give the same error.

Edit 2

This is the code I'm using to populate the data in my Data Table:

foreach (var column in table)
{
    System.Data.DataRow newRow = dt.NewRow();
    foreach (var field in column)
    {
        if (!IsNull(field.Value) && !IsEmptyDateOrNumber(field.ColumnType, field.Value))
        {
            // For DateAdded, this is not hit on the first batch, though there are columns Before and After DateAdded on the same row which do have value.
            // But it WILL be hit once or twice a few batches later.  So I don't want to completely remove the definition from the DataTable.
            newRow[field.ColumnName] = field.Value;
        }
        else
        {
            // newRow[field.ColumnName] = dt.Columns[field.ColumnName].DefaultValue;
            // newRow[field.ColumnName] = DBNull.Value;
            // dt.Columns[field.ColumnName].AllowDBNull = true;
        }
    }
    dt.Rows.Add(newRow);
}

IsNull() returns TRUE if the value is null or the string "null", as is required for my business requirements.

IsEmptyDateOrNumber() will return TRUE if the field is a numeric or date type, and the value is null or empty "". Because while empty is valid for many string-like fields, it is never a valid numeric value.

The condition to assign the field a value is hit exactly 0 percent of the time for this particular column. Thus nothing is set.

Answer 1

Simply put, you can't do what you want. The best reference for how the BulkCopy works with default values is This Answer by Rutzky.

The problem is, BulkCopy includes a step where it queries the target database and determines the structure of the table. If it determines that the target column is NOT NULLable, and you are passing null or DBNull, it throws an exception before even trying to pass the data.

If you use SQL Profiler, you'll see the BCP Calls, but not the data (The data would never show up anyway). All you'll see is the call to define the Column List and Flags.

When the BulkCopy finally decides to pass the data along. If the column exists, and the field is NULLable, and the value is DBNull.Value, and the column has a Default Value; Bulk Copy essentially passes the DEFAULT flag along for that column. But some decision was made such that those conditions, except if the field is NOT NULLable, that the default value should not be used and instead an exception should be thrown.

As far as I can tell this is a bug or oversight by Microsoft.

The common workaround, as some other answers state, is to just handle those values manually by calculating what the value should be in-code. Of course, if you calculate default values, then the DBA changes the actual SQL Default Value for a field, your systems won't match. The next step is to add a subsystem to your system that queries and/or tracks/caches the currently specified default values from the SQL Server you're hitting, and assigning those. That's way more work than should be required.

TLDR: You can't do what you want. But there are sub-optimal workarounds that others have specified.

Answer 2

This combination does not work for SqlBulkCopy:

1. The date field in the SQL table is configured as not null.
2. Your C# DataTable has records with unset date values (or has set values but they are not actual date/time values).

This combination does work:

1. The date field in the SQL table is configured to allow nulls but use a default value.
2. Your C# DataTable has records with unset date values (SQL will use the default value when the date field is not specified in the C# DataTable).

If the date field in your table MUST be configured to not accept null, then you have more work to do (create a staging table that accepts nulls, bulk insert into the staging table, call a stored proc that you write that inserts from the staging table into your regular table).

The simplest path to what I think you're looking for is this:

Your table should allow NULL for the date field but with a default:

CREATE TABLE [dbo].[MyTable](
    [Field1] [varchar](50) NULL,
    [MyDateField] [datetime] NULL,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Table_1_MyDateField] DEFAULT (getdate()) FOR [MyDateField]
GO

Then in C#:

        DataTable dt = new DataTable("dbo.MyTable");

        dt.Columns.Add("Field1");
        dt.Columns.Add("MyDateField");

        DataRow row1 = dt.NewRow();
        row1["Field1"] = "test row 1";
        row1["MyDateField"] = DateTime.Now; //specify a value for the date field in C#
        dt.Rows.Add(row1);

        DataRow row2 = dt.NewRow();
        row2["Field1"] = "test row 2";
        //do not specify a value for the date field - SQL will use the default value
        dt.Rows.Add(row2);

        do the bulk copy

Answer 3

Of the 100,000 records, maybe 20 of them have data. So in my batches of 500, sometimes None have data in the DateAdded field, sometimes one or two have data.

I'm guessing some of your records have nulls in DateAdded, while the DateAdded is configured to not aceept nulls.

To overcome this, you have multiple ways to do it :

1. (Easier one) is just to alter column DateAdded to accept null.
2. Assign a default value to column DateAdded (from MSSQL).
3. Manage the nulls on the records from your code.

If you don't have permissions or your work requirment specified that DateAdded cannot accept nulls or have a default value from MSSQL OR 1 & 2 didn't solve your problem. Then, you can manage the nulls on DateAdded column on each batch before you copy it to the server.

In this case, in your code, while you're populating the data, you should add a condition to this column with something like this :

if(field.ColumnName == "DateAdded")
{
    // Do something to handle this column if it's null 

    // Set a default value 
    DateTimeOffset dtoffset = DateTimeOffset.Parse("2019-01-01 00:00:00.0000000 -00:00", CultureInfo.InvariantCulture); // change it to the required offset 
    string defaultDateAdded = dtoffset.ToString("yyyy-MM-dd HH:mm:ss.fffffff zzz", CultureInfo.InvariantCulture);               

    // Add the default value 
    newRow[field.ColumnName] = defaultDateAdded;
}
else
{
    // Do something to handle the rest of columns if they're null 

}   

then when you're done from handling all columns, you just add the new row into your datatable, then copy the finalized datatable to the server.