CODEWITHSUNDEEP
c#sql-serversql-server-2008t-sqltransactions
brem
brem

Reputation: 33

Get SCOPE_IDENTITY mid-transaction

I have a rather complex object, containing nested lists of items, each of them with their own table.

When creating this object, i would like to insert it, along with it's children and all their descendants in a single transaction for performance reason.

my tables : 

Parent
|Id| Has a list of child

Child
|Id|ParentId| Has a list of Grandchild

Grandchild
|Id|ChildId|

Here is what my transaction would look like : 

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

The way i do this is by storing all my Queries into a 'operation' object, and then looping through them in a transaction.

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

my problem is i cant seem to find a way to store SELECT SCOPE_IDENTITY() into a variable (something akin to this : "SELECT SCOPE_IDENTITY() into @ChildId;") to use in a later command (but in the same transaction).

Upvotes: 2

Views: 1656

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

First of all there is no need for three tables when only one table will do.

id parentid
1  null
2  1
3  2

In your current scenerio,if there can be bulk insert (more than one insert) then yes you should use OUTPUT clause.

Your complete code is not visible,though i think,you hv to return output parameter to pass it again.

Upvotes: 0

under
under

Reputation: 3067

Instead of SCOPE_IDENTITY function you can always use OUTPUT clause. This is a much more robust and flexible approach. 

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

Added advantage is that you can store multiple IDs into a table variable. For example, you could store the ParentID next to the 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);

Upvotes: 1

Related Questions