What happens to an unclosed database connection?

Question

Earlier today I've found a bug in one of our projects - there is a conenction with database that is never closed, i mean the Close() method is never called. However, when I close the application, the connection is closed (checked in sql management studio many times). Why?

Answer 1

The connection will close when the application exits. Read up on SqlConnection's Finalize. From MSDN's documentation for Object.Finalize:

"During shutdown of an application domain, Finalize is automatically called on objects that are not exempt from finalization, even those that are still accessible."

Answer 2

SQL connections are expensive to create and ADO.NET uses a technique called connection pooling which allows to reuse them.

Quote from MSDN:

It is strongly recommended that you always close the connection when you are finished using it so that it will be returned to the connection pool and be reused.

If the maximum pool size has been reached and no usable connection is available, the request is queued. The pooler then tries to reclaim any connections until the time-out is reached (the default is 15 seconds). If the pooler cannot satisfy the request before the connection times out, an exception is thrown.

Answer 3

When you exit the application in a regular fashion, I expect the finalizers to run. They will close the connection if it is still open. Just don't depend on this: it might take a while before those finalizers are run in normal operation of your application, so you will be keeping too many connections open.

When the application crashes, maybe the finalizer will not run, leaving the connection open past the lifetime of the application.

Answer 4

Another thing to keep in mind here is that in .Net, you can wrap your connections in a using block, and that will close and dispose your connections for you. So the lack of an explicit Close() isn't a bad thing if you've got your using blocks there...

// this using block will auto close & dispose your connection...
using (var conn = new SqlConnection(...))
{
    conn.Open();
    // database code here with no explicit close

}

that is the functional equivalent of a try/finally block with a conn.close in the finally. Many devs overlook the using blocks - make sure you're not doing the same in this case.

If you do rewrite your code to close your connections - it's good practice to use Using blocks around all of your database objects (connection, command, reader) to make sure that they are closing and disposing when they fall out of scope of the using block. I'd definitely suggest writing those into your code instead of just conn.Close() where needed.