What are the consequences of not closing database connection after an error?

Question

I have an application that is causing a lot of headaches. It's a .NET app connecting to SQL Server 2005 via a web service. The program has grid that is filled by a long running stored procedure that is prone to timing out. In the case when it does time out and a SqlException is thrown, there is no execption handling to close the connection.

What are the actual consequences of this condition? I think that the framework or SQL Server probably takes care of it one way or another but am not sure.

Addition The program always works well in the morning, but after an hour or so of use it basically stops working. The issue isn't that I don't know how to code the connection properly. I need to know if these symptoms could be casued by the unclosed connections. It is kind of a big deal to change the production code and I would like to know that it is at least possible for this to be the issue.

Conclusion I engineered this failure to occur on hundreds of simultaneous connections. Never was I able reproduce the failure condition in the application environment. Marked best practices answer as correct. Thanks everyone.

Answer 1

There is a connection limit; if your app crashes frequently and does not close the connections automatically, new connection requests will be denied.

That said, connections do time out after a while if they're not closed.

Answer 2

You could run out of connections available if it happens often enought, you should use a finally everywhere you execute a command to close the connection.

Answer 3

The garbage collector will eventually finalize your open connection object, but you don't know when the GC comes around the next time. Until then you might run out of connections in your pool if you have a lot of traffic or it's a shared sql server.

Why not dispose it in the finally section of your try/catch block?

finally
{
   if (cn != null)
   {
      cn.Dispose();
      cn = null;
   }
}

This should be done in the web service method obviously.

Answer 4

try
{
    sqlCommandObject.Execute(); // this line will throw a timeout exception
}
finally
{
    sqlConnectionObject.Close(); // this will execute no matter what happens
}

Answer 5

If the app stops working after an hour or so, that could definitely be caused by connections not being closed/disposed.

Answer 6

Since a SqlConnection closes while disposing i usually use this syntax

using (SqlConnection conn = new SqlConnection())
{
  // SqlCode here 
}

Answer 7

This is why the 'using' keyword is so important when using ADO.Net

 using ( SqlConnection conn = new SqlConnection() )
 {
     ...
 }

This forces a type of deterministic garbage collection on the ADO.Net object using the IDispose interface.

Most database code uses a lot of nested 'using' clauses for that purpose.