Transactions, locking and table releasing with MySQL

• Download source files - 4.59 Kb
• Download demo project - 18.8 Kb

Introduction

In my last project, I needed to develop a class that simplifies the execution of multiples queries against my MySQL database. I developed the class for ease of use and "do more for less". What I want to show here is how to:

• Create the OdbcExecuter class.

Using the Code

The class I implemented is based on one Windows form which also allows feedback to users about the transaction. To use it, you only have to add the class to your project. Start by adding a Windows form and declaring some private variables:

Private variables:

public class OdbcExecuter : System.Windows.Forms.Form
{
    // the string array with queries

    private string[] QueryCollection = new string[1];
    // the connection string

    private string connstring;
    // the message show to user while executing

    private string waitmessage = "EXECUTING THE QUERIES";
    // total commands to execute

    private int nCommands = 0;

Next, the properties to set the above variables:

    /// <SUMMARY>

    /// The connection string to use

    /// </SUMMARY>

    public string ConnectionString
    {
        get
        {
            return(connstring);
        }
        set 
        {
            this.connstring = value;
        }
    }


    /// <SUMMARY>

    /// The message show to user in form

    /// </SUMMARY>

    public string WaitMessage
    {
        get
        {
            return(waitmessage);
        }
        set
        {
            // WaitMessage represents a label type object

            WaitMessage.Text = value;
        }
    }

OK, at this phase, we coded the two properties to treat the internal variables for use in our future methods. As the string array of queries grows, we must resize it. Let's start by adding the private function to deal with the array growth:

    /// <summary>

    /// Add an query to the queries collection

    /// </summary>

    /// <param name="sQuery">the query to add</param>

    private Array ResizeArray(Array QueryArray, int NewSize) 
    {
        // Check to see the type of the elements

        Type QueryArrayType = QueryArray.GetType().GetElementType();
        // Build our new array maintaining the original type

        Array NewQueryArray = Array.CreateInstance(QueryArrayType, NewSize);
        // Copy the values set to the new array

        Array.Copy(QueryArray, 0, NewQueryArray, 0, 
                   Math.Min(QueryArray.Length, NewSize));
                
        return NewQueryArray;
    }

So, how this function works. First, we get the type of elements on the array, declare a new object of type Type and build a new array from the original array. Next, copy our existing data from the original array to our new fresh array and finally, return it. With this function, we can add as much queries as we need, that this function will just resize our array as needed.

Next, we will code our LockTables and ReleaseTables functions, but first, a little explanation about the MySQL functions we will use.

Since the release of MySQL version 3.21.27, the database engine permits user level locks. If you have MySQL installed on your system, you can try out the GET_LOCK and RELEASE_LOCK functions. Lately, MySQL developers added the IS_FREE_LOCK function to check the status of the locks. This function is only available from the version 4.0.2.

Now, back to code. The LockTable is the function dealing with lock and feedback to user, it will continually cycle until it gets the lock. I didn't add a timeout control variable because I didn't need it. Finally, the ReleaseTable function will deal with the release of database user level lock. I coded the LockTable and ReleaseTable functions with try / catch block to deal safely with database exceptions. The functions will return true if everything goes well or false if an exception occurred.

Finally, the core public functions: AddQuery and ExecuteQueries. The function AddQuery simply adds queries to our QueryCollection string array. It uses the ResizeArray function described above to grow the string array QueryCollection as needed. ExecuteQueries, is our main function that will perform all the action. It makes a call to LockTable function to lock the tables at user level, initiates a Transaction object, places the queries against the database, commits or rollbacks the transaction and finally calls the ReleaseTable function and returns the result from the execution operation which can be true for things run well, or false on exception.

private bool LockTables()
    {
        // Build the connection object

        OdbcConnection OdbcMyConnection = new OdbcConnection(connstring);
        OdbcMyConnection.Open();
        // Our var to see how things end up

        bool bStatus = false;

        // Build the command object

        OdbcCommand OdbcMyCommand = OdbcMyConnection.CreateCommand();
        
        // In MySQL the function IS_FREE_LOCK is called by:

        // IS_FREE_LOCK(name_of_lock)

        OdbcMyCommand.CommandText = "SELECT IS_FREE_LOCK('OdbcExecuter')";
        int nStatus = 0;
        while (!bStatus)
        {
            // Execute function IS_FREE_LOCK and see what returns

            bStatus = Convert.ToBoolean(OdbcMyCommand.ExecuteScalar());
            if (bStatus) break;  // Could lock the table, let's exit the cycle


            // Still trying to lock, let's give feedback to user

            ldots.Text += ".";                
            Thread.Sleep(400);
            Application.DoEvents();                
        }

        // The lock is free for us, let's lock

        try
        {
            // Execute lock query

            // In MySQL the function GET_LOCK is called by:

            // GET_LOCK(name_of_lock, timeout seconds)

            OdbcMyCommand.CommandText = "SELECT GET_LOCK('OdbcExecuter',60)";
            nStatus = Convert.ToInt32(OdbcMyCommand.ExecuteScalar());
            if (nStatus == 1) bStatus = true;
            else bStatus = false;
        }
        catch (OdbcException e)
        {
            // Something bad happened, let the user know

            MessageBox.Show(this, e.ToString());
            bStatus = false;
        }

        // Close the connection object and return the result

        OdbcMyCommand.Dispose();
        OdbcMyConnection.Close();
        return bStatus;
    }

    private bool ReleaseTables()
    {
        // Build the connection object

        OdbcConnection OdbcMyConnection = new OdbcConnection(connstring);
        OdbcMyConnection.Open();
        // Our var to see how things end up

        bool bStatus = false;

        // Build our command object

        OdbcCommand OdbcMyCommand = OdbcMyConnection.CreateCommand();
        
        // See if our tables are already loocked

        try
        {
            // Execute the release lock query

            int nStatus = 0;
            // In MySQL the function RELEASE_LOCK is called by:

            // RELEASE_LOCK(name_of_lock)

            OdbcMyCommand.CommandText = "SELECT RELEASE_LOCK('OdbcExecuter')";
            nStatus = Convert.ToInt32(OdbcMyCommand.ExecuteScalar());
            if (nStatus == 1) bStatus = true;
            else bStatus = false;
        }
        catch (OdbcException e)
        {
            // Something bad happened, let the user know

            MessageBox.Show(this, e.ToString());
            bStatus = false;
        }

        // Close the connection object and return the result

        OdbcMyCommand.Dispose();
        OdbcMyConnection.Close();            
        return bStatus;
    }

The public functions:

    /// <summary>

    /// Add an query to the queries collection

    /// </summary>

    /// <param name="sQuery">the query to add</param>

    public void AddQuery(string sQuery)
    {            
        // Check to see if our string array as elements

        if (nCommands > 0)
            // Resize the array and cast to prevent an exception throw

            QueryCollection = (string[])ResizeArray(QueryCollection, 
                                           QueryCollection.Length + 1);

        // Store the new query passed

        QueryCollection[nCommands] = sQuery;
        nCommands++;
    }

    /// <summary>

    /// Executes the stored queries in the query collection

    /// </summary>

    /// <returns>operation result</returns>

    public bool ExecuteQueries()
    {
        // Our var to see how things end up

        bool bStatus = false;

        // Force the form to show to the user

        if (!this.Focus())
        {
            this.TopMost = true;            // force window to show on top

            this.ShowInTaskbar = false;     // hide window from taskbar

            this.Show();                    // show window

        }

        // Build the connection object

        OdbcConnection OdbcMyConnection = new OdbcConnection(connstring);
        OdbcMyConnection.Open();

        // Start our transaction

        OdbcCommand OdbcMyCommand = OdbcMyConnection.CreateCommand();
        OdbcTransaction transact = 
          OdbcMyConnection.BeginTransaction(IsolationLevel.ReadCommitted);
        OdbcMyCommand.Transaction = transact;

        if (LockTables())
        {
            try
            {
                // Execute the queries in our QueryCollection array

                for (int nQuerys = 0; nQuerys < nCommands; nQuerys++)
                {
                    OdbcMyCommand.CommandText = QueryCollection[nQuerys];
                    OdbcMyCommand.ExecuteNonQuery();
                }

                // Commit our queries to the database                            

                transact.Commit();
                bStatus = true;
            }
            catch (Exception cmex)
            {
                try
                {
                    // Something bad happened, let's roll back our transaction

                    transact.Rollback();
                }
                catch (OdbcException ex)
                {
                    // Something bad happened, let the user know

                    MessageBox.Show(this, ex.ToString());
                }
                // Let the user know what happened with the transaction

                MessageBox.Show(this, cmex.ToString());
                bStatus = false;
            }
            finally
            {
                // Finally, let's end up our objects

                OdbcMyCommand.Dispose();
                OdbcMyConnection.Close();
            }
        }
        // We finish executing the queries, let's release the tables

        ReleaseTables();
        this.Hide();
        return bStatus;
    }
    
    public void CleanQueries()
    {
        QueryCollection.Initialize();
        QueryCollection = new string[1];
        nCommands = 0;
    }

OK, this concludes our coding for the OdbcExecuter class. If you want to include it on your projects, be free to download the class and implement.