問題描述

我有一個帶有標識列的表，我想在插入后獲取該列的值.以下不使用參數的代碼運行良好:

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 似乎不適用于參數化查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！