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

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.