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

Question

更新:Here is my solution

我有一个表定义为:

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填写的是 DBNull.Value在 CreatedOn , LastUpdatedOn和 LastUpdatedUser . ReferenceID已经生成。当我调用以下代码时，出现以下错误。

代码:

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 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 class 似乎不尊重默认值，即使它说它确实如此。我在这里做错了什么？

Answer 1

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

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

由于 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.

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

明确区分:

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

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

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

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

如果该字段在源中，则必须指定 ColumnMappings集合，以便您可以将该字段排除在映射之外。

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

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

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

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

如果该字段在源中，则必须指定 ColumnMappings集合，以便您可以将该字段排除在映射之外。

传入设置为不是 DbNull.Value 的值的字段，在这种情况下，它将被设置为这个值，而不是选择默认值

作为 DbNull.Value 在现场传递，在这种情况下，效果取决于是否SqlBulkCopyOptions正在传入，已设置为KeepNulls :

KeepNulls未设置将取默认值

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

下面是一个简单的测试，看看 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;

结果:

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