Ways to implement Nested Transactions in Nested Methods

Question

assuming i have a class like below, how can i implement something that would make all the called Methods inside MasterMethod (MethodA, MethodB, and MethodC) inherit the MySqlTransaction Object that was instantiated from 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;
            }
        }
    }
}

With my current architecture, queries are immediately committed right after a specific Method's block has reached its end instead of waiting for the DbTrans.Commit() on the bottom most part of MasterMethod's try block.

How can i make it (the MySqlTransaction Object) to behave in such a way that it must wait for all 3 nested/called Methods (MethodA, MethodB, MethodC) before committing (or rolling back) the changes to the Database ?

Answer 1

Hope the below code would help you:

You have to use 2 different connection object in order to maintain states (as per your requirement). I have posted a general DB connection code and you can use it on your own way.

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

EDIT -how to use it in your logic

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

    }