Proper Etiquette for using MySQL in C# (Part of 1 of 3) – IDisposable

Many tutorials describing how to access MySQL databases in C# are typically missing a major point of emphasis or two. Stack Overflow alone is filled with questions where the author violates best practices in their code snippet. In this three part series, I would like to go over some major points of etiquette when using the MySQL connector objects in C#. These articles are actually likely applicable to other ADO.NET Data Providers, but I will be using a MySQL database as my data store and so I will be using the ADO.NET driver for MySQL (MySQL Connector/NET) in these articles.

So let’s look at some proper etiquette for interacting with databases in .NET.

The series will be broken into the following parts:

1. Use ‘using statements’ on your disposable objects which interface with the database.
2. Parameterize your queries (aka Prepared Statements)
3. Ensure that your Data Reader code is readable

Use ‘using statements’ on your disposable objects which interface with the database.

Admittedly, this first point is not specific to interfacing with databases, but I see it violated more often by programmers using MySQL connector objects than anything else. For readers who are not familiar with IDisposable, I suggest you get yourself acquainted if you plan on spending any serious amount of time with the .NET Framework. Here is an excerpt from the MSDN documentation linked above:

The primary use of this interface is to release unmanaged resources. The garbage collector automatically releases the memory allocated to a managed object when that object is no longer used. However, it is not possible to predict when garbage collection will occur. Furthermore, the garbage collector has no knowledge of unmanaged resources such as window handles, or open files and streams.

Use the Dispose method of this interface to explicitly release unmanaged resources in conjunction with the garbage collector. The consumer of an object can call this method when the object is no longer needed.

This combined with the using statement is a great way to ensure that disposable objects get disposed. The using statement is syntactic sugar for a try/finally block where the Dispose() method is called on the object in the finally block (after a proper null-check).

So what does that specifically mean for MySqlConnection objects? If you don’t dispose of your database connection when you are done with it, then the connection does not get returned back to the connection pool. Of course the finalizer will fire eventually and this will release the database connection, but that is at some undeterministic time in the future. A best practice in software engineering is to manage your resources efficiently and that is being violated when you fail to dispose of your MySQL objects and free the associated resources in a deterministic manner.

So what will happen if you don’t release your database connections as soon as you are done with them? You will witness performance problems due to the fact that the database connection pool is of limited size and when that limit is hit – new connections will have to wait for old connections to be released before opening. Long delays when opening up connections to databases is going to negatively impact the quality of your software application. It is generally best practice to keep a connection open only as long as required.

If a class implements IDisposable, then wrap it in a using statement. There are some rare exceptional circumstances such as WCF client proxies where the use of the using statement is not advised, but ADO.NET objects are certainly not one of these cases. So be a good code citizen in the .NET Framework and wrap your MySql connector objects in using statements.

Some may argue that if they are calling Close() on their MySqlConnection object then this is good enough as it releases the connection back into the connection pool. There may actually be times where you may wish to simply close the connection (as the connection object can then be reused) instead of disposing of the connection, but even then you should wrap the object in a using statement for two reasons:

1. The code may throw an exception and the Close() method may not be called.
2. You may forget to call the Close() method when you are done with the connection object.

I recommend NOT doing this:

/* 1. Using statements where art thou? 
 * Unmanaged resources are not guaranteed to be released as soon as they are no longer needed.
 */
MySqlConnection conn = new MySqlConnection(ConnectionString);
conn.Open();

// DO SOME STUFF

// This may not be called due to a thrown exception or the programmer may forget
conn.Close();

I recommended that you do this instead:

using (var conn = new MySqlConnection(ConnectionString))
{
     conn.Open();
     // SO SOME STUFF
} // Dispose will call Close
// Even in the event of a thrown exception we are safe in knowing that 
// the connection has been released.

In a related matter, some may also argue that just calling Close() on your MySqlDataReader object is enough. However, again you may forget to call Close() and it is an all around best practice to ensure that unmanaged resources are efficiently released by calling Dispose() on all objects that implement IDisposable. Even MySqlCommand objects need to be wrapped in a using statement.

So in closing (pun not intended), please do NOT write code like this:

// DO NOT WRITE YOUR MySQL Connector CODE LIKE THIS!!
// I DO NOT IN ANY WAY, SHAPE OR FORM ENDORSE THIS CODE!
// I ONLY USE IT AS AN EXAMPLE OF WHAT NOT TO DO!
MySqlConnection conn = new MySqlConnection(ConnectionString);
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;

cmd.CommandText = "SELECT * FROM SomeTable";
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
     // DO SOME WORK
}
reader.Close();
conn.Close();

Try to aim for code that properly utilizes using statements like this:

// AH! MUCH BETTER! NICE, CLEAN, EFFICIENT, HIGH FUNCTIONING CODE!
// USE THIS APPROACH - I WHOLEHEARTEDLY ENDORSE THIS CODE! :-)
using (var conn = new MySqlConnection(ConnectionString))
{
     conn.Open();
     using (MySqlCommand cmd = conn.CreateCommand())
     {
          cmd.CommandText = "SELECT * FROM SomeTable";
          using (MySqlDataReader reader = cmd.ExecuteReader())
          {
               while (reader.Read())
               {
                   // DO SOME WORK
               }
          }
     }
}