Checking a MySQL connection and refreshing it

Question

I am using the following function to check if a MySQL connection is in an invalid state or if it has reached a predefined maximum operation count:

 /// <summary>
 /// Holds the amount of operations for the currently mysql-connection open
 /// </summary>
 private int connectionUsageCount = 0;
 /// <summary>
 /// The maximum usage count of the connection until it has to be refreshed
 /// </summary>
 private const int MAX_CONNECTION_USAGE_COUNT = 100;
 /// <summary>
 /// Holds the cached MySql Connection to the databse server.
 /// </summary>
 private MySqlConnection mySqlConnection;
 /// <summary>
 /// Checks the status of the sql connection and refreshes it when necessary.
 /// </summary>
 private void CheckSqlConnection()
 {
 // Check if the connection is in an invalid state or
 // the connection has to be refreshed and refresh it
 // when necessary
 if (mySqlConnection == null
 || mySqlConnection.State == System.Data.ConnectionState.Broken
 || mySqlConnection.State == System.Data.ConnectionState.Closed
 || connectionUsageCount >= MAX_CONNECTION_USAGE_COUNT)
 {
 // If the connection was already initialized, close and dispose it
 if (mySqlConnection != null)
 {
 // If connection is open, close it
 if (mySqlConnection.State != System.Data.ConnectionState.Broken
 && mySqlConnection.State != System.Data.ConnectionState.Closed)
 {
 mySqlConnection.Close();
 }
 mySqlConnection.Dispose();
 mySqlConnection = null;
 }
 // Create new connection 
 mySqlConnection = new MySqlConnection(this.ConnectionString.ToString());
 }
 }

Can anyone review this piece of code and tell provide me with some feedback? I am not sure if I have covered all possibilities that would cause an error when using the mySqlConnection.

Answer 1

1. As per this SO question you need to be aware that calling Close will not necessarily actually close the connection but only put it back into the connection pool.

2. There is no reason to do mySqlConnection = null;. You assign a new value to mySqlConnection in the next statement so setting it to null achieves nothing.

3. These docs might be somewhat out-dated but they state that you can call Close multiple times and that no exception is generated so you can probably skip the check and just call Close unconditionally.

• user613326: how about a simple re-binder if (MySQLcon.State != System.Data.ConnectionState.Open) MySQLcon.Open()