While viewing some of the C# code written by a new programmer, I noticed that they lack of calling Dispose() method on SqlConnection and SqlCommand objects. And the dabase code was not placed in try/finally blocks. This coding style is typical newbie style of development that everyone, including myself, attempted in the very beginning.

SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(commandString, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

The problem in the above code is that SqlConnection and SqlCommand implement IDisposable, which means they could have unmanaged resources to cleanup and it is our job, 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, we need to make sure Dispose() gets called even in the case of an exception.

Its better to use the “using” keyword in C#. Internally, this 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 con = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand(commandString, con))
   {
      con.Open();
      cmd.ExecuteNonQuery();
   }
}

This is essentially equivalent to the following:

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

You may notice the lack of calling Close() on the SqlConnection class, con. 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 (con) as Dispose() will do it for you. In addition, Dispose() destroys the connection string of the SqlConnection class. Therefore, if you want to re-open the connection after calling Dispose() on con, you will have to re-establish the connection string. Not doing so will throw an exception.