What is the proper way to ensure a SQL connection is closed when an exception is thrown?

Question

I use a pattern that looks something like this often. I'm wondering if this is alright or if there is a best practice that I am not applying here.

Specifically I'm wondering; in the case that an exception is thrown is the code that I have in the finally block enough to ensure that the connection is closed appropriately?

public class SomeDataClass : IDisposable
{
    private SqlConnection _conn;

    //constructors and methods

    private DoSomethingWithTheSqlConnection()
    {
        //some code excluded for brevity

        try
        {
            using (SqlCommand cmd = new SqlCommand(SqlQuery.CountSomething, _SqlConnection))
            {
                _SqlConnection.Open();
                countOfSomething = Convert.ToInt32(cmd.ExecuteScalar());
            }
        }
        finally
        {
            //is this the best way?
            if (_SqlConnection.State == ConnectionState.Closed)
                _SqlConnection.Close();
        }

        //some code excluded for brevity
    }

    public Dispose()
    {
        _conn.Dispose();
    }
}

Solution 1

Wrap your database handling code inside a "using"

using (SqlConnection conn = new SqlConnection (...))
{
    // Whatever happens in here, the connection is 
    // disposed of (closed) at the end.
}

Solution 2

The .Net Framework mantains a connection pool for a reason. Trust it! :) You don't have to write so much code just to connect to the database and release the connection.

You can just use the 'using' statement and rest assured that 'IDBConnection.Release()' will close the connection for you.

Highly elaborate 'solutions' tend to result in buggy code. Simple is better.

Solution 3

MSDN Docs make this pretty clear...

• The Close method rolls back any pending transactions. It then releases the connection to the connection pool, or closes the connection if connection pooling is disabled.

You probably haven't (and don't want to) disable connection pooling, so the pool ultimately manages the state of the connection after you call "Close". This could be important as you may be confused looking from the database server side at all the open connections.

---

• An application can call Close more than one time. No exception is generated.

So why bother testing for Closed? Just call Close().

---

• Close and Dispose are functionally equivalent.

This is why a using block results in a closed connection. using calls Dispose for you.

---

• Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class.

Important safety tip. Thanks, Egon.

Solution 4

I'm guessing that by _SqlConnection.State == ConnectionState.Closed you meant !=

This will certainly work. I think it is more customary to contain the connection object itself inside a using statement, but what you have is good if you want to reuse the same connection object for some reason.

One thing that you should definitely change, though, is the Dispose() method. You should not reference the connection object in dispose, because it may have already been finalized at that point. You should follow the recommended Dispose pattern instead.

Solution 5

Put the connection close code inside a "Finally" block like you show. Finally blocks are executed before the exception is thrown. Using a "using" block works just as well, but I find the explicit "Finally" method more clear.

Using statements are old hat to many developers, but younger developers might not know that off hand.

View more solutions