c# - 获取交易中的SCOPE_IDENTITY

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

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

我的桌子：

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;”），以便在以后的命令中使用（但在同一事务中）。

最佳答案

可以始终使用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存储在ChildID旁边：

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);