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.