SqlBulkCopy to the table that default column values ​​are not executed when the original DataTable row is DBNull.Value

Question

Update: Here is my solution

I have a table defined as:

CREATE TABLE [dbo].[csvrf_References] ( [Ident] [int] IDENTITY(1,1) NOT NULL, [ReferenceID] [uniqueidentifier] NOT NULL DEFAULT (newsequentialid()), [Type] [nvarchar](255) NOT NULL, [Location] [nvarchar](1000) NULL, [Description] [nvarchar](2000) NULL, [CreatedOn] [datetime] NOT NULL DEFAULT (getdate()), [LastUpdatedOn] [datetime] NOT NULL DEFAULT (getdate()), [LastUpdatedUser] [nvarchar](100) NOT NULL DEFAULT (suser_sname()), CONSTRAINT [PK_References] PRIMARY KEY NONCLUSTERED ([ReferenceID] ASC) ) ON [PRIMARY] 

I have a DataTable with columns that match table column names and data types. DataTable populated with DBNull.Value in CreatedOn , LastUpdatedOn and LastUpdatedUser . ReferenceID already generated. When I call the following code, I get the error below.

the code:

 SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, bulkCopyTran); bulkCopy.DestinationTableName = table.TableName; bulkCopy.ColumnMappings.Clear(); foreach (DataColumn col in table.Columns) bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName); bulkCopy.WriteToServer(table); 

Error:

Error trying BulkCopy csvrf_References table
System.InvalidOperationException: column "CreatedOn" does not allow DBNull.Value.
in System.Data.SqlClient.SqlBulkCopy.ConvertValue (object value, metadata _SqlMetaData, Boolean isNull, Boolean & isSqlType, Boolean & coercedToDataFeed)

I looked at everything and I can not find the answer for this. The SqlBulkCopy class does not seem to respect the default values, even if it says so. What am I doing wrong here?

Answer 1

For part 1, "a field that is not NULL with DEFAULT", you should not send the field in the first place. It should not be displayed. There is no need to change this field to accept NULL just for that.

For part 2, a "field with NULL with DEFAULT", which will work to get the default value when passing to DbNull.Value, if you do not have SqlBulkCopyOptions set to KeepNulls , otherwise it will insert the actual NULL database.

Since there is some confusion regarding SqlBulkCopyOption KeepNulls , consider its definition:

Store null values ​​in the destination table, regardless of the settings for the default values. If not specified, null values ​​are replaced with default values.

This means that the DataColumn set to DbNull.Value will be inserted as a NULL database, even if the column has DEFAULT CONSTRAINT if the KeepNulls parameter is KeepNulls . It is not listed in your code. This leads to the second part, in which the values ​​of DbNull.Value are replaced with "default values", where applicable. Here, "applicable" means that the column has a DEFAULT CONSTRAINT defined on it. Therefore, when a DEFAULT CONSTRAINT exists, a value of non DbNull.Value will be sent as is, while DbNull.Value should translate to the SQL DEFAULT keyword. This keyword is interpreted in the INSERT statement as taking the value of the DEFAULT constraint. Of course, it is also possible that SqlBulkCopy , if you issue separate INSERT statements, can simply leave this field outside the column list if this row is set to NULL, which allows you to get the default value. In any case, the end result is that it works as you expected. And my testing shows that it really works that way.

To find out about the difference:

• If the field in the database is set to NOT NULL , and it is set to FINAL DEFAULT, then your options are:

  • Go to the field (i.e. it will not get the DEFAULT value), in which case it can never be set to DbNull.Value

  • Do not go into the field at all (that is, it will receive a DEFAULT value), which can be done with:

    • Do not use it in a DataTable or query or DataReader or anything else sent as a source, in which case you may not need to specify the ColumnMappings collection at ColumnMappings

    • If the field is in the source, you must specify the ColumnMappings collection ColumnMappings that you can leave this field outside the mappings.

  • Setting or missing KeepNulls settings KeepNulls not change the above behavior.

• If the field in the database is set to NULL and the DEFAULT DESIGN is set on it, then your parameters:

  • Do not go into the field at all (that is, it will receive a DEFAULT value), which can be done with:

    • Do not use it in a DataTable or query or DataReader or anything else sent as a source, in which case you may not need to specify the ColumnMappings collection at ColumnMappings

    • If the field is in the source, you must specify the ColumnMappings collection ColumnMappings that you can leave this field outside the mappings.

  • Pass a value in the field that is not equal to DbNull.Value , in which case it will be set to this value and will not receive the value DEFAULT

  • Go to the field as DbNull.Value , and in this case the effect is determined by whether or not SqlBulkCopyOptions is SqlBulkCopyOptions and SqlBulkCopyOptions set:

    • KeepNulls not set, select DEFAULT

    • KeepNulls will be set to NULL

---

Here is a simple test to see how the DEFAULT keyword works:

 --DROP TABLE ##DefaultTest; CREATE TABLE ##DefaultTest ( Col1 INT, [CreatedOn] [datetime] NOT NULL DEFAULT (GETDATE()), [LastUpdatedOn] [datetime] NULL DEFAULT (GETDATE()) ); INSERT INTO ##DefaultTest (Col1, CreatedOn) VALUES (1, DEFAULT); INSERT INTO ##DefaultTest (Col1, LastUpdatedOn) VALUES (2, DEFAULT); INSERT INTO ##DefaultTest (Col1, LastUpdatedOn) VALUES (3, NULL); INSERT INTO ##DefaultTest (Col1, LastUpdatedOn) VALUES (4, '3333-11-22'); SELECT * FROM ##DefaultTest ORDER BY Col1 ASC; 

Results:

 Col1 CreatedOn LastUpdatedOn 1 2014-11-20 12:34:31.610 2014-11-20 12:34:31.610 2 2014-11-20 12:34:31.610 2014-11-20 12:34:31.610 3 2014-11-20 12:34:31.610 NULL 4 2014-11-20 12:34:31.613 3333-11-22 00:00:00.000 

Answer 2

Reading the documentation regarding SqlBulkCopy , especially SqlBulkCopyOptions , I would draw the same conclusion as you: SQL Server should be "smart" enough to use the default constraint, where applicable, especially since you do not use the SqlBulkCopyOptions.KeepNulls attribute.

However, in this case, I suspect that the documentation is subtly incorrect; if not mistaken, this, of course, is misleading.

As you noticed, with a non-empty field with a default constraint (in this case GetDate() ) SqlBulkCopy fails with the above error.

As a test, try creating a second table that mimics the first, but this time make the CreatedOn and LastUpdatedOn null. In my tests, using the default parameters ( SqlBulkCopyOptions.Default ), the process works without errors and CreatedOn and LastUpdatedOn , both have the correct DateTime value populated in the table, despite the DataTable values ​​for these fields were DBNull.Value .

As another test, using the same table with zero fields, run SqlBulkCopy only this time using the SqlBulkCopyOptions.KeepNulls attribute. I suspect that you will see the same results as me, that is, CreatedOn and LastUpdatedOn are zero in the table.

This behavior is similar to executing the T-SQL “vanilla” statement to insert data into a table.

As an example, you can use the source table (fields with a non-empty value) if you execute

 INSERT INTO csvrf_References ([Type], [Location], [Description], [CreatedOn], [LastUpdatedOn], [LastUpdatedUser]) VALUES ('test', 'test', 'test', null, null, null) 

you will get a similar error regarding null values ​​that are not allowed in the table.

However, if you omit non-zero fields, the SQL Server statement uses the default restrictions for these fields from the statement:

 INSERT INTO csvrf_References ([Type], [Location], [Description] VALUES ('test', 'test', 'still testing') 

Based on this, I would suggest either to make the fields nullable in the table (in my opinion, not a good option), or to use the "intermediate" table for the SqlBulkCopy process (where the fields are NULL and have similar default values ​​in place). After the data is in the staging table, run the second statement to move the data to the actual destination destination table.

Answer 3

"The SQLBulkCopy column does not resolve the DbNull.value error" because the source and destination tables have a different column order.