Why Not Close a Database Connection in a Finally Block

Question

Major Edit: I misread the article! The comment was in regards to the finalize method of the the class not the finally block :). Apologies.

I was just reading that you should not close or dispose a database connection within a finally block but the article did not explain why. I can not seem to find a clear explanation as to why you would not want to do this.

Here is the article

Answer 1

If you look around, closing the connection in the finally block is one of the recommended ways of doing it. The article you were looking at probably recommended having a 'using' statement around the code that used the connection.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = connection.CreateCommand();

    command.CommandText = "select * from someTable";

    // Execute the query here...put it in a datatable/dataset
}

The 'using' statement will ensure the Connection object gets disposed immediately after it's needed rather than waiting for the Garbage Collector to dispose of it.

Answer 2

I have to disagree that you should not close or dispose of a database connection within a finally block.

Letting an unhandled (or even handled for that matter) exception leave open connections can take down a database pretty quickly if it has a lot of activity.

Closing a database connection is the defacto example of why to use the finally statement, IMHO. Of course, the using statement is my preferred method, which is maybe what the original author was going for.

Edit to the Major Edit: That makes sense now. You wouldn't want to leave closing your database connection up to the garbage collector.

Answer 3

Without the original article, I can't speak for the author. However, depending on how you've implemented instantiating and opening the connection in relation to your try/catch/finally block, you might need to do some additional checking before just calling close. Ex, ensure the connection is not null and not already closed.

EDIT: The article says not to dispose of a connection object in your Finalize method, not to not close it in the finally block. In fact, in the paragraph above it says you should always be closing your connection after you use it, so it is returned to the connection pool.

"CAUTION It is recommended that you always close the Connection when you are finished using it in order for the connection to be returned to the pool. This can be done using either the Close or Dispose methods of the Connection object. Connections that are not explicitly closed might not be added or returned to the pool. For example, a connection that has gone out of scope but that has not been explicitly closed will only be returned to the connection pool if the maximum pool size has been reached and the connection is still valid.

Note Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. In a finalizer, only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. For more information, "

http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.71).aspx?ppud=4

Answer 4

A little bit of Googling turns up quite a few pages that hold the opposite opinion. Using a "finally" block seems like a good way to ensure that the connection is always closed correctly, although as others have said I'd be interested to see the original article that said it wasn't a good idea.

Answer 5

From what I can see in the article it advices against calling Dispose or Close in the Finalizer of a class, not against doing so in a finally block, which is quite a different thing.

Answer 6

The Close method puts the connection object into a state from which it can be re-opened. The Dispose method puts it into a state from which it cannot be re-opened (closing it first if currently open).

If you instantiate a connection, open it, use it, then throw it away (the normal usage pattern), then a using block is the best and simplest way to do it.

Obviously, if you are doing something more complex with multiple Open and Close calls, then disposing it will throw a spanner in the works.