Geek asks and answers

.net is a complex sql transaction (SQL Server, but Oracle too, for later)

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

  • For each insertion Xinto X_Table(with X_UIDas an identifier for automatic increment) I need to insert k -require another object Yin Y_Table(with X_UIDas FK and Y_UIDas an identifier for auto-increment), since it Xcontains a list of objects k - Y.
  • Then for each inserted XI also need to add an object Zto Z_Table(with X_UIDas FK and Z_UIDas auto increment identifier).

My pseudocode 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

X_UID InsertX SP ?

, , , ?

.

... , , , .

+2
3

- SCOPE_IDENTITY(), cdel. - OUTPUT INSERT

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

, , , . # , SELECT, .. ExecuteReader().

OUTPUT , , . , , 100, 100 . , 100 , . SQL Server 2008: .

OUTPUT , , . Chained Updates.

+1

. , x Y .

Cross ..

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

, ,

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

,

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

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

, . , .

BulkCopy. .

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

1 - CreateDataTableFromList

2 - , .

select, .

+1

Make your InsertX sp structured as follows:

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 identifier and submit it to InsertY

+1
source

More articles:


All Articles