SCOPE_IDENTITY 似乎不适用于参数化查询 [英] SCOPE_IDENTITY does not seem to work with parameterized queries

问题描述

我有一个带有标识列的表，我想在插入后获取该列的值.以下不使用参数的代码运行良好:

I have a table with an identity column whose value I would like to get after an INSERT. The following code, which does not use parameters, is working perfectly:

string query = "INSERT INTO aTable ([aColumn]) VALUES (42)";
SqlCommand command = new SqlCommand(query, connection);
command.ExecuteNonQuery();

query = "SELECT CAST(SCOPE_IDENTITY() AS bigint)";
command = new SqlCommand(query, connection);
object identity = command.ExecuteScalar();

如果我将上述代码的 INSERT 部分更改为使用参数化查询，ExecuteScalar() 会突然返回一个 System.DBNull 值.这是参数化查询代码的样子:

If I change the INSERT part of the above code to use a parameterized query, ExecuteScalar() suddenly returns a System.DBNull value. This is how the parameterized query code looks like:

string query = "INSERT INTO aTable ([aColumn]) VALUES (@aColumn)";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@aColumn", 42);
command.ExecuteNonQuery();

我尝试更改 SCOPE_IDENTITY 代码，以便它使用输出参数并调用 ExecuteNonQuery()，但我仍然在 out 参数中得到空值.我还尝试在两个不同版本的 SQL Server(2012 和 2008，都为 Express 版)上运行代码，结果相同.

I have tried to change the SCOPE_IDENTITY code so that it uses an output parameter and invokes ExecuteNonQuery(), but I still get a null value in the out parameter. I have also tried running the code against two different versions of SQL Server (2012 and 2008, both Express Edition), again with the same result.

知道我在这里做错了什么吗?

Any ideas what I am doing wrong here?

推荐答案

尝试将 INSERT 和 SELECT 合并为一个语句

Try combining your INSERT and SELECT into one statement

string query = "INSERT INTO aTable ([aColumn]) VALUES (@aColumn);SELECT CAST(SCOPE_IDENTITY() AS bigint)"; 
SqlCommand command = new SqlCommand(query, connection); 
command.Parameters.AddWithValue("@aColumn", 42); 
object identity = command.ExecuteScalar(); 

这篇关于SCOPE_IDENTITY 似乎不适用于参数化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！