logo标签列表

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

c#sql-servernullsqlbulkcopydefault-constraint
8

更新: 这是我的解决方案

我定义了一个表:

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

错误：

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

我已经搜遍了所有的地方，但似乎找不到答案。尽管SqlBulkCopy类声称支持默认值，但实际上并未如此。我在这里做错了什么？

3个回答
17

对于第一部分，“具有DEFAULT的NOT NULL字段”，您不应该首先发送该字段。 它不应被映射。 没必要为此更改该字段以接受NULL。

对于第二部分，“具有DEFAULT的NULL字段”，当传入DbNull.Value时，会起作用以获取默认值，只要没有将SqlBulkCopyOptions设置为KeepNulls，否则它将插入实际的数据库NULL。

由于有关KeepNulls SqlBulkCopyOption的某些混淆，让我们看一下其定义：

保留目标表中的空值，而不管默认值的设置如何。 如果未指定，则会将空值替换为适用的默认值。

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

清楚区分：

  • 如果数据库中的字段设置为NOT NULL并具有DEFAULT CONSTRAINT，则您的选项为：

    • 传递字段（即它不会自动获取DEFAULT值），在这种情况下，它永远不能设置为DbNull.Value

    • 根本不要传递该字段（即它将自动获取DEFAULT值），可以通过以下两种方式实现：

      • 无论是从作为源发送的DataTable或查询或DataReader还是其他任何内容中都不包含它，这样您可能根本不需要指定ColumnMappings集合

      • 如果该字段在源中，则必须指定ColumnMappings集合，以使该字段在映射中被省略。

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

  • 如果数据库中的字段设置为NULL并具有DEFAULT CONSTRAINT，则您的选项为：

    • 根本不传递该字段（即它将自动获取DEFAULT值），可以通过以下两种方式实现：

      • 无论是从作为源发送的DataTable或查询或DataReader还是其他任何内容中都不包含它，这样您可能根本不需要指定ColumnMappings集合

      • 如果该字段在源中，则必须指定ColumnMappings集合，以使该字段在映射

        --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应该在相关字段上有默认约束时转换为Default，所以它应该可以工作？对于我来说，在使用.NET 4.5和SQL Server 2012时，我无法通过将DBNull.Value传递给具有默认约束的非空字段来使SqlBulkCopy工作。 - Tim Lentine
@TimLentine：嘿，为了澄清，我想说的是a）我确实通过SqlBulkCopy使其工作，并且b）如果您想选择默认值，则根本不能指定标记为“NOT NULL”的字段（这是我在第1部分中提到的）。我在第2部分中提到的将其转换为“DEFAULT”是关于可空字段的，这涵盖了詹姆斯在您的答案评论中提到的“其他表格中我将有包含数据以及nulls的列”的内容。但是，是的，令人困惑的是，SqlBulkCopy根据字段被声明为“NULL”还是“NOT NULL”而不同地处理DbNull.value。 - Solomon Rutzky
@TimLentine：我刚刚更新了我的答案，并加入了一个项目列表，希望更清晰地列出各种情况。 - Solomon Rutzky
如果微软文档能够直截了当地说明这一点就好了！感谢您的解释。 - James Nix
@JamesNix 是的，这确实很令人困惑，因为它的行为不一致。DbNull.Value 在可空字段上使用默认值是完全可以的，那么为什么在非空字段上也不能呢？我唯一能想到的原因是，首先会有一个验证步骤，它会将数据与模式进行比较，但不知道 BulkCopyOptions 值。我怀疑这种行为并不是故意的，要么是开发人员不知道（听起来像个 bug），要么是已知的，但要么“不值得修复”，要么“还没有修复”。无论哪种方式，:(。 - Solomon Rutzky
显示剩余9条评论
3
“SQLBulkCopy列不允许DbNull.value”错误是由于源表和目标表具有不同的列顺序所致。
当我从一个数据库表传输数据到另一个表时，出现了这个错误。正如@James Nix所总结的那样，根本原因在于我们如何设置列的_default_值属性。谢谢。 - Anasuddeen
这是正确的答案！我一直在搜索mysql整数列转换和我的代码以及可能的空值等问题。但这只是一个愚蠢的错误！谢谢。 - sanpat
0

阅读有关SqlBulkCopy的文档，特别是SqlBulkCopyOptions，我得出了与您相同的结论：SQL Server应该足够“聪明”，以在适用的情况下使用默认约束条件，尤其是当您不使用SqlBulkCopyOptions.KeepNulls属性时。

然而，在这种情况下，我怀疑文档存在微妙的错误；如果不是错误，那么它肯定是误导性的。

正如您所观察到的，对于具有默认约束条件（在本例中为GetDate()）的非空字段，SqlBulkCopy会失败并显示上述错误。

作为测试，请创建第二个表，模仿第一个表，但这次将CreatedOn和LastUpdatedOn字段设置为可空。在我的测试中，使用默认选项（SqlBulkCopyOptions.Default），该过程可以无错误地运行且CreatedOn和LastUpdatedOn都在表中填充了正确的DateTime值，尽管DataTable的这些字段的值为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)

如果您在表格中输入 null 值，您将会收到与此类似的错误提示。

但是，如果您在 SQL 语句中省略了非空字段，SQL Server 将使用默认约束来填充这些字段：

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

基于此，我建议在表中使字段可为空（在我看来并不是一个很好的选择），或者使用“暂存”表进行SqlBulkCopy过程（其中字段可为空并具有类似的默认约束）。一旦数据进入暂存表，执行第二个语句将数据移动到实际的最终目标表中。
是的，我期望当提供空值时，SQL Server会使用默认值填充该列。请参见：http://stackoverflow.com/a/4244132/3874334。这个例子演示了我可能总是保留为空的字段，但我有其他表，其中包含数据和空值的列。 - James Nix
@JamesNix 这不是 SQL Server DEFAULTS 的工作方式，无论是否使用 SqlBulkCopy。只有在未指定列时才会应用 DEFAULTS。如果您为列指定 NULL，则它将尝试使其为 NULL（如果声明为 NOT NULL，则会导致错误），并且不会使用 DEFAULT 值。当文档说“*...将观察任何默认值..*”时，这意味着 SqlBulkCopy 将以与任何其他 SQL 命令相同的方式观察 DEFAULTs。 - RBarryYoung
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接