SCOPE_IDENTITY 似乎不适用于参数化查询
我有一个带有标识列的表，我想在插入后获取该列的值.以下不使用参数的代码运行良好:
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();
相关文章