Use the "using" C# key word to build sql connection

While reviewing some C# code written by a newbie programmer and buddy of mine, I noticed a lack of calling Dispose() on SqlConnection and SqlCommand objects. And in all cases, the database code was not placed in try / finally blocks. This is typical newbie style of development that everyone, including myself, attempted in the very beginning.

SqlConnection cn = new SqlConnection(connectionString);
SqlCommand cm = new SqlCommand(commandString, cn);

cn.Open();
cm.ExecuteNonQuery();
cn.Close();


It sure is easy to follow :)

The problem is that SqlConnection and SqlCommand implement IDisposable, which means they could have unmanaged resources to cleanup and it is our job, the developers, to make sure Dispose() gets called on these classes after we are finished with them. And, because an exception could be raised if the database is unavailable ( a very real possibility on WebHost4Life :) ), we need to make sure Dispose() gets called even in the case of an exception.

Personally, I like the “using” keyword in C#. Internally, this bad boy generates a try / finally around the object being allocated and calls Dispose() for you. It saves you the hassle of manually creating the try / finally block and calling Dispose().

The new code would looking something like this:

using (SqlConnection cn = new SqlConnection(connectionString))
{
using (SqlCommand cm = new SqlCommand(commandString, cn))
{
cn.Open();
cm.ExecuteNonQuery();
}
}

This is essentially equivalent to the following, although my guess is that C# will internally generate two try / finally blocks (one for the SqlConnection and one for the SqlCommand), but you get the idea:

SqlConnection cn = null;
SqlCommand cm = null;

try
{
cn = new SqlConnection(connectionString);
cm = new SqlCommand(commandString, cn);
cn.Open();
cm.ExecuteNonQuery();
}
finally
{
if (null != cm);
cm.Dispose();
if (null != cn)
cn.Dispose();
}

You may notice the lack of calling Close() on the SqlConnection class, cn. Internally, Dispose() checks the status of the connection and closes it for you. Therefore, technically you don't need to call Close() on the connection (cn) as Dispose() will do it for you. However, I don't think there is any penalty for calling Close() directly and then Dispose(), but I don't know for sure. I doubt it.

In addition, Dispose() destroys the connection string of the SqlConnection class. Therefore, if you want to re-open the connection after calling Dispose() on cn, you will have to re-establish the connection string. Not doing so will throw an exception.

posted on Thursday, January 13, 2005 3:07 PM

from: http://davidhayden.com/blog/dave/archive/2005/01/13/773.aspx