当源 DataTable 行具有 DBNull.Value 时，SqlBulkCopy 到默认列值失败的表中

问题描述

更新:这是我的解决方案

我有一个表定义为:

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]

我有一个 DataTable，其中的列与表列名称和数据类型相匹配.DataTable 在 CreatedOn、LastUpdatedOn 和 LastUpdatedUser 中用 DBNull.Value 填充.ReferenceID 已经生成.当我调用以下代码时，出现以下错误.

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.

代码:

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

错误:

尝试批量复制表时出错 csvrf_References
System.InvalidOperationException:列CreatedOn"不允许 DBNull.Value.
在 System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)

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)

我已经看遍了，似乎无法找到答案.SqlBulkCopy 类似乎不尊重默认值，即使它说它确实如此.我在这里做错了什么?

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?

推荐答案

对于第 1 部分具有默认值的 NOT NULL 字段"，您首先不应发送该字段.它不应该被映射.无需为此更改该字段以接受 NULL.

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.

对于第 2 部分，带有 DEFAULT 的 NULL 字段"，将在传入 DbNull.Value 时获取默认值，只要您没有设置 SqlBulkCopyOptionsKeepNulls，否则会插入一个实际的数据库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.

由于对KeepNulls的SqlBulkCopyOption有些混淆，我们来看看它的定义:

Since there is some confusion about the SqlBulkCopyOption of KeepNulls, let's look at 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.

这意味着设置为 DbNull.Value 的 DataColumn 将作为数据库NULL 插入，即使该列具有 DEFAULT CONSTRAINT，if KeepNulls 选项被指定.它没有在您的代码中指定.这导致第二部分说 DbNull.Value 值在适用的情况下替换为默认值".这里的适用"意味着该列上定义了一个 DEFAULT CONSTRAINT.因此，当存在 DEFAULT CONSTRAINT 时，非 DbNull.Value 值将按原样发送，而 DbNull.Value should 转换为SQL 关键字 DEFAULT.该关键字在 INSERT 语句中被解释为采用 DEFAULT 约束的值.当然，也有可能 SqlBulkCopy，如果发出单独的 INSERT 语句，如果该行设置为 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.

要清楚区别:

• 如果数据库中的某个字段设置为 NOT NULL 并且定义了 DEFAULT CONSTRAINT，则您的选择是:

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

• 传入该字段(即它不会选择默认值)，在这种情况下，它永远不能设置为 DbNull.Value

• 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

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

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

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

如果该字段在源中，那么您必须指定 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.

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

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

如果数据库中的某个字段设置为 NULL 并在其上定义了 DEFAULT CONSTRAINT，则您的选择是:

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

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

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

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

如果该字段在源中，那么您必须指定 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.

传入设置为不是DbNull.Value 的值的字段，在这种情况下，它将被设置为该值并且不选取默认值

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传入，这种情况下的效果取决于是否传入SqlBulkCopyOptions并已设置为KeepNulls:

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:

• KeepNulls 未设置将获取默认值

KeepNulls is 设置将保留字段设置为 NULL

KeepNulls is set will leave the field set to NULL

下面是一个简单的测试，看看 DEFAULT 关键字是如何工作的:

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;

结果:

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

这篇关于当源 DataTable 行具有 DBNull.Value 时，SqlBulkCopy 到默认列值失败的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！