問(wèn)題描述

更新:這是我的解決方案

我有一個(gè)表定義為:

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]

我有一個(gè) DataTable，其中的列與表列名稱和數(shù)據(jù)類型相匹配.DataTable 在 CreatedOn、LastUpdatedOn 和 LastUpdatedUser 中用 DBNull.Value 填充.ReferenceID 已經(jīng)生成.當(dāng)我調(diào)用以下代碼時(shí)，出現(xiàn)以下錯(cuò)誤.

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

錯(cuò)誤:

嘗試批量復(fù)制表時(shí)出錯(cuò) 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)

我已經(jīng)看遍了，似乎無(wú)法找到答案.SqlBulkCopy 類似乎不尊重默認(rèn)值，即使它說(shuō)它確實(shí)如此.我在這里做錯(cuò)了什么?

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?

推薦答案

對(duì)于第 1 部分具有默認(rèn)值的 NOT NULL 字段"，您首先不應(yīng)發(fā)送該字段.它不應(yīng)該被映射.無(wú)需為此更改該字段以接受 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.

對(duì)于第 2 部分，帶有 DEFAULT 的 NULL 字段"，將在傳入 DbNull.Value 時(shí)獲取默認(rèn)值，只要您沒(méi)有設(shè)置 SqlBulkCopyOptionsKeepNulls，否則會(huì)插入一個(gè)實(shí)際的數(shù)據(jù)庫(kù)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.

由于對(duì)KeepNulls的SqlBulkCopyOption有些混淆，我們來(lái)看看它的定義:

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

無(wú)論默認(rèn)值的設(shè)置如何，都在目標(biāo)表中保留空值.如果未指定，則空值將在適用的情況下替換為默認(rèn)值.

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.

這意味著設(shè)置為 DbNull.Value 的 DataColumn 將作為數(shù)據(jù)庫(kù)NULL 插入，即使該列具有 DEFAULT CONSTRAINT，if KeepNulls 選項(xiàng)被指定.它沒(méi)有在您的代碼中指定.這導(dǎo)致第二部分說(shuō) DbNull.Value 值在適用的情況下替換為默認(rèn)值".這里的適用"意味著該列上定義了一個(gè) DEFAULT CONSTRAINT.因此，當(dāng)存在 DEFAULT CONSTRAINT 時(shí)，非 DbNull.Value 值將按原樣發(fā)送，而 DbNull.Value should 轉(zhuǎn)換為SQL 關(guān)鍵字 DEFAULT.該關(guān)鍵字在 INSERT 語(yǔ)句中被解釋為采用 DEFAULT 約束的值.當(dāng)然，也有可能 SqlBulkCopy，如果發(fā)出單獨(dú)的 INSERT 語(yǔ)句，如果該行設(shè)置為 NULL，則可以簡(jiǎn)單地將該字段排除在列列表之外，這將采用默認(rèn)值.在任何一種情況下，最終結(jié)果都是它按預(yù)期工作.我的測(cè)試表明它確實(shí)以這種方式工作.

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.

要清楚區(qū)別:

• 如果數(shù)據(jù)庫(kù)中的某個(gè)字段設(shè)置為 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:

• 傳入該字段(即它不會(huì)選擇默認(rèn)值)，在這種情況下，它永遠(yuǎn)不能設(shè)置為 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

根本不傳入字段(即它將獲取默認(rèn)值)，這可以通過(guò)以下任一方式完成:

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 或作為源發(fā)送的任何內(nèi)容中包含它，在這種情況下，您可能根本不需要指定 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.

設(shè)置或不設(shè)置KeepNulls 不會(huì)改變上述行為.

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

如果數(shù)據(jù)庫(kù)中的某個(gè)字段設(shè)置為 NULL 并在其上定義了 DEFAULT CONSTRAINT，則您的選擇是:

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

• 根本不傳入字段(即它將獲取默認(rèn)值)，這可以通過(guò)以下任一方式完成:

• 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 或作為源發(fā)送的任何內(nèi)容中包含它，在這種情況下，您可能根本不需要指定 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.

傳入設(shè)置為不是DbNull.Value 的值的字段，在這種情況下，它將被設(shè)置為該值并且不選取默認(rèn)值

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并已設(shè)置為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 未設(shè)置將獲取默認(rèn)值

KeepNulls is 設(shè)置將保留字段設(shè)置為 NULL

KeepNulls is set will leave the field set to NULL

下面是一個(gè)簡(jiǎn)單的測(cè)試，看看 DEFAULT 關(guān)鍵字是如何工作的:

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;

結(jié)果:

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

這篇關(guān)于當(dāng)源 DataTable 行具有 DBNull.Value 時(shí)，SqlBulkCopy 到默認(rèn)列值失敗的表中的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！