How to start DB transaction, run multiple queries on MySQL then commit or rollback using c#?

Question

I have a generic class that is allowing me to run queries on MySQL database.

The only issue that I am seeing with this class is that it open a connections run a query and close the connection.

Now, I need to be able to

START TRANSACTION;
SELECT ...
INSERT INTO table1...
INSERT INTO table2...
INSERT INTO table3...
if all the above queries worked with no error COMMIT; otherwise ROLLBACK;

Yes, I need a transaction and I need to make sure all queries run 100% or I need to rollback and correct the error prior tarting again.

How can I modify my class to allow me to handle the steps above?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using System.Windows.Forms;


namespace RM
{
    public class dbConnetion
    {
        //private OdbcConnection conn; 
        private static readonly string mServer = "localhost";
        private static readonly string mDatabase = "my_db_name";
        private static readonly string mUid = "my_db_user";
        private static readonly string mPassword = "my_user_password";
        private static readonly string mPort = "3306";
        private string conn_string = String.Format("server={0};user={1};database={2};port={3};password={4};", mServer, mUid, mDatabase, mPort, mPassword);


        public string SYSTEM_NAME { get; set; }

        public dbConnetion()
        {
            Initilize_System_Settings();
        }

        // query the data base
        public IEnumerable<T> getData<T>(string query, List<MySqlParameter> pars, Func<IDataRecord, T> transform)
        {
            using (var conn = new MySqlConnection(conn_string))
            using (var cmd = new MySqlCommand(query, conn))
            {
                if (pars != null)
                {
                    foreach (MySqlParameter p in pars)
                    {
                        cmd.Parameters.Add(p);
                    }
                }

                conn.Open();

                using (var rdr = cmd.ExecuteReader())
                {

                    while (rdr.Read())
                    {
                        yield return transform(rdr);
                    }
                }

                conn.Close();
            }
        }

        // query the data base
        public T getValue<T>(string query, List<MySqlParameter> pars)
        {
            T value;
            using (var conn = new MySqlConnection(conn_string))
            using (var cmd = new MySqlCommand(query, conn))
            {

                if (pars != null)
                {
                    foreach (MySqlParameter p in pars)
                    {
                        cmd.Parameters.Add(p);
                    }
                }


                try
                {
                    conn.Open();
                    object rawValue = cmd.ExecuteScalar();

                    if (rawValue != null)
                    {
                        value = (T)Convert.ChangeType(rawValue, typeof(T));
                    }
                    else
                    {
                        value = default(T);
                    }

                }
                catch (Exception ex)
                {

                    Common.Alert(ex.ToString(), "SQL Error");
                    value = default(T);

                }
                finally
                {
                    conn.Close();
                }

            }

            return value;
        }

        public bool processQuery(string strSQL, List<MySqlParameter> pars)
        {
            bool toReturn = true;
            using (var conn = new MySqlConnection(this.conn_string))
            using (var cmd = new MySqlCommand(strSQL, conn))
            {

                foreach (MySqlParameter param in pars)
                {
                    cmd.Parameters.Add(param);
                }

                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (MySqlException ex)
                {
                    Common.Alert(ex.ToString(), "SQL Error");
                    toReturn = false;
                }
                finally
                {
                    conn.Close();
                }
            }

            return toReturn;
        }
    }
}

My first thoughts was to add a new parameter to the getData method to allow me to not open/close connection if there is an open transaction, like this

// query the data base
        public IEnumerable<T> getData<T>(string query, List<MySqlParameter> pars, Func<IDataRecord, T> transform, bool inTransaction = false)
        {
            using (var conn = new MySqlConnection(conn_string))
            using (var cmd = new MySqlCommand(query, conn))
            {
                if (pars != null)
                {
                    foreach (MySqlParameter p in pars)
                    {
                        cmd.Parameters.Add(p);
                    }
                }
                if(! inTransaction){
                    conn.Open();
                }
                using (var rdr = cmd.ExecuteReader())
                {

                    while (rdr.Read())
                    {
                        yield return transform(rdr);
                    }
                }
                if(! inTransaction){
                    conn.Close();
                }
            }
        }

but I think this is not going to work because of the using statement

score 0 · Answer 1 · edited May 23 '17 at 10:33

0

trans = Conn.BeginTransaction();
trans.Commit();
trans.Rollback();

Cannot access SqlTransaction object to rollback in catch block

edited May 23 '17 at 10:33

Community

1
1

answered Jan 12 '15 at 00:48

Richard Williamson

81
2

score 0 · Answer 2 · answered Jan 12 '15 at 00:53

0

You have to determine where to start and end the transaction then,

using (var conn = new MySqlConnection(conn_string))
{
    var tx = conn.BeginTransaction();
    try
    {
        using (var cmd1 = new MySqlCommand(query1, conn))
        {
           cmd1.Transaction = tx;
           //do cmd 1 stuff here
        }

        using (var cmd2 = new MySqlCommand(query2, conn))
        {
           cmd2.Transaction = tx;
           //do cmd 1 stuff here
        }

       //do other commands....

       tx.Commit(); //or Rollback() based on results
    }
    catch (Exception ex)
    {
       tx.Rollback();
       throw ex;
    }
}

answered Jan 12 '15 at 00:53

Low Flying Pelican

5,974
1
32
43

Thank you for your help. Is it possible to control the steps outside the code? I need to be able to run the some queries from within my form and read results. for example insert into table1 then select last_insert_id() and use that value in a different query in the same transaction. – Jaylen Jan 12 '15 at 00:58
I mean, I am looking for a generic methods like the rest of my connection class where I can control from outside my class. for example var db = new dbConnection; db.StartTranaction(); db.getData(....), f = db.processQuery(...), s = db.processQuery(...) and then I can do `if(s && f) db.commit(); else db.rollback();` – Jaylen Jan 12 '15 at 01:11
In your methods you have to remove any connection opening and closing sections, and connection object and transaction object needs to pass into these generic methods (such as public bool processQuery(MySqlConnection con,MySqlTransaction tx,string strSQL, List pars)) the transaction committing/rolling back and connection opening and closing has to be handled in your specific place of calling – Low Flying Pelican Jan 12 '15 at 01:20

How to start DB transaction, run multiple queries on MySQL then commit or rollback using c#?

2 Answers2