CODEWITHSUNDEEP
c#mysqlmysql-connector
Adam Knights
Adam Knights

Reputation: 2151

C# MySQL use transactions in a loop safely in same connection

Having opened a connection I want to do a transaction multiple times, I don't want to open the connection again and again because this would greatly hinder performance. With the below code I get an exception the second time in the loop of Transaction has already been committed or is not pending.

My question is - what do I need to add to the end of the loop/ beginning of the code to keep using transactions on the same MySql connection?

The code:

    using (MySqlConnection conn = new MySqlConnection(hostString))
    {
        try
        {
            conn.Open();
            conn.ChangeDatabase(databaseName);

            /// put in db using a transaction
            MySqlCommand cmd = conn.CreateCommand();
            MySqlTransaction trans;
            trans = conn.BeginTransaction();
            cmd.Connection = conn;
            cmd.Transaction = trans;

            while (someStuff)
            {
                try
                {
                    cmd.CommandText = getPersonUpdateString();
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = getBookUpdateString();
                    cmd.ExecuteNonQuery();

                    trans.Commit();
                }
                catch (Exception ex)
                {
                    writeError("", ex);
                    try
                    {
                       trans.Rollback();
                    }
                    catch (MySqlException)
                    {
                       writeError("", ex);
                    }
                }
            }
         }
      }

Upvotes: 1

Views: 4516

Answers (1)

O. Jones
O. Jones

Reputation: 108651

After you commit the transaction, you have to start another one. This means you need to move the BeginTransaction method call into the loop, like so.

        while (someStuff)            
        {
            MySqlTransaction trans;
            try
            {
                trans = conn.BeginTransaction();

                cmd.CommandText = getPersonUpdateString();
                cmd.ExecuteNonQuery();

                cmd.CommandText = getBookUpdateString();
                cmd.ExecuteNonQuery();

                trans.Commit();
            }
            catch (Exception ex)
            {
                writeError("", ex);
                try
                {
                   trans.Rollback();
                }
                catch (MySqlException)
                {
                   writeError("", ex);
                }
            }
        }

Upvotes: 1

Related Questions