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();
}

Missing .NET Functionality

Something I've noticed recently in .NET4 is that certain functionality doesn't seem to be available.


Consider the following line:

string value = ConfigurationSettings.AppSettings["somevalue"];

I get a warning in the compiler "Warning 'System.Configuration.ConfigurationSettings.AppSettings' is obsolete: "This method is obsolete, it has 

been replaced by 

System.Configuration!System.Configuration.ConfigurationManager.AppSettings"

However, when I type ConfigurationManager, nothing shows up in Intellisense!

The solution to this is to go to "Add reference" and add the reference to "System.Configuration" in your solution.  Now, that functionality is available.

This is also the case for the "HttpWebRequest" object location in System.Web.

I'm not sure why 4.0 requires this whereas it just worked in older versions of .NET but it is what it is.