Transactions, locking and table releasing with MySQL






3.20/5 (5 votes)
Apr 17, 2004
3 min read

101341

1717
Implement a simple class to store multiple queries and run against a MySQL database.
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.