SqlBulkCopy into table that Default column values fails when source DataTable row has DBNull.Value

c# default-constraint null sqlbulkcopy sql-server

Question

Version: Here is my response.

I've defined a table as follows:

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 possessDataTable contains columns whose names and data types correspond to those of the table. TheDataTable contains filled outDBNull.Value in CreatedOn , LastUpdatedOn and LastUpdatedUser . ReferenceID has already been produced. I get the following error when I call the following code.

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 to BulkCopy table csvrf_References
System.InvalidOperationException: Column 'CreatedOn' does not allow DBNull.Value.
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)

I've searched everywhere, but I can't seem to locate a solution. TheSqlBulkCopy Despite its claims to the contrary, class seems to ignore default settings. Why am I doing this wrong?

1
6
5/23/2017 12:17:52 PM

Accepted Answer

You shouldn't even be transmitting the field for part 1, "field that is NOT NULL with a DEFAULT," in the first place. You shouldn't map it. It is not necessary to modify that field to allow NULL values alone for this.

In order to get the default value when handing in DbNull, part 2's "field that is NULL with a DEFAULT" requires that will work. Value, as long as SqlBulkCopyOptions is not set toKeepNulls else a real database will be inserted.NULL .

Given the ambiguity surrounding the SqlBulkCopyOption ofKeepNulls check out its definition:

Preserve null values in the destination table regardless of the settings for default values. When not specified, null values are replaced by default values where applicable.

In other words, a DataColumn set toDbNull.Value database will be insertedNULL even if there is a DEFAULT CONSTRAINT for the column, theKeepNulls options are listed. There is no mention of it in your code. This brings us to the second clause, which statesDbNull.Value Where appropriate, "default values" are used in lieu of values. In this case, "applicable" denotes the presence of a DEFAULT CONSTRAINT on the column. Therefore, if a DEFAULT CONSTRAINT is present, a non-DbNull.Value Value will be submitted as-is.DbNull.Value Translating to the SQL keyword is shouldDEFAULT . When used in an INSERT statement, this keyword is understood to accept the value of the DEFAULT constraint. Of course, it's also conceivableSqlBulkCopy if the field was set to NULL for that row, might simply leave that field out of the column list when issuing individual INSERT queries, which would take the default value. The outcome is the same in either caseā€”it performs as you anticipated. And my testing demonstrates that it indeed works in this way.

To make the difference clear:

  • If a database field has the valueNOT NULL When is defined with a DEFAULT CONSTRAINT, your choices are:

    • It will not pick up the default value if the field is passed, hence it can never be changed toDbNull.Value

    • Do not pass at all in the field (i.e., it will take up the DEFAULT value), which may be achieved in one of two ways:

      • Do not include it in the DataTable, query, DataReader, or other source that is being passed in; if you do, you may not need to define theColumnMappings any collection

      • You must identify the source if the field is present.ColumnMappings so that you may omit that field from the mappings.

    • Whether or not to set,KeepNulls does not alter the behavior mentioned above.

  • If a database field has the valueNULL When is defined with a DEFAULT CONSTRAINT, your choices are:

    • Do not send any information in the field (i.e., it will use the DEFAULT value), which may be achieved in one of two ways:

      • Do not include it in the DataTable, query, DataReader, or other source that is being passed in; if you do, you may not need to define theColumnMappings any collection

      • You must identify the source if the field is present.ColumnMappings so that you may omit that field from the mappings.

    • Enter a value in the field that is notDbNull.Value if so, it will then be set to this value rather than the DEFAULT value.

    • In the field, pass asDbNull.Value if so, then the outcome depends on whether or notSqlBulkCopyOptions is being submitted and has been designated toKeepNulls :

      • KeepNulls Zzz-197-Zzz will choose the DEFAULT value if it is set.

      • KeepNulls The field will be left with is set toNULL


Here is a quick test to determine theDEFAULT keyword effective

--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
12
11/20/2014 6:25:06 PM

Popular Answer

The reason for the "oeSQLBulkCopy column does not allow DbNull.value" problem is because the source and destination tables' column orders disagree.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow