ADO.NET SqlCommand 未返回 SCOPE_IDENTITY() 的正确值

Question

我有一个简单的表格：

CREATE TABLE [MyTable]
(
[ID] [INT] IDENTITY(1,1) NOT NULL,
[NAME] [NVARCHAR](100) NOT NULL,
[DATA] [NVARCHAR](max) NOT NULL,
CONSTRAINT [PK_MyTable] 
PRIMARY KEY CLUSTERED ([ID] 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]
GO

我有一个存储过程，它在此表中插入一行，并通过SCOPE_IDENTITY()返回PK IDENTITY列的值

CREATE PROCEDURE [InsertMyTable] 
@NAME NVARCHAR(100),
@DATA NVARCHAR(MAX)  
AS
BEGIN
INSERT INTO [MyTable] ([NAME], [DATA])
VALUES (@NAME, @DATA)  
RETURN SCOPE_IDENTITY()
END

我已经通过 SSMS 和其他工具测试了这个存储过程，它的行为符合预期。

每次运行以下脚本时，返回的值都会递增 1

：

DELETE FROM [MyTable]
GO
DECLARE @return INT
EXEC @return = [InsertMyTable] @NAME='MyName', @DATA='Data'
SELECT @return
GO

• 首次运行返回 1
• 第二次运行返回 2
• 等。

但是，当我使用System.Data.SqlClient在 C# 代码中执行类似操作时，存储过程返回值始终为 1。它似乎没有反映每次删除并重新插入行时标识都会递增。

using (var connection = new SqlConnection({connection-string}))
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "delete from [MyTable] where [NAME]='MyName'";
connection.Open();
command.ExecuteNonQuery();
}
using (var connection = new SqlConnection({connection-string}))
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "InsertMyTable";
command.Parameters.Add(new SqlParameter($"@NAME", "MyName"));
command.Parameters.Add(new SqlParameter($"@DATA", "data"));
connection.Open();
var returnValue = command.ExecuteNonQuery();
}

• 首次运行returnValue = 1
• 第二轮returnValue = 1
• 等。

Answer 1

存储过程将 ID 作为存储过程返回值，该值应仅用于成功或失败，而不应用于数据。

SqlCommand.ExecuteNonQuery(( 返回已执行批处理的所有行计数消息的总和。

您应该使用输出参数

CREATE PROCEDURE [InsertMyTable] 
@NAME NVARCHAR(100)
, @DATA NVARCHAR(MAX) 
, @ID INT OUTPUT
AS
BEGIN
INSERT INTO [MyTable]
([NAME]
,[DATA])
VALUES
( @NAME
, @DATA)  
set @ID = SCOPE_IDENTITY()
END

或使用结果集，并使用 SqlCommand.ExecuteScalar(( 或 SqlCommand.ExecuteReader(( 获取值。

CREATE PROCEDURE [InsertMyTable] 
@NAME NVARCHAR(100)
, @DATA NVARCHAR(MAX)  
AS
BEGIN
INSERT INTO [MyTable]
([NAME]
,[DATA])
VALUES
( @NAME
, @DATA)  
SELECT SCOPE_IDENTITY() ID
END

您可以通过绑定额外的 SqlParameter 来使现有内容正常工作，但这是一种不好的做法。