当前位置：文江博客话题详情

从 MS SQL Server 获取自动编号主键

发布于 2024-07-09 05:35:35 字数 99 浏览 12 评论 0原文

我目前正在使用 C# 工作，我需要将一条新记录插入到一个表中，获取新的主键值，然后将其用作外键引用来插入更多记录。数据库是 MS SQL Server 2003。感谢所有帮助！

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

忆梦 2024-07-16 05:35:35

获取插入行标识的方法是使用 SCOPE_IDENTITY() 函数。如果您使用存储过程，那么这将类似于以下内容，以返回行标识作为输出参数。

CREATE PROCEDURE dbo.MyProcedure
(
    @RowId INT = NULL OUTPUT
)
AS

INSERT INTO MyTable
(
    Column1
    ,Column2
    ,...
)
VALUES
(
    @Param1
    ,@Param2
    ,...
);

SET @RowId = SCOPE_IDENTITY();

然后，您可以将该值用于任何后续插入（或者，如果您可以将数据全部传递到存储过程中，那么您可以在过程主体的其余部分中使用它）。

如果您动态传递 SQL，那么您将使用大致相同的技术，但使用带有语句分隔符的单个字符串（在 SQL 中也是 ;），例如：

var sql = "INSERT INTO MyTable (Column1, Column2, ...) VALUES (@P1, @P2, ...);" +
          "SELECT SCOPE_IDENTITY();";

然后，如果您使用 执行此操作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, ...);";

这可能不是完全正确的语法，并且您可能需要在开始时使用SET NOCOUNT ON;（我的头脑已经生锈了，因为我很少使用动态 SQL），但它应该会让你走上正轨。

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.

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.:

var sql = "INSERT INTO MyTable (Column1, Column2, ...) VALUES (@P1, @P2, ...);" +
          "SELECT SCOPE_IDENTITY();";

Then if you execute this using ExecuteScalar you'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.

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 exactly the 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.

回复收藏 0 原文

神经暖 2024-07-16 05:35:35

执行此操作的最佳方法是在 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();

或者您可以将 SCOPE_IDENTITY() 分配给要在连续语句中使用的变量。例如

DECLARE @T1 int

INSERT INTO T (Name) VALUES('Test')

SELECT @T1 = SCOPE_IDENTITY() 

INSERT INTO T2 (Name, TId) VALUES('Test', @T1)

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.

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.

DECLARE @T1 int

INSERT INTO T (Name) VALUES('Test')

SELECT @T1 = SCOPE_IDENTITY() 

INSERT INTO T2 (Name, TId) VALUES('Test', @T1)

回复收藏 0 原文

墨小墨 2024-07-16 05:35:35

如果您只是使用 SQL，那么请检查 Duncan 的答案。但是，如果您使用 LINQ，则可以创建实体，将其保存到数据库，并且 ID 参数将自动填充。

给定一个用户实体和一个用户表，它可能如下所示：

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 */
}

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.

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 */
}

回复收藏 0 原文

~没有更多了~