c# - 在事务中获取 SCOPE_IDENTITY

Question

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

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

我的表:

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() into @ChildId;”)以在以后的命令中使用(但在相同的交易)。

Answer 1

您始终可以使用 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);