使用 c# 从 SQL Server Insert 命令返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9319532/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return value from SQL Server Insert command using c#
提问by Neko
Using C# in Visual Studio, I'm inserting a row into a table like this:
在 Visual Studio 中使用 C#，我将一行插入到这样的表中：
INSERT INTO foo (column_name)
VALUES ('bar')
I want to do something like this, but I don't know the correct syntax:
我想做这样的事情，但我不知道正确的语法：
INSERT INTO foo (column_name)
VALUES ('bar')
RETURNING foo_id
This would return the foo_idcolumn from the newly inserted row.
这将从foo_id新插入的行返回列。
Furthermore, even if I find the correct syntax for this, I have another problem: I have SqlDataReaderand SqlDataAdapterat my disposal. As far as I know, the former is for reading data, the second is for manipulating data. When inserting a row with a return statement, I am both manipulating and reading data, so I'm not sure what to use. Maybe there's something entirely different I should use for this?
此外，即使我找到了正确的语法，我还有另一个问题：我有SqlDataReader并且SqlDataAdapter可以使用。据我所知，前者用于读取数据，后者用于操作数据。使用 return 语句插入一行时，我同时在操作和读取数据，所以我不确定该使用什么。也许我应该为此使用完全不同的东西？
采纳答案by Tim Schmelter
SCOPE_IDENTITYreturns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.
SCOPE_IDENTITY返回插入到同一范围内的标识列中的最后一个标识值。作用域是一个模块：存储过程、触发器、函数或批处理。因此，如果两个语句在同一个存储过程、函数或批处理中，则它们在同一范围内。
You can use SqlCommand.ExecuteScalarto execute the insert command and retrieve the new ID in one query.
您可以使用SqlCommand.ExecuteScalar来执行插入命令并在一个查询中检索新 ID。
using (var con = new SqlConnection(ConnectionString)) {
int newID;
var cmd = "INSERT INTO foo (column_name)VALUES (@Value);SELECT CAST(scope_identity() AS int)";
using (var insertCommand = new SqlCommand(cmd, con)) {
insertCommand.Parameters.AddWithValue("@Value", "bar");
con.Open();
newID = (int)insertCommand.ExecuteScalar();
}
}
回答by KM.
try this:
尝试这个：
INSERT INTO foo (column_name)
OUTPUT INSERTED.column_name,column_name,...
VALUES ('bar')
OUTPUTcan return a result set (among other things), see: OUTPUT Clause (Transact-SQL). Also, if you insert multiple values (INSERT SELECT) this method will return one row per inserted row, where other methods will only return info on the last row.
OUTPUT可以返回结果集（除其他外），请参阅：OUTPUT Clause (Transact-SQL)。此外，如果您插入多个值 (INSERT SELECT)，此方法将为每个插入的行返回一行，而其他方法将只返回最后一行的信息。
working example:
工作示例：
declare @YourTable table (YourID int identity(1,1), YourCol1 varchar(5))
INSERT INTO @YourTable (YourCol1)
OUTPUT INSERTED.YourID
VALUES ('Bar')
OUTPUT:
输出：
YourID
-----------
1
(1 row(s) affected)
回答by Jesse
I think you can use @@IDENTITY for this, but I think there's some special rules/restrictions around it?
我认为您可以为此使用 @@IDENTITY，但我认为它有一些特殊的规则/限制？
using (var con = new SqlConnection("connection string"))
{
con.Open();
string query = "INSERT INTO table (column) VALUES (@value)";
var command = new SqlCommand(query, con);
command.Parameters.Add("@value", value);
command.ExecuteNonQuery();
command.Parameters.Clear();
command.CommandText = "SELECT @@IDENTITY";
int identity = Convert.ToInt32(command.ExecuteScalar());
}
