SqlBulk复制到默认列值在源数据表行具有 DBNull.Value 时失败的表中

本文关键字：Value DBNull 失败 默认 复制 数据表 SqlBulk | 更新日期: 2023-09-27 18:31:09

更新：这是我的解决方案

我有一个表定义为：

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已生成。 当我调用以下代码时，出现以下错误。

法典：

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。
at System.Data.SqlClient.SqlBulkCopy.ConvertValue（Object value， _SqlMetaData metadata， Boolean isNull， Boolean& isSqlType， Boolean& coercedToDataFeed）

我看遍了所有地方，似乎找不到答案。SqlBulkCopy类似乎不遵守默认值，即使它说它这样做。 我在这里做错了什么？

SqlBulk复制到默认列值在源数据表行具有 DBNull.Value 时失败的表中

对于第 1 部分，"默认值不为 NULL 的字段"，您不应该首先发送该字段。不应对其进行映射。无需为此更改该字段以接受 NULL。

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

由于对 KeepNulls 的 SqlBulkCopyOption 存在一些混淆，让我们看一下它的定义：

在目标表中保留空值，而不考虑默认值的设置。如果未指定，则在适用的情况下，空值将替换为默认值。

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

要明确区别：

  • 如果数据库中的字段设置为 NOT NULL 并定义了 DEFAULT 约束，则选项包括：

    • 传入字段（即它不会拾取默认值），在这种情况下，它永远不能设置为 DbNull.Value

    • 根本不要在字段中传递（即它将拾取默认值），这可以通过以下任一方式完成：

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

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

      • 设置

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

  • 如果数据库中的字段设置为 NULL 并定义了 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

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

阅读有关SqlBulkCopy的文档，特别是SqlBulkCopyOptions，我会得出与您相同的结论：SQL Server应该足够"智能"，以便在适用的情况下使用默认约束，特别是因为您没有使用SqlBulkCopyOptions.KeepNulls属性。

但是，在这种情况下，我怀疑文档略有不正确;如果不是不正确，那肯定是误导性的。

如您所观察的，对于具有默认约束（在本例中为 GetDate()）的不可为空字段，SqlBulkCopy 将失败并出现上述错误。

作为测试，请尝试创建模拟第一个表的第二个表，但这次使CreatedOn和LastUpdatedOn字段可为空。在我的测试中，使用默认选项 （ SqlBulkCopyOptions.Default ） 该过程没有错误和CreatedOn，并且LastUpdatedOn两者都在表中填充了正确的 DateTime 值，尽管这些字段的数据表值DBNull.Value。

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

此行为类似于执行"原版"T-SQL 语句以将数据插入表中。

以原始表（不可为空字段）为例，如果执行 

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

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

但是，如果省略语句中的不可为空字段，则 SQL Server 对这些字段使用默认约束： 

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

基于此，我建议要么使表中的字段为空（在我看来不是一个好的选择），要么在 SqlBulkCopy 过程中使用"临时"表（其中字段可为空并且具有类似的默认约束）。一旦数据在临时表中，请执行第二条语句将数据移动到实际的最终目标表中。

相关文章：