关闭数据集插入的IDENTITY_INSERT功能

Question

我正在使用一个数据集来插入从旧数据库转换而来的数据。要求是保持当前的Order_ID号码。

我已经尝试使用：

SET IDENTITY_INSERT orders ON;

在 SqlServer Management Studio 中，这段代码可以成功运行：

INSERT INTO orders (order_Id, ...) VALUES ( 1, ...);

然而，使用我在转换脚本中正在使用的数据集插入方式无法实现此操作。它看起来基本上是这样的：

dsOrders.Insert(oldorderId, ...);

我在过程中运行了 SQL（SET IDENTITY_INSERT orders ON）。我知道我一次只能针对一个表进行操作并且正在进行该操作。但我一直收到这个异常：

在尝试向orders表插入值时出现异常 System.Data.SqlClient.SqlException：设置IDENTITY_INSERT为OFF时，无法向表'orders'中的标识列插入显式值。

有什么想法吗？

更新

AlexS 和 AlexKuznetsov 提到Set Identity_Insert是一个连接级别的设置，但是当我在SqlProfiler中查看SQL时，我注意到有几个命令。

第一条 - SET IDENTITY_INSERT DEAL ON 第二个-exec sp_reset_connection 第三到n -我的各种SQL命令，包括select和insert。

不过，每个命令之间总会有一个exec sp_reset_connection，我相信这是导致Identity_Insert设置丢失的原因。

有没有办法防止数据集执行连接重置？

- Nathan Koop

Answer 1

您的选项混淆了：

SET IDENTITY_INSERT orders ON

会启用插入指定值的能力（由您指定），并将其插入具有IDENTITY列的表中。

SET IDENTITY_INSERT orders OFF

关闭此行为后，正常行为（您无法为自动生成的IDENTITY列指定值）将恢复。

Marc

- marc_s

Answer 2

要允许您插入标识列，您需要执行 SET IDENTITY_INSERT ON 操作。

这似乎有些反常，但这就是它的工作方式。

- Eric Petroelje

---

你说得对，我把选项搞混了，但是当我更改它们时，问题并没有得到解决。还有其他的想法吗？ - Nathan Koop

Answer 3

看起来你做得没错：在SQL Server端，SET IDENTITY_INSERT orders ON 是正确的方式。但问题在于你正在使用数据集。从你提供的代码可以看出，你正在使用类型化数据集——这是基于数据库在Visual Studio中生成的数据集。

如果是这种情况（最有可能），那么这个数据集包含一个约束，不允许你设置orderId字段的值，也就是说，是代码不允许指定显式值，而不是SQL Server。你应该进入数据集设计器并编辑orderId字段的属性：将AutoIncrement和ReadOnly设置为false。但同样的更改也可以在运行时执行。这将允许你向数据集添加一个具有orderId显式值的行，然后将其保存到SQL Server表中（仍然需要SET IDENTITY_INSERT）。

还要注意，IDENTITY_INSERT是连接级别的设置，因此你需要确保你执行相应的SET操作恰好是用于将更改保存到数据库的同一连接。

- AlexS

Answer 4

AlexS是正确的，问题在于Insert_Identity有效，但它是一个连接级别的设置，所以我需要在事务中设置Insert_Identity。

我使用了Ryan Whitaker的TableAdapterHelper代码，并在我的tableadapter上创建了一个更新命令，运行Identity_Insert。然后，我不得不创建一个新的插入命令，指定Identity列。然后我运行了这段代码。

SqlTransaction transaction = null;

try
{
     using (myTableAdapter myAdapter = new myTableAdapter())
     {   
         transaction = TableAdapterHelper.BeginTransaction(myAdapter);
         myAdapter.SetIdentityInsert();
         myAdapter.Insert(myPK,myColumn1,myColumn2,...);
     }

     transaction.Commit();
 }
 catch(Exception ex)
 {
     transaction.Rollback();
 } 
 finally
 {
     transaction.Dispose();
 }

- Nathan Koop

Answer 5

我会使用 Profiler 工具来确定您的 SET IDENTITY_INSERT orders ON; 是否是从相同的连接中发出的，以及在插入期间执行的确切 SQL。

- A-K

Answer 6

如果你仍然在使用 "insert_identity" 时遇到问题，可以尝试使用一个完整的插入语句，例如：

insert into User(Id, Name) values (1,'jeff')

- user3447006