棘手的sql事务(sql Server，但稍后也要考虑Oracle)

本文关键字：sql Oracle 事务 Server | 更新日期: 2023-09-27 17:58:40

我必须执行批量操作bool AddEntitiesX(List<X>):

  • 对于每次将X插入X_Table（以X_UID作为自动递增ID），我必须将k倍的另一个实体Y插入Y_Table（以X_UID作为FK，Y_UID作为自动递增标识），因为X包含k+Y实体的列表
  • 然后，对于每个插入的X，我还需要在Z_Table中插入一个Z实体（X_UID为FK，Z_UID为自动递增ID）

我的伪代码将如下所示：

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

如何从InsertX SP检索X_UID以传递到下一个存储的进程？

如果没有办法，那么既然我不能在一个存储过程中拥有这个大事务，我应该如何对它进行建模？

我想知道使用事务处理从业务到数据层的此类操作的最佳实践。

谢谢。。。如果我的问题不够清楚，请告诉我。

棘手的sql事务(sql Server，但稍后也要考虑Oracle)

一种方法是使用SCOPE_IDENTITY()，就像cdel已经建议的那样。另一种方法是使用INSERT 的OUTPUT子句

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

这将插入记录并生成一个结果集，其中包含插入的行生成的标识值。在C#中，读取这个结果集就像执行SELECT一样，即使用ExecuteReader()。

使用OUTPUT子句的一个优点是它可以可靠地返回多个行ID的唯一方法。假设您插入的不是一个，而是100个，您可以在一个结果集中返回所有100个插入行的ID。如果您想知道如何一次插入100行，请参阅SQLServer2008中的数组和列表：使用表值参数。

使用OUTPUT子句的另一个优点是可以将两个语句链接到一个语句中，请参阅链接更新。

您可以使用批量复制。使用此方法，首先在新表中大容量插入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语句。

让InsertX sp的结构如下：

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

呼叫后，您获得ID并将其输入InsertY

相关文章：