Why not close the database connection in the finally block

Question

Major change: I read the article incorrectly! The comment was regarding the finalize method of the class, not the finally :) block. Apologies

I just read that you should not close or delete the database connection in the finally block, but the article did not explain why. It seems that I cannot find a clear explanation why you would not want to do this.

Here is an article

Answer 1

If you look around, closing the connection in the finally block is one of the recommended ways to do it. The article you were looking at might recommend using the "using" statement for code that uses a 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" operator ensures that the Connection object will be deleted immediately after its use, and does not wait for the garbage collector to get rid of it.

Answer 2

I must disagree that you should not close or delete the database connection in the finally block.

Giving an unhandled (or even processed in this case) exception to leave open connections can very quickly reduce the database if it has a lot of activity.

Closing a database connection is an example of a disactome why to use the finally statement, IMHO. Of course, using the operator is my preferred method, which may have been for the original author.

Edit main file: Now it makes sense. You will not want to leave the database connection until the garbage collector.

Answer 3

Without an original article, I cannot speak for the author. However, depending on how you implemented the instantiation and opening of the connection with respect to your try / catch / finally block, you may need additional checking before simply calling close. Ex, make sure the connection is not null and not yet closed.

EDIT: The article says that you should not delete the connection object in the Finalize method, and not close it in the finally block. In fact, the paragraph above says that you should always close your connection after using it, so it returns to the connection pool.

"WARNING It is recommended that you always close the connection when you finish using it so that the connection is returned to the pool. This can be done using the Close or Dispose methods of the Connection object. Connections that do not explicitly close cannot be added or returned to the pool. For example, a connection that has gone out of scope but has not been explicitly closed will only be returned to the connection pool if the maximum pool size is reached and the connection remains valid.

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

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

Answer 4

A bit about Googling, quite a few pages appear, on the contrary . Using the "final" block seems to be a good way to ensure that the connection is closed correctly, although as others have said, I would be interested to see the original article, which said that this was not a good idea.

Answer 5

From what I see in the article, he advises not to call Dispose or Close in the Finalizer class, but not to do it in the finally block, which is completely different.

Answer 6

The Close method puts the connection object in a state from which it can be reopened. The Dispose method puts it in a state from which it cannot be reopened (closing first, if it is open).

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

Obviously, if you are doing something more complex with a few calls to Open and Close , then disposing will result in a wrench to work.