在表上如何使用SqlBulkCopy的一个GUID主键和默认NEWSEQUENTIALID（）？ [英] How can use SQLBulkCopy on a table with a GUID primary key and default newsequentialid()?

问题描述

在桌子上使用SqlBulkCopy的一个GUID主键和默认NEWSEQUENTIALID（）

When using SQLBulkCopy on a table with a GUID primary key and default newsequentialid()

例如

CREATE TABLE [dbo].[MyTable](
[MyPrimaryKey] [uniqueidentifier] NOT NULL CONSTRAINT [MyConstraint]  DEFAULT (newsequentialid()),
[Status] [int] NULL,
[Priority] [int] NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED 
(
[MyPrimaryKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

与C＃code

wIth the C# code

        tran = connection.BeginTransaction();
        SqlBulkCopy sqlCopy = new SqlBulkCopy(connection,SqlBulkCopyOptions.Default, tran);            

        sqlCopy.DestinationTableName = "MyTable";            
        sqlCopy.WriteToServer(dataTable);

给你一个错误...

Gives you an error...

列'MyPrimaryKey不允许的DBNull.Value

Column 'MyPrimaryKey' does not allow DBNull.Value

我已经试过了摆弄的SqlBulkCopyOptions。该作品的唯一的事情就是设置MyPrimaryKey领域，允许空值和删除主键。

I've tried fiddling the the SqlBulkCopyOptions. The only thing that works is setting the MyPrimaryKey field to allow nulls and removing the primary key.

任何人都知道，如果有一个解决此问题？ 或者，你能验证有没有解决方法（不是改变表结构等）？

Anyone know if there is a workaround for this issue? Or can you verify that there is no workaround (other than changing the table structure)?

推荐答案

您需要设置列映射。第一次调用

You need to set up the column mappings. First call

sqlCopy.ColumnMappings.Clear();

然后调用

sqlBulkCopy.ColumnMappings.Add("Status", "Status");
sqlBulkCopy.ColumnMappings.Add("Priority", "Priority");

这意味着批量复制将停止尝试插入MyPrimaryKey列，将只插入的状态和优先级列。

This means the bulk copy will stop trying to insert into MyPrimaryKey column and will only insert into the status and Priority columns.

这篇关于在表上如何使用SqlBulkCopy的一个GUID主键和默认NEWSEQUENTIALID（）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！