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.

Solution 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.

Solution 2

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.

Solution 3

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

Comments

[Suamere]

I previously marked this as an answer, but have un-marked it. What I did was completely skip the Assignment of a Value for that column, though the column still existed in the DataTable definition. It seemed to work, but turned out I hadn't removed my prior test code that had actually been removing the column from the DataTable definition. So it was a false positive on fixing the issue. I'll put some time into looking at the FireTriggers and re-apply this as the answer if that helps solve the issue.

[Suamere]

I removed FireTriggers and it still said Column DateAdded does not allow DBNull.Value. But thank you for the path for me to attempt.

[toni]

Does your table have an insert trigger? Not certain but I believe a insert from any source will fire a trigger. Another thing to verify is that the field in your table does allow nulls. From your description however, it sounds like it certainly does.

[Suamere]

No, the field in my table is NOT NULL. e.g.: Does not allow nulls. That's why I'm trying to send the signal to use "Default" value. I just want to tell the Bulk Insert to use the Default Value for that Column. And, just like with a regular INSERT statement, if I omit the column, SQL has no choice but to use DEFAULT, and it works. However, I do NOT want to Omit the column. Because in bulk of dozens of thousands, batched by 500, occasionally there is a value.

[toni]

I realized this information is missing - what value do you want to be in the date field in the sql table when there is no valid date/time? null? some default? the min sql date (which is '1900-01-01 00:00:00.000')?

[Suamere]

This is pretty much what I'm doing right now to get around it. You're right that I don't have the access to change the target database. Your answer solves the situation, but not the exact solution to the exact question. This is great information for somebody else who is looking for this path, but I'm hoping there are other options and I'm hoping those options will appear by tomorrow.

[iSR5]

@Suamere I think the appropriate option for your case is to solve it from the source or the target databases, since they have schema differences. So, either the source should be set to not have nulls, or your target table should accept it. This would solve it permanently.

[Suamere]

Whatever the specified default is. If I Create Table to Script, it is GETUTCDATE(). But this question isn't limited to this table and column. So I don't want to hard-code that. I just want it to use the default value.

[Ivan Stoev]

@Suamere After digging into the SqlBulkCopy class source code, I have to say that unfortunately there is a code which prevents passing null value to a database table column which does not allow NULL. They are retrieving the metadata information for the destination table from the database, and there is no mapping/controlling option to avoid that check. Which is bug in my opinion, but it is what it is - so no exact solution to the exact question, you have to accept some of the workarounds.

[Suamere]

Thanks for the update, Ivan. I actually found that same thing. I also used SQL Profiler and found that I can't see the resulting call, other than some basic BCP metadata. I may have to just suck it up and force a code-calculated Default.