在事务中获取SCOPE_IDENTITY

本文关键字：IDENTITY SCOPE 获取 事务 | 更新日期: 2023-09-27 18:18:12

我有一个相当复杂的对象，包含嵌套的项列表，每个项都有自己的表。

创建此对象时，出于性能原因，我想将其插入到单个事务中，以及它的子对象和它们的所有后代。

my tables: 

Parent
|Id| Has a list of child
Child
|Id|ParentId| Has a list of Grandchild
Grandchild
|Id|ChildId|

我的事务是这样的:

INSERT INTO Parent(mycolumns) VALUES (mydata);SELECT SCOPE_IDENTITY() into @ParentId;
--insert the first child and his grandchilds
INSERT INTO Child(mycolumns, parentid) VALUES (mydata, @ParentId);SELECT SCOPE_IDENTITY() into @ChildId;
INSERT into Grandchild(mycolumns, childid) VALUES (mydata, @ChildId);
INSERT into Grandchild(mycolumns, childid) VALUES (mydata, @ChildId);
... loop through all grandchilds with this childid
--insert the second child and his grandchilds
INSERT INTO Child(mycolumns, parentid) VALUES (mydata, @ParentId);SELECT SCOPE_IDENTITY() into @ChildId;
INSERT into Grandchild(mycolumns, childid) VALUES (mydata, @ChildId);
INSERT into Grandchild(mycolumns, childid) VALUES (mydata, @ChildId);
... loop through all grandchild with this childid again...

我这样做的方式是将我所有的查询存储到一个"操作"对象中，然后在一个事务中循环它们。

 using (SqlConnection connection = new SqlConnection(this.ConnectionString))
        {
            connection.Open();
            using (SqlTransaction transaction = connection.BeginTransaction())
            {
                foreach (var operation in operations)
                {
                    using (SqlCommand command = new SqlCommand(operation.SqlCommand, connection, transaction))
                    {
                        if (operation.Parameters != null)
                        {
                            foreach (var param in operation.Parameters)
                            {
                                command.Parameters.AddWithValue(param.Name, param.Value);
                            }
                        }
                        command.ExecuteNonQuery();
                    }
                }
                transaction.Commit();
            }
        }

我的问题是我似乎找不到一种方法来存储SELECT SCOPE_IDENTITY()到一个变量(类似于此:"SELECT SCOPE_IDENTITY()到@ChildId;")在以后的命令中使用(但在同一事务中)。

在事务中获取SCOPE_IDENTITY

可以使用OUTPUT子句代替SCOPE_IDENTITY函数。这是一种更加健壮和灵活的方法。

declare @id TABLE(id int not null);
INSERT INTO Child(mycolumns, parentid) 
OUTPUT INSERTED.childid INTO @ID(id)
VALUES (mydata, @ParentId);

附加的优点是可以将多个id存储到一个表变量中。例如，您可以将ParentID存储在childdid:

旁边。
declare @id TABLE(ParentId int not null, ChildID int not null);
INSERT INTO Child(mycolumns, parentid) 
OUTPUT INSERTED.parentid, INSERTED.childid INTO @ID(ParentID, ChildID)
VALUES (mydata, @ParentId);

首先，只要一个表就可以了，不需要三个表。

id parentid
1  null
2  1
3  2

在您当前的场景中，如果可以批量插入(不止一次插入)，那么您应该使用OUTPUT子句。

完整的代码是不可见的，虽然我认为，你必须返回输出参数再次传递。

相关文章：