Database Connectivity in .NET

Something to keep in mind about database connectivity in .NET is to always ensure your connection is being disposed of properly.

Many times as developers we code according to the "happy path" which can lead to some undesirable behaviors when an unhappy path occurs.

Here is a quick example (excuse any typos as I'm doing this from memory):

SqlConnection cnn = new SqlConnection(myConnectionString);
cnn.Open();
SqlDataReader reader = cnn.ExecuteReader(someQueryOrCommand);
cnn.Close();

We open a connection, get some data, and we close the connection.  Easy, right?  Probably works 99% of the time with no problems.

What happens if we timeout during the execution of our SQL query or command?  Most of us are/should be using a try...catch block to catch that exception and deal with that error instead of having the app crash due to an un-handled exception.

A potentially big problem exists regardless of the try...catch block.  Each time this code is executed and fails for whatever reason we are leaving an orphaned connection.  If this happens enough times, we run out of available connections on the server and SQL is not responsive.

Commonly I see this problem when the database is performing slowly and causing a lot of database calls to timeout.  A user may repeatedly retry a piece of functionality or may repeatedly close and re-open the application trying to get it to run.  These orphan connections typically make the problem worse to the point that the server has to be rebooted.

A good way to handle/prevent trashing your available connection is by using a "finally" block in addition to the try...catch.

The finally block will execute regardless if the outcome of the code (IE, regardless if it goes through the try code with no problems or it hits the exception code block).

Pseudocode for this solution looks like this:

SqlConnection cnn = null;

try
{

cnn = new SqlConnection(myConnectionString);
cnn.Open();
SqlDataReader reader = cnn.ExecuteReader(someQueryOrCommand);

}
catch (Exception ex)
{
// do something here
}
finally
{
if (cnn != null && cnn.State == ConnectionState.Open)
    cnn.Close();
}