C# 在一个事务中调用多个 SQL Server 存储过程

Call multiple SQL Server stored procedures in a transaction

提问by Misiu

For usage in my current project I've created a class that allows me to call SQL Server async.

为了在我当前的项目中使用，我创建了一个允许我调用 SQL Server 异步的类。

My code looks like this:

我的代码如下所示：

internal class CommandAndCallback<TCallback, TError>
{
    public SqlCommand Sql { get; set; }
    public TCallback Callback { get; set; }
    public TError Error { get; set; }
}

class MyCodes:SingletonBase<MyCodes>
{
    private static string _connString = @"Data Source=MyDB;Initial Catalog=ED;Integrated Security=True;Asynchronous Processing=true;Connection Timeout=0;Application Name=TEST";

    private MyCodes() { }

    public void SetSystem(bool production)
    {
        _connString =
            string.Format(@"Data Source=MyDB;Initial Catalog={0};Integrated Security=True;Asynchronous Processing=true;Connection Timeout=0;Application Name=TEST", production ? "ED" : "TEST_ED");
    }

    public void Add(string newCode, Action<int> callback, Action<string> error)
    {
        var conn = new SqlConnection(_connString);
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandTimeout = 0;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = @"ADD_CODE";
        cmd.Parameters.Add("@NEW", SqlDbType.NVarChar).Value = newCode;
        cmd.Parameters.Add("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output;

        try
        {
            cmd.Connection.Open();
        }
        catch (Exception ex)
        {
            error(ex.ToString());
            return;
        }

        var ar = new CommandAndCallback<Action<int>, Action<string>> { Callback = callback, Error = error, Sql = cmd };
        cmd.BeginExecuteReader(Add_Handler, ar, CommandBehavior.CloseConnection);
    }

    private static void Add_Handler(IAsyncResult result)
    {
        var ar = (CommandAndCallback<Action<int>, Action<string>>)result.AsyncState;
        if (result.IsCompleted)
        {
            try
            {
                ar.Sql.EndExecuteReader(result);
                ar.Callback(Convert.ToInt32(ar.Sql.Parameters["@NewId"].Value));
            }
            catch (Exception ex)
            {
                ar.Error(ex.Message);
            }
        }
        else
        {
            ar.Error("Error executing SQL");
        }
    }

public void Update(int codeId, string newCode, Action callback, Action<string> error)
    {
        var conn = new SqlConnection(_connString);
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandTimeout = 0;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = @"UPDATE_CODE";
        cmd.Parameters.Add("@CODE_ID", SqlDbType.Int).Value = codeId;
        cmd.Parameters.Add("@NEW", SqlDbType.NVarChar).Value = newCode;

        try
        {
            cmd.Connection.Open();
        }
        catch (Exception ex)
        {
            error(ex.ToString());
            return;
        }

        var ar = new CommandAndCallback<Action, Action<string>> { Callback = callback, Error = error, Sql = cmd };
        cmd.BeginExecuteReader(Update_Handler, ar, CommandBehavior.CloseConnection);
    }

    private static void Update_Handler(IAsyncResult result)
    {
        var ar = (CommandAndCallback<Action, Action<string>>)result.AsyncState;
        if (result.IsCompleted)
        {
            try
            {
                ar.Sql.EndExecuteReader(result);
                ar.Callback();
            }
            catch (Exception ex)
            {
                ar.Error(ex.Message);
            }
        }
        else
        {
            ar.Error("Error executing SQL");
        }
    }

}

This may look like too much of code, but it lets me call it as so:

这可能看起来像太多的代码，但它让我可以这样称呼它：

private void Add_Click(object sender, EventArgs e)
{
   MyCodes.Instance.Add("Test",Success,Error)
}

private void Success(int newId)
{
   MessageBox.Show(newId.ToString(), "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}

private void Error(string error)
{
   MessageBox.Show(error, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

Above code works just fine for me, I'm able to do every call async.

上面的代码对我来说很好用，我可以异步执行每个调用。

Problem that I have right now is to do multiple calls as transaction - I would like to update 2 codes and add one new.

我现在遇到的问题是作为事务进行多次调用 - 我想更新 2 个代码并添加一个新代码。

Normally I would call update, then in success handler call second update, and in handler to second update I would call add that would return new id.

通常我会调用更新，然后在成功处理程序中调用第二次更新，然后在处理程序中调用第二次更新，我会调用将返回新 ID 的添加。

Something like:

就像是：

-UPDATE CODE
 |-UPDATE CODE
   |-ADD CODE (only this one return something)

But I would like to call all of those as transaction, so if add code would break updates would rollback.

但是我想将所有这些都称为事务，因此如果添加代码会破坏更新，则会回滚。

Question:

题：

Is it possible to call multiple async queries as a transaction?

是否可以将多个异步查询作为事务调用？

Can I call my above methods as transaction or do I must create separate method to call my procedures as one? (I would like to avoid this one because it's just copying the same code from one method to another)

我可以将上述方法称为事务，还是必须创建单独的方法才能将我的程序称为一个？（我想避免这个，因为它只是将相同的代码从一种方法复制到另一种方法）

I would like to add that I use .NET 3.5 so await and other nice features aren't an option.

我想补充一点，我使用 .NET 3.5，因此无法选择 await 和其他不错的功能。

回答by Remus Rusanu

Yes, it is possible. Simply call SqlConnection.BeginTransactionbefore your first call, make you assign the returned SqlTransactionobject to each SqlCommand.Transactionin the chain and call SqlTransaction.Commit()at the end.

对的，这是可能的。只需SqlConnection.BeginTransaction在第一次调用之前调用，将返回的SqlTransaction对象分配给SqlCommand.Transaction链中的每个对象并SqlTransaction.Commit()在最后调用。

回答by user2561316

  string cnnString =WebConfigurationManager.ConnectionStrings["MyString"].ConnectionString;
    SqlConnection cnn = new SqlConnection(cnnString);
    SqlTransaction transaction;

    cnn.Open();
    transaction = cnn.BeginTransaction();

    try
    {

        // Command Objects for the transaction
        SqlCommand cmd1 = new SqlCommand("sproc1", cnn);
        SqlCommand cmd2 = new SqlCommand("sproc2", cnn);

        cmd1.CommandType = CommandType.StoredProcedure;
        cmd2.CommandType = CommandType.StoredProcedure;

        cmd1.Parameters.Add(new SqlParameter("@Param1", SqlDbType.NVarChar, 50));
        cmd1.Parameters["@Param1"].Value = paramValue1;

        cmd1.Parameters.Add(new SqlParameter("@Param2", SqlDbType.NVarChar, 50));
        cmd1.Parameters["@Param2"].Value = paramValue2;

        cmd2.Parameters.Add(new SqlParameter("@Param3", SqlDbType.NVarChar, 50));
        cmd2.Parameters["@Param3"].Value = paramValue3;

        cmd2.Parameters.Add(new SqlParameter("@Param4", SqlDbType.NVarChar, 50));
        cmd2.Parameters["@Param4"].Value = paramValue4;

        cmd1.ExecuteNonQuery();
        cmd2.ExecuteNonQuery();

        transaction.Commit();
    }

    catch (SqlException sqlEx)
    {
        transaction.Rollback();
    }

    finally
    {
        cnn.Close();
        cnn.Dispose();
    }

回答by bala

public class Command
{
    public string sql { get; set; }
    public CommandType cmdType { get; set; }
    public Dictionary<string, object> parameter { get; set; } = null;
}

    private Command insertInvoice(Invoice invoice)
    {
        try
        {
            Dictionary<string, object> parameterLocal = new Dictionary<string, object>();

            parameterLocal.Add("p_customerId", invoice.customerId);
            parameterLocal.Add("p_invoiceNo", invoice.invoiceNo);
            parameterLocal.Add("p_invoiceDate", invoice.invoiceDate);
            parameterLocal.Add("p_invoiceAmount", invoice.invoiceAmount);                
            parameterLocal.Add("p_withInvoice", invoice.withInvoice);

            return (new Command { sql = "sp_insertInvoice", cmdType = CommandType.StoredProcedure, parameter = parameterLocal });
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    private Command insertInvoiceModel(InvoiceModel invoiceModel)
    {
        try
        {
            Dictionary<string, object> parameterLocal = new Dictionary<string, object>();

            parameterLocal.Add("p_invoiceNo", invoiceModel.invoiceNo);
            parameterLocal.Add("p_model", invoiceModel.model);
            parameterLocal.Add("p_quantity", invoiceModel.quantity);
            parameterLocal.Add("p_unitPrice", invoiceModel.unitPrice);

            return (new Command { sql = "sp_insertInvoiceModel", cmdType = CommandType.StoredProcedure, parameter = parameterLocal });
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

 List<Command> commandList = new List<Command>();

 cmd = insertInvoice(invoicesave);

 commandList.Add(cmd);

 cmd = insertInvoiceModel(invoiceModelSave);

 commandList.Add(cmd);

        try
        {
            erplibmain.erpDac.runOleDbTransaction(commandList);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    public void runOleDbTransaction(List<Command> commandList)
    {
        OleDbConnection erpConnection = new OleDbConnection(ErpDalMain.connectionstring);
        erpConnection.Open();

        OleDbCommand erpCommand = erpConnection.CreateCommand();
        OleDbTransaction erpTrans;

        // Start a local transaction
        erpTrans = erpConnection.BeginTransaction();
        // Assign transaction object for a pending local transaction
        erpCommand.Connection = erpConnection;
        erpCommand.Transaction = erpTrans;

        try
        {
            foreach (Command cmd in commandList)
            {
                erpCommand.CommandText = cmd.sql;
                erpCommand.CommandType = cmd.cmdType;

                foreach (KeyValuePair<string, object> entry in cmd.parameter)
                {
                    erpCommand.Parameters.AddWithValue(entry.Key, entry.Value);
                }

                erpCommand.ExecuteNonQuery();

                erpCommand.Parameters.Clear();
            }

            erpTrans.Commit();
        }
        catch (Exception e)
        {
            try
            {
                erpTrans.Rollback();
            }
            catch (OleDbException ex)
            {
                if (erpTrans.Connection != null)
                {
                    throw ex;
                }
            }

            throw e;
        }
        finally
        {
            erpConnection.Close();
        }
    }