我将 IDENTITY_INSERT 设置为 ON，但收到 SqlException 提示它已关闭

Question

我试图从文本文件中获取数千张发票（和其他内容）并将它们插入到 SQL Server 2008 中。我编写了一个小控制台应用程序来执行此操作，它使用 LINQ to SQL。插入所有现有发票后，我希望 Invoice_ID 成为标识列并自动递增，因此我将其设计如下：

CREATE TABLE [dbo].[Invoice]
(
 [Invoice_ID] int IDENTITY(1,1) NOT NULL PRIMARY KEY, 
    /* Other fields elided */
)

在开始插入发票之前，我调用一个包含以下内容的存储过程行：

SET IDENTITY_INSERT [dbo].[Invoice] ON;
/* Other lines for setup elided */

提交更改后，我使用以下行调用另一个存储过程：

SET IDENTITY_INSERT [dbo].[Invoice] OFF;
/* Other lines for clean up elided */

当我尝试插入发票并提交更改时，出现以下异常：

SQLException：当 IDENTITY_INSERT 设置为 OFF 时，无法在表“Invoice”中插入标识列的显式值。

我运行了 SQL Profiler，可以看到它确实在尝试执行插入之前将 IDENTITY_INSERT 设置为 ON。我没有看到它在任何地方被设置为 OFF 。我对 SQL Profiler 还很陌生，所以也许我可以启用一个事件，该事件将为我提供更多信息来尝试和调试它。有什么想法吗？

我尝试调整 LINQ to SQL 使用的 .dbml 文件中的值。我将发票表的自动生成值设置为“False”，将自动同步设置为“从不”，并将服务器数据类型 设置为“Int NOT NULL”。通常我会将它们分别设置为“True”、“On Insert”和“Int NOT NULL IDENTITY”，但是当我这样做时，我可以看到 SQL Server 正在执行：

选择 CONVERT(Int,SCOPE_IDENTITY()) AS [值]

而不是插入我提供的 Invoice_ID。

Answer 1

http://msdn.microsoft.com/en-us/library/ms190356。 aspx

如果你往下看，你可以看到：

如果 SET 语句在存储中运行
过程或触发器，的值
控制后恢复SET选项
从存储过程返回
或触发。另外，如果 SET 语句
在动态 SQL 字符串中指定
通过使用以下任一方式运行
sp_executesql 或 EXECUTE，值
SET 选项恢复后
控制从批处理中返回
在动态 SQL 字符串中指定。

Answer 2

虽然 IDENTITY_INSERT 的范围似乎是一个问题，但我在 EF v4.1 中找到并测试的解决方案是首先打开连接，以便会话保持打开状态。例如，

using (SiteDataContext context = SiteDataContext.Create())
{
    context.Connection.Open();
    context.ExecuteStoreCommand("SET IDENTITY_INSERT [EnumValue] ON");

    var val = new EnumValue()
    {
        ID = 2000000,
        Value = "just a test",
    };
    context.AddToEnumValues(val);
    context.SaveChanges();

    context.ExecuteStoreCommand("SET IDENTITY_INSERT [EnumValue] OFF");
}

我测试了这个解决方案，没有调用 context.Connection.Open();首先，它按照描述失败了。然后我添加了它完美运行的行。

Answer 3

我希望 SET IDENTITY_INSERT 的范围仅限于过程的主体。我见过的最常见的方法是创建一个存储过程，它使用 EXEC 来执行包含 SET IDENTITY_INSERT 和 INSERT 本身的动态 SQL。