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

By | February 17, 2015

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:

I recommended that you do this instead:

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:

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

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

  1. nanang

    How about this :

    MySqlConnection conn=null;
    try
    {
    conn = new MySqlConnection(ConnectionString);
    conn.Open();
    }
    catch() {}
    finaly
    {
    conn.close();
    conn.dispose()
    }

    Reply
    1. derek Post author

      That is pretty close to what you get from using using for free. My only complaint with that approach is that any and all exceptions are swallowed up and essentially hidden (maybe you just left out error handling for brevity – but I thought it worth mentioning).

      Either way this is what using equates to:

      {
      MySqlConnection conn = new MySqlConnection(ConnectionString);
      try
      {
      conn.Open();
      // Do other stuff
      }
      finally
      {
      if (conn != null)
      ((IDisposable)conn).Dispose();
      }
      }

      Reply
      1. Tim

        Not to mention, there is no check for conn == null in the finally block… and any exceptions thrown in the finally block (when calling conn.Close() / conn.Dispose()) will be thrown to the caller instead of the original exception. A better looking finally block would be –

        finally
        {
        try
        {
        if (conn != null)
        {
        conn.Dispose();
        }
        }
        catch { /* hide any exceptions thrown when disposing of the connection */ }
        throw; //throw original exception to caller
        }

        Reply
        1. Tim

          BTW – a common criticism of using statements… if an exception is thrown within the using block, and then a 2nd exception is thrown from within the using object’s Dispose() method, the 2nd exception will mask the original exception. A solution, if throwing the original exception to the caller is important, is to code a finally block as I’ve shown above…

          Reply
  2. Jon

    One thing that is worth mentioning is that the C# IDisposable MySQL classes do implement a destructor that calls Dispose(). So when the garbage collector decides the object can be destroyed it will be properly disposed, and if you forget to use ‘using’ it is not necessarily disastrous. But certainly you may need to be more proactive with MySqlConnection because the database connection and server thread should be terminated as soon you have finished with the connection. Below is the destructor for MySqlConnection:

    ~MySqlConnection()
    {
    #if !RT
    Dispose(false);
    #else
    Dispose();
    #endif
    }

    Reply
  3. KT

    Thank you for this! I have a question. If I want to run two separate queries, is it better to create two MySqlCommand objects and run them both within the connection block? Or can I change the command text and run it after the first reader closes (as demo’d below)? Thanks!

    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
    }
    }
    // SECOND COMMAND
    cmd.CommandText = “SELECT * FROM SomeTable”;
    using (MySqlDataReader reader = cmd.ExecuteReader())
    {
    while (reader.Read())
    {
    // DO SOME WORK
    }
    }
    }
    }

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *