c# – 在事务中调用多个SQL Server存储过程

我的代码如下所示：

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,Sql = cmd };
        cmd.BeginExecuteReader(Update_Handler,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");
        }
    }

}

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

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",MessageBoxIcon.Error);
}

上面的代码对我来说很好,我可以做每个调用异步.

我现在的问题是做多个调用作为事务 – 我想更新2个代码并添加一个新的.

通常我会调用更新,然后在成功处理程序调用第二次更新,并在处理程序中第二次更新我将调用add将返回新的id.

就像是：

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

但是我想将所有这些都称为事务,所以如果添加代码会中断更新将会回滚.

题：

可以作为事务调用多个异步查询吗？

我可以将我的上述方法称为事务,还是必须创建单独的方法来将我的过程称为一个？ (我想避免这个,因为它只是将相同的代码从一种方法复制到另一种方法)

我想补充说,我使用.NET 3.5等待和其他不错的功能不是一个选项.