Can I rely on the using statement to close my MySQL connections?

Question

I have an ASP.NET website that uses mysql as it's database. I notice there are a lot of connections "sleeping" when I show a full process list. Today we had some errors "The timeout period elapsed prior to obtaining a connection from the pool.". If the processes are "sleeping" then are they still open from the code? All MySQL connections in the code are in using statements. Can I rely on the using statement to properly close connections?

Edit Code: I am using this class to create my connection:

public class DbAccess
{
    public static MySqlConnection OpenConnection(string connectionStringName)
    {
        string connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
        if (string.IsNullOrEmpty(connectionString))
        {
            throw new ArgumentException("Connection string " + connectionStringName + " does not exist.");
        }
        MySqlConnection connection = new MySqlConnection(connectionString);
        connection.Open();
        return connection;
    }
}

Then I am calling it like this:

using (MySqlConnection connection = DbAccess.OpenConnection(connectionString))
{
    //Code Here
}

Some additional info: Resetting MySql did not make the errors go away, but resetting my app pool did..

Answer 1

C# using blocks are guaranteed to call the .Dispose() method of the object, even if an exception is thrown. That means it's safe, as long as your provider uses the .Dispose() method to close the connection. Looking in the documentation for that type, I see this excerpt (down in section 25.2.3.3.5):

From Open to Closed, using either the Close method or the Dispose method of the connection object.

This tells me you can close the connection via the Dispose method, and so a using block should be all you need.

Answer 2

hope this could be of any help: MySqlConnection really not close and Using MySQLConnection in C# does not close properly and this one http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/c57c0432-c27b-45ab-81ca-b2df76c911ef

Answer 3

yes, absolutely.

using (MySqlConnection connection = DbAccess.OpenConnection(connectionString))
{
    //Code Here
}

is exactly the same as

MySqlConnection connection = null
try
{
   connection = DbAccess.OpenConnection(connectionString) 
   //Code Here
}
finally
{
    if (connection is IDisposable)
       connection.Dispose
}

provided the MySqlConnection class implements IDisposable, then it'll get cleaned up properly. If you need to call another methos, such as close instead of or as well as, then consider the more verbose syntax above and add the method in the finally.