What is the proper way to ensure a SQL connection is closed when an exception is thrown?
Wrap your database handling code inside a "using"
using (SqlConnection conn = new SqlConnection (...))
{
// Whatever happens in here, the connection is
// disposed of (closed) at the end.
}
The .Net Framework mantains a connection pool for a reason. Trust it! :) You don't have to write so much code just to connect to the database and release the connection.
You can just use the 'using' statement and rest assured that 'IDBConnection.Release()' will close the connection for you.
Highly elaborate 'solutions' tend to result in buggy code. Simple is better.
MSDN Docs make this pretty clear...
- The Close method rolls back any pending transactions. It then releases the connection to the connection pool, or closes the connection if connection pooling is disabled.
You probably haven't (and don't want to) disable connection pooling, so the pool ultimately manages the state of the connection after you call "Close". This could be important as you may be confused looking from the database server side at all the open connections.
- An application can call Close more than one time. No exception is generated.
So why bother testing for Closed? Just call Close().
- Close and Dispose are functionally equivalent.
This is why a using block results in a closed connection. using calls Dispose for you.
- Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class.
Important safety tip. Thanks, Egon.