.net - tricky sql transaction (SQL Server, but Oracle in mind too, for later)

Question

I have to perform a bulk operation bool AddEntitiesX(List<X>):

• For each insert of X into X_Table (with X_UID as auto-increment ID), I have to insert k-times another entity Y into Y_Table (with X_UID as FK and Y_UID as auto-increment ID), since X contains a list of k-Y entities.
• Then, for each inserted X, I need to insert also a Z entity in Z_Table (with X_UID as FK and Z_UID as auto-increment ID).

My pseudo-code will look like this:

// cannot use 'TransactionScope' since there are problems with Oracle
// so, prepare SqlTransaction
foreach (X)
{
 //1. call 'InsertX' SP

 foreach (Y in x.ListY)
   //2. call 'InsertY' SP

 //3. call 'InsertZ' SP
}
// commit transaction

How can I retrieve the X_UID from InsertX SP to pass to the next stored procs?

If there is no way, then since I cannot have this big transaction in one stored procedure, how should I model it?

I would like to know best practices to handle this kind of operations from business to data layer using transactions.

Thank you... and please let me know if my question is not clear enough.

Answer 1

You can use Bulk Copy. Using this, first bulk insert all the records of x in a new table and bulk insert all records of Y in a new table.

Now you can use Cross joins in between these two new tables..like below

Select X.TableXVal, Y.TableYVal from NewTableX X
Cross Join NewTableY Y

This query can be written in the Stored Procedure and stored procedure can be called like below

using (System.Data.SqlClient.SqlConnection con = new SqlConnection("YourConnection string")) { 
    con.Open(); 
    SqlCommand cmd = new SqlCommand(); 
    string expression = "Parameter value"; 
    cmd.CommandType = CommandType.StoredProcedure; 
    cmd.CommandText = "Your Stored Procedure"; 
    cmd.Parameters.Add("Your Parameter Name", 
                SqlDbType.VarChar).Value = expression;    
    cmd.Connection = con; 
    using (IDataReader dr = cmd.ExecuteReader()) 
    { 
        if (dr.Read()) 
        { 
        } 
    } 
}

You can create the sample tables like below

create table NewTableX
(
    ID int Primary Identity(1,1),
    TableXVal int
)

create table NewTableY
(
    ID int Primary Identity(1,1),
    TableYVal int
)

In this way, you can skip inserting the records one by one. hope this will help you.

For more information about using BulkCopy. Below is the code.

private void CreateDataTableFromList()
        {
            //supose you have list for X like below
            List<int> x = new List<int>();
            x.Add(1);
            x.Add(2);
            x.Add(3);
            x.Add(4);

            //supose you have list for Y like below
            List<int> y = new List<int>();
            y.Add(1);
            y.Add(2);
            y.Add(3);
            y.Add(4);


            DataTable dt = new DataTable();
            DataColumn dc;
            DataRow dr;

            dc = new DataColumn();
            dc.DataType = System.Type.GetType("System.Int32");
            dc.ColumnName = "TableXVal";
            dt.Columns.Add(dc);

            dr = dt.NewRow();
            dr["TableXVal"] = 1;
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr["TableXVal"] = 2;
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr["TableXVal"] = 3;
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr["TableXVal"] = 4;
            dt.Rows.Add(dr);


            SqlBulkCopy copy = new SqlBulkCopy("Your Connection String");
            copy.DestinationTableName = "NewTableX";
            copy.WriteToServer(dt);


            dt = new DataTable();

            dc = new DataColumn();
            dc.DataType = System.Type.GetType("System.Int32");
            dc.ColumnName = "TableYVal";
            dt.Columns.Add(dc);

            dr = dt.NewRow();
            dr["TableYVal"] = 1;
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr["TableYVal"] = 2;
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr["TableYVal"] = 3;
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr["TableYVal"] = 4;
            dt.Rows.Add(dr);

            copy = new SqlBulkCopy("Your Connection String");
            copy.DestinationTableName = "NewTableY";
            copy.WriteToServer(dt); 
        }

Step1 - Use CreateDataTableFromList function

Step 2 - Call stored procedure as mentioned above

Your stored procedure must have the select statement as mentioned above.

Answer 2

Have your InsertX sp structured like this:

ALTER PROCEDURE dbo.InsertX
(
    -- other parameters
    @ID int = null OUTPUT,
    @ErrMsg nvarchar(512) = null OUTPUT
)
AS
SET NOCOUNT ON
DECLARE @ret as int
SET @ret = 1
BEGIN TRY
    INSERT INTO [dbo].[XTable] 
        ([Column1]) 
    VALUES 
        (null)
    SET @ID = SCOPE_IDENTITY()
    SET @ErrMsg = 'OK'
END TRY
BEGIN CATCH
    SET @ErrMsg = ERROR_MESSAGE()
    SET @ret = -1
END CATCH
RETURN @ret

After the call you get the ID and feed it into InsertY

Answer 3

One way is to use SCOPE_IDENTITY(), like cdel already suggested. Another way is to use the OUTPUT clause of INSERT

INSERT INTO table (field, field, ...) 
OUTPUT INSERTED.ID
VALUES (@value, @value, ...);

This inserts the record and also produces a result set, which contains the inserted row generated identity value. In C# you read this result set just as if you'd have executed a SELECT, ie. you use ExecuteReader().

One advantage of using OUTPUT clause is that is the only way it can reliable return multiple row IDs. Say you insert not one, but 100, you can get back the IDs of all 100 inserted rows in a single result set. In case you wonder how to insert 100 rows in one swoop, see Arrays and Lists in SQL Server 2008: Using Table-Valued Parameters.

Another advantage of using OUTPUT clause is that you can chain two statements into a single one, see Chained Updates.