問題描述

更新:這是我的解決方案

我有一個表定義為:

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 到默認列值失敗的表中的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！