C# 从 MS SQL Server 获取自动编号主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/315963/
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
Getting autonumber primary key from MS SQL Server
提问by Zeroth
I am currently working in C#, and I need to insert a new record into one table, get the new primary key value, and then use that as a foreign key reference in inserting several more records. The Database is MS SQL Server 2003. All help is appreciated!
我目前正在使用 C#，我需要在一个表中插入一条新记录，获取新的主键值，然后将其用作插入更多记录的外键引用。数据库是 MS SQL Server 2003。感谢所有帮助！
采纳答案by Greg Beech
The way to get the identity of the inserted row is with the SCOPE_IDENTITY()function. If you're using stored procedures then this would look something like the following to return the row identity as an output parameter.
获取插入行标识的方法是使用SCOPE_IDENTITY()函数。如果您使用的是存储过程，那么这将类似于以下内容，以将行标识作为输出参数返回。
CREATE PROCEDURE dbo.MyProcedure
(
@RowId INT = NULL OUTPUT
)
AS
INSERT INTO MyTable
(
Column1
,Column2
,...
)
VALUES
(
@Param1
,@Param2
,...
);
SET @RowId = SCOPE_IDENTITY();
You can then use this value for any subsequent inserts (alternatively, if you can pass the data all into the stored procedure, then you can use it in the remainder of the procedure body).
然后，您可以将此值用于任何后续插入（或者，如果您可以将数据全部传递到存储过程中，那么您可以在过程主体的其余部分中使用它）。
If you're passing the SQL in dynamically then you use much the same technique, but with a single string with statement delimiters (also ;in SQL), e.g.:
如果您动态传递 SQL，那么您将使用相同的技术，但使用带有语句分隔符的单个字符串（也在;SQL 中），例如：
var sql = "INSERT INTO MyTable (Column1, Column2, ...) VALUES (@P1, @P2, ...);" +
"SELECT SCOPE_IDENTITY();";
Then if you execute this using ExecuteScalaryou'll be able to get the identity back as the scalar result and cast it to the right type. Alternatively you could build up the whole batch in one go, e.g.
然后，如果您使用它执行此操作，ExecuteScalar您将能够将标识作为标量结果取回并将其转换为正确的类型。或者，您可以一次性建立整个批次，例如
var sql = "DECLARE @RowId INT;" +
"INSERT INTO MyTable (Column1, Column2, ...) VALUES (@P1, @P2, ...);" +
"SET @RowId = SCOPE_IDENTITY();" +
"INSERT INTO MyOtherTable (Column1, ...) VALUES (@P3, @P4, ...);";
This may not be exactlythe right syntax, and you may need to use SET NOCOUNT ON;at the start (my mind is rusty as I rarely use dynamic SQL) but it should get you on the right track.
这可能不是完全正确的语法，您可能需要SET NOCOUNT ON;在开始时使用（我的想法很生疏，因为我很少使用动态 SQL）但它应该让您走上正确的轨道。
回答by Duncan
The best way of doing this is the use SCOPE_IDENTITY() function in TSQL. This should be executed as part of the insert i.e.
最好的方法是在 TSQL 中使用 SCOPE_IDENTITY() 函数。这应该作为插入的一部分执行，即
SqlCommand cmd = new SqlCommand(@"
INSERT INTO T (Name) VALUES(@Name)
SELECT SCOPE_IDENTITY() As TheId", conn);
cmd.AddParameter("@Name", SqlDbType.VarChar, 50).Value = "Test";
int tId = (int)cmd.ExecuteScalar();
Alternatively you can assign SCOPE_IDENTITY() to a variable to be used in successive statements. e.g.
或者，您可以将 SCOPE_IDENTITY() 分配给要在连续语句中使用的变量。例如
DECLARE @T1 int
INSERT INTO T (Name) VALUES('Test')
SELECT @T1 = SCOPE_IDENTITY()
INSERT INTO T2 (Name, TId) VALUES('Test', @T1)
回答by Yona
If you are just using SQL then check Duncan's answer. If however you are using LINQ then you can create the entity, save it to the DB and the ID parameter will be populated automatically.
如果您只是使用 SQL，请查看 Duncan 的答案。但是，如果您使用的是 LINQ，那么您可以创建实体，将其保存到数据库中，ID 参数将自动填充。
Given a user entity and a user table it might look like this:
给定一个用户实体和一个用户表，它可能如下所示：
using(var db = new DataContext()) {
var user = new User { Name = "Jhon" };
db.Users.InsertOnSubmit(user);
db.SubmitChanges();
/* At this point the user.ID field will have the primary key from the database */
}
