問題描述
我有一個帶有標識列的表，我想在插入后獲取該列的值.以下不使用參數的代碼運行良好:
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模板網！