C# 如何使用单个 SqlCommand 插入记录并返回新创建的 ID？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/330707/
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
How to insert a record and return the newly created ID using a single SqlCommand?
提问by Mats
I'm using an SqlCommand object to insert a record into a table with an autogenerated primary key. How can I write the command text so that I get the newly created ID when I use the ExecuteScalar() method?
我正在使用 SqlCommand 对象将记录插入到带有自动生成主键的表中。如何编写命令文本，以便在使用 ExecuteScalar() 方法时获得新创建的 ID？
采纳答案by Dave Markle
INSERT INTO YourTable(val1, val2, val3 ...)
VALUES(@val1, @val2, @val3...);
SELECT SCOPE_IDENTITY();
Don't forget the semicolons at the end of each statement.
不要忘记每个语句末尾的分号。
回答by JoshBerke
insert into Yourtable()
values()
SELECT SCOPE_IDENTITY()
I just ran a test and verified that the semi-colons are optional using SQL Server 2005 SP2, and .Net 3.5
我刚刚运行了一个测试并验证了分号是可选的，使用 SQL Server 2005 SP2 和 .Net 3.5
回答by Treb
Straight out of the Whirlpool:
直接从漩涡中出来：
If you're using MS SQL you can use "SELECT @@IDENTITY as Value" after your insert to get the last ID generated
如果您使用的是 MS SQL，则可以在插入后使用“SELECT @@IDENTITY as Value”来获取生成的最后一个 ID
and:
和：
@@IDENTITYandSCOPE_IDENTITYreturn the last identity value generated in any table in the current session. However,SCOPE_IDENTITYreturns the value only within the current scope;@@IDENTITYis not limited to a specific scope.
@@IDENTITY并SCOPE_IDENTITY返回在当前会话中任何表中生成的最后一个标识值。但是，SCOPE_IDENTITY只返回当前范围内的值；@@IDENTITY不限于特定范围。
Edit:As pointed out in the comments, you should always use SCOPE_IDENTITY, not @@IDENTITY.
编辑：正如评论中所指出的，您应该始终使用SCOPE_IDENTITY，而不是@@IDENTITY。
回答by Andy McCluggage
Add the following line to the end of the Sql Query...
将以下行添加到 Sql 查询的末尾...
SELECT SCOPE_IDENTITY()
And then use the ExecuteScalar method on the SqlCommand object...
然后在 SqlCommand 对象上使用 ExecuteScalar 方法...
var rowCount = command.ExecuteScalar()
回答by Samiksha
Immediately after your insert stmt, use
在插入 stmt 之后，立即使用
SELECT CAST(scope_identity() AS bigint) ---- incase you have a return result as int64
This will return the column created id/identity.
这将返回创建的 id/identity 列。
回答by Kezzer
Don't use @@IDENTITY, however simple it may seem. It can return incorrect values.
不要使用@@IDENTITY，无论它看起来多么简单。它可能会返回不正确的值。
SELECT SCOPE_IDENTITY()
appears to be the obvious choice.
似乎是显而易见的选择。
回答by Matt
Add an output parameter to the command object and then set the value to the new ID in the stored procedure.
向命令对象添加输出参数，然后将值设置为存储过程中的新 ID。
Stored Procedure:
存储过程：
@ID AS INT OUTPUT
[Insert Command]
SET @ID = SCOPE_IDENTITY()
.NET:
。网：
cmd.CommandText = "stored_procedure";
SqlParameter pID = new SqlParameter("ID", DBType.Int32, 4);
pID.Direction = ParameterDirection.Output;
cmd.ExecuteScalar();
int id = Convert.ToInt32(cmd.Parameters["ID"].Value.ToString());
回答by Russ
Although I like Dave Markle'sanswer, ( and I see you did too, since you marked it as your answer ), that method can fail if you have triggers on your database, that audit CUD operations, and your audit table has an IDENTITY column. It would return the value of the Audit table's identity, not the table you just inserted into, since the audit table actualy happen after.
尽管我喜欢Dave Markle 的回答，（我看到你也喜欢，因为你将它标记为你的答案），如果你的数据库上有触发器，审计 CUD 操作，并且你的审计表有一个 IDENTITY 列，那么该方法可能会失败。它会返回审计表的标识值，而不是您刚刚插入的表，因为审计表实际上是在之后发生的。
In that case, a more generic method can be used that will work in both cases, regardless of any auditing. Its a bit more wordy, but you get what you pay for.
在这种情况下，可以使用更通用的方法，无论任何审计如何，它都适用于这两种情况。它有点罗嗦，但你得到你所支付的。
example:
例子：
@"DECLARE @tmp AS TABLE ( id int )
INSERT INTO case
(
caseID,
partID,
serialNumber,
hardware,
software,
firmware
)
OUTPUT Inserted.ID into @tmp
VALUES
(
@caseID,
@partItemID,
@serialNumber,
@hardware,
@software,
@firmware
)
Select ID from @tmp" )
回答by Zymotik
If your id is a Guid, then I found this solution to be best:
如果您的 id 是 Guid，那么我发现这个解决方案是最好的：
INSERT INTO YourTable (val1, val2, val3)
OUTPUT inserted.id
VALUES (@val1, @val2, @val3)
Thanks @Scott Ivey
谢谢@Scott Ivey
Full demo:
完整演示：
internal static Guid InsertNote(Note note)
{
Guid id;
using (
var connection =
new SqlConnection(ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString))
{
connection.Open();
using (
var command =
new SqlCommand(
"INSERT INTO Notes ([Title],[Text]) " +
"OUTPUT inserted.id " +
$"VALUES ('{title}','{text}');", connection))
{
command.CommandType = CommandType.Text;
var reader = command.ExecuteReader();
reader.Read();
id = reader.GetGuid(reader.GetOrdinal("id"));
}
connection.Close();
}
return id;
}
I would recommend using a Stored Procedure, but this is for unit testing our repository.
我建议使用存储过程，但这是用于对我们的存储库进行单元测试。
