当源DataTable行具有DBNull.Value时，SqlBulkCopy进入表，默认列值失败

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 the table column names and data types. The DataTable is filled out with DBNull.Value in CreatedOn, LastUpdatedOn and LastUpdatedUser. ReferenceID is already generated. When I call the following code I get the error below.

我有一个DataTable,其列与表列名和数据类型相匹配。 DataTable使用CreatedOn,LastUpdatedOn和LastUpdatedUser中的DBNull.Value填充。 ReferenceID已生成。当我调用以下代码时,我得到以下错误。

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)

尝试BulkCopy表时出错csvrf_References System.InvalidOperationException:列'CreatedOn'不允许DBNull.Value。 at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value,_SqlMetaData metadata,Boolean isNull,Boolean&isSqlType,Boolean&coercedToDataFeed)

I have looked all over and I can't seem to find an answer for this. The SqlBulkCopy class seems not to honor default values even though it says it does. What am I doing wrong here?

我看了一遍,似乎无法找到答案。 SqlBulkCopy类似乎不尊重默认值,即使它说它也是如此。我在这做错了什么?

3 个解决方案

#1

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

对于第1部分,“带有DEFAULT的非NULL字段”,您不应该首先发送字段。它不应该映射。没有必要为此更改该字段以接受NULL。

For part 2, "field that is NULL with a DEFAULT", that will work to get the default value when passing in DbNull.Value, as long as you don't have the SqlBulkCopyOptions set to KeepNulls, else it will insert an actual database NULL.

对于第2部分,“带有DEFAULT为NULL的字段”,只要您没有将SqlBulkCopyOptions设置为KeepNulls,它将在传递DbNull.Value时获取默认值,否则它将插入实际数据库空值。

Since there is some confusion about the SqlBulkCopyOption of KeepNulls, let's look at its definition:

由于对KeepNulls的SqlBulkCopyOption存在一些疑惑,让我们来看看它的定义:

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.

无论默认值的设置如何,都保留目标表中的空值。未指定时,null值将替换为适用的默认值。

This means that a DataColumn set to DbNull.Value will be inserted as a database NULL, even if the column has a DEFAULT CONSTRAINT, if the KeepNulls option is specified. It is not specified in your code. Which leads to the second part that says DbNull.Value values are replaced by "default values" where applicable. Here "applicable" means that the column has a DEFAULT CONSTRAINT defined on it. Hence, when a DEFAULT CONSTRAINT exists, a non-DbNull.Value value will be sent in as is while DbNull.Value should translate to the SQL keyword DEFAULT. This keyword is interpreted in an INSERT statement as taking the value of the DEFAULT constraint. Of course, it is also possible that SqlBulkCopy, if issuing individual INSERT statements, could simply leave that field out of the column list if set to NULL for that row, which would pick up the default value. In either case, the end result is that it works as you expected. And my testing shows that it does indeed work in this manner.

这意味着设置为DbNull.Value的DataColumn将作为数据库NULL插入,即使该列具有DEFAULT CONSTRAINT,如果指定了KeepNulls选项。它没有在您的代码中指定。这导致第二部分说DbNull.Value值被适用的“默认值”替换。这里“适用”表示该列上定义了DEFAULT CONSTRAINT。因此,当存在DEFAULT CONSTRAINT时,将按原样发送非DbNull.Value值,而DbNull.Value应转换为SQL关键字DEFAULT。此关键字在INSERT语句中被解释为获取DEFAULT约束的值。当然,如果发出单独的INSERT语句,SqlBulkCopy也可能只是将该字段留出列列表(如果为该行设置为NULL),这将获取默认值。在任何一种情况下,最终结果是它按预期工作。我的测试表明它确实以这种方式工作。

To be clear about the distinction:

要清楚区别:

• If a field in the database is set to NOT NULL and has a DEFAULT CONSTRAINT defined on it, your options are:

  如果数据库中的字段设置为NOT NULL并且在其上定义了DEFAULT CONSTRAINT,则您的选项为:

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

    传入字段(即它不会获取DEFAULT值),在这种情况下,它永远不能设置为DbNull.Value

  • Do not pass in the field at all (i.e. it will pick up the DEFAULT value), which can be accomplished by either:

    根本不要传入字段(即它将获取DEFAULT值),这可以通过以下任一方式完成:

    • Do not have it in the DataTable or query or DataReader or whatever is being sent in as the source, in which case you might not need to specify the ColumnMappings collection at all

      不要在DataTable或查询或DataReader中或者作为源发送的任何内容中使用它,在这种情况下,您可能根本不需要指定ColumnMappings集合

    • If the field is in the source, then you must specify the ColumnMappings collection so that you can leave that field out of the mappings.

      如果该字段位于源中,则必须指定ColumnMappings集合,以便可以将该字段保留在映射之外。

  • Setting, or not setting, KeepNulls does not change the above noted behavior.

    设置或不设置KeepNulls不会改变上述行为。

• If a field in the database is set to NULL and has a DEFAULT CONSTRAINT defined on it, your options are:

  如果数据库中的字段设置为NULL并且在其上定义了DEFAULT CONSTRAINT,则您的选项为:

  • Do not pass in the field at all (i.e. it will pick up the DEFAULT value), which can be accomplished by either:

    根本不要传入字段(即它将获取DEFAULT值),这可以通过以下任一方式完成:

    • Do not have it in the DataTable or query or DataReader or whatever is being sent in as the source, in which case you might not need to specify the ColumnMappings collection at all

      不要在DataTable或查询或DataReader中或者作为源发送的任何内容中使用它,在这种情况下,您可能根本不需要指定ColumnMappings集合

    • If the field is in the source, then you must specify the ColumnMappings collection so that you can leave that field out of the mappings.

      如果该字段位于源中,则必须指定ColumnMappings集合,以便可以将该字段保留在映射之外。

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

    传入设置为不是DbNull.Value的值的字段,在这种情况下,它将被设置为此值而不会获取DEFAULT值

  • Pass in the field as DbNull.Value, in which case the effect is determined by whether or not SqlBulkCopyOptions is being passed in and has been set to KeepNulls:

    在字段中传递为DbNull.Value,在这种情况下,效果取决于是否传入SqlBulkCopyOptions并已设置为KeepNulls:

    • KeepNulls is not set will pick up the DEFAULT value

      未设置KeepNulls将获取DEFAULT值

    • KeepNulls is set will leave the field set to NULL

      设置KeepNulls将字段设置为NULL

---

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

这是一个简单的测试,用于查看DEFAULT关键字的工作原理:

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

#2

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

阅读有关SqlBulkCopy的文档,特别是SqlBulkCopyOptions,我会得出你所做的相同结论:SQL Server应该“足够”以便在适用的地方使用默认约束,特别是因为你没有使用SqlBulkCopyOptions.KeepNulls属性。

However, in this case I suspect the documentation is subtly incorrect; if not incorrect it is certainly misleading.

但是,在这种情况下,我怀疑文档是微妙的错误;如果不是错误的话肯定会产生误导。

As you have observed, with a non-nullable field with a default constraint (in this case GetDate()) the SqlBulkCopy fails with the aforementioned error.

正如您所观察到的,对于具有默认约束的非可空字段(在本例中为GetDate()),SqlBulkCopy会因上述错误而失败。

As a test, try creating a second table that mimics the first, but this time make the CreatedOn and LastUpdatedOn fields nullable. In my tests, using the default options (SqlBulkCopyOptions.Default) the process works without error and CreatedOn and LastUpdatedOn both have the correct DateTime value populated in the table despite the fact that the DataTable's values for those fields were DBNull.Value.

作为测试,尝试创建第二个模仿第一个表的表,但这次使CreatedOn和LastUpdatedOn字段可以为空。在我的测试中,使用默认选项(SqlBulkCopyOptions.Default)的过程而不会出现错误和CreatedOn和LastUpdatedOn都有尽管该数据表的这些字段的值是DBNull.Value在表中填写正确的日期时间值。

As yet another test, using the same (nullable fields) table, perform the SqlBulkCopy only this time use the SqlBulkCopyOptions.KeepNulls attribute. I suspect you will see the same results I did, that is, CreatedOn and LastUpdatedOn are both null in the table.

作为另一个测试,使用相同(可空字段)表,仅执行SqlBulkCopy,这次使用SqlBulkCopyOptions.KeepNulls属性。我怀疑你会看到我所做的相同结果,即CreatedOn和LastUpdatedOn在表中都是null。

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

此行为类似于执行“vanilla”T-SQL语句以将数据插入表中。

Using the original table (non-nullable fields) as an example, if you execute

如果执行,使用原始表(不可空字段)作为示例

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

you will receive a similar error regarding null values not being allowed in the table.

您将收到有关表中不允许的空值的类似错误。

However, if you omit the non-nullable fields from the statement SQL Server uses the Default Constraints for those fields:

但是,如果从语句中省略不可为空的字段,SQL Server将对这些字段使用默认约束:

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

Based on this, I would suggest either making the fields nullable in the table (not really a great option in my opinion) OR using a "staging" table for the SqlBulkCopy process (where the fields are nullable and have a similar default constraint in place). Once the data is in the staging table execute a second statement to move the data into the actual final destination table.

基于此,我建议在表中使字段可以为空(在我看来并不是一个很好的选项)或者为SqlBulkCopy进程使用“staging”表(其中字段可以为空并且具有类似的默认约束) )。一旦数据在临时表中,执行第二个语句将数据移动到实际的最终目标表中。

#3

“SQLBulkCopy column does not allow DbNull.value” error is due to source and destination table has different column order.

“SQLBulkCopy列不允许DbNull.value”错误是由于源表和目标表具有不同的列顺序。