c# - 在嵌套方法中实现嵌套事务的方法

Question

假设我有一个如下所示的类，我怎样才能实现一些东西，使 MasterMethod (MethodA, MethodB, and MethodC) 中的所有被调用方法都成为可能？继承MySqlTransaction从 MasterMethod 实例化的对象？

private MySqlConnection OpenConnection() {
    try {
        MySqlConnection DbConn = new MySqlConnection("~connectionstring");
        DbConn.Open();

        return DbConn;
    } catch(Exception Ex) {
        throw Ex;
    }
}

public void MasterMethod() {
    using(MySqlConnection DbConn = OpenConnection()) {
        using(MySqlTransaction DbTrans = DbConn.BeginTransaction()) {
            try {
                MethodA();
                MethodB(Param1);
                MethodC(Param1, Param2);

                DbTrans.Commit();
            } catch(Exception Ex) {
                DbTrans.Rollback();
                throw Ex;
            }
        }
    }
}

public void MethodA() {
    using(MySqlConnection DbConn = OpenConnection()) {
        using(MySqlTransaction DbTrans = DbConn.BeginTransaction()) {
            try {
                // Lots and Lots of things to do

                DbTrans.Commit();
            } catch(Exception Ex) {
                DbTrans.Rollback();
                throw Ex;
            }
        }
    }
}

public void MethodB(int Param1) {
    using(MySqlConnection DbConn = OpenConnection()) {
        using(MySqlTransaction DbTrans = DbConn.BeginTransaction()) {
            try {
                // Lots and Lots of things to do

                DbTrans.Commit();
            } catch(Exception Ex) {
                DbTrans.Rollback();
                throw Ex;
            }
        }
    }
}

public void MethodC(string Param1, string Param2) {
    using(MySqlConnection DbConn = OpenConnection()) {
        using(MySqlTransaction DbTrans = DbConn.BeginTransaction()) {
            try {
                // Lots and Lots of things to do

                DbTrans.Commit();
            } catch(Exception Ex) {
                DbTrans.Rollback();
                throw Ex;
            }
        }
    }
}

在我当前的架构中，在特定方法的 block 到达末尾后立即提交查询，而不是等待 DbTrans.Commit()在 MasterMethod 的 try block 的最底部。

我怎样才能做到(the MySqlTransaction Object)其行为方式必须等待所有 3 个嵌套/调用的方法 (MethodA, MethodB, MethodC)在提交(或回滚)数据库更改之前？

Answer 1

希望以下代码对您有所帮助:

您必须使用 2 个不同的连接对象才能维护状态(根据您的要求)。我已经发布了通用的数据库连接代码，您可以按照自己的方式使用它。

public class ConnectionClass
{
    public string ConnectionString = ConfigurationManager.ConnectionStrings["xyz"].ConnectionString;
    public SqlConnection conTrans = new SqlConnection();
    public SqlTransaction dbTrans;
    public SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["xyz"].ConnectionString);
    public SqlTransaction sqlTrans;



    public bool BeginConTrans()
    {
        try
        {
            conTrans.ConnectionString = ConnectionString;
            conTrans.Open();
            dbTrans = conTrans.BeginTransaction();
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }


    public bool CommitConTrans()
    {
        try
        {
            dbTrans.Commit();
            conTrans.Close();
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }


    public bool RollbackConTrans()
    {
        try
        {
            dbTrans.Rollback();
            conTrans.Close();
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }


    public bool ExecuteNonQueryTrans(string proc, SqlParameter[] par)
    {
        SqlCommand cmd = new SqlCommand();
        try
        {
            if (par != null)
            {

                for (int i = 0; i <= par.Length - 1; i++)
                {
                    cmd.Parameters.Add(par[i]);
                }
            }
            cmd.Connection = conTrans;
            cmd.Transaction = dbTrans;
            cmd.CommandText = proc;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }
}

编辑 - 如何在您的逻辑中使用它

public void MasterMethod()
    {
        try
        {
            BeginConTrans();
            // your methods A,B,C
            //must use ExecuteNonQueryTrans for your get/put data

        }
        catch (Exception ex)
        {
            //if any method fails handle exception and rollback the transcation
            RollbackConTrans();
        }
        CommitConTrans(); // if success ,commit the transcation

    }