Scott Hanselman

Why the using statement is better than a sharp stick in the eye, and a SqlConnection refactoring example

July 2, '04 Comments [3] Posted in Programming
Sponsored By

A friend of mine sent me some code just now where he was experimenting with Close and Dispose on SqlConnection.  Reflectoring into SqlConnection shows it closes open connections in Dispose().  So, here's the before and after code.  I think it shows good examples on why the using statement exists, and when to avoid (hide) superfluous try/catches.  I also changed a few nits for readability by using certain overloaded constructors as well as String.Format().

BEFORE - The code I was given:

private void RunScriptOnDB(string filename,string DB)
{
    SqlConnection sqlcon = new SqlConnection();
    sqlcon.ConnectionString ="Persist Security Info=False;Integrated Security=SSPI;Initial Catalog="+DB+";Data Source=(local);";
    SqlCommand com = new SqlCommand();
    com.Connection = sqlcon;
    try
    {
        StreamReader sr = Utility.GetStreamOfFile(filename);
        com.CommandText = sr.ReadToEnd();
        sr.Close();
    }
    catch(FileNotFoundException fileex)
    {
        msg.Text = fileex.Message;
        return;
    }
    try
    {
        sqlcon.Open();
        com.ExecuteNonQuery();
        msg.Text = "Successful";
    }
    catch( SqlException sqlex)
    {
        msg.Text = sqlex.Message;
    }
    finally
    {

        if(closingMethod.SelectedValue == "c") //SDH: He's trying different closing methods based on a Radio Button, this won't be needed in a refactor
        {
            sqlcon.Close();
        }
        else if(closingMethod.SelectedValue == "d")
        {
            sqlcon.Dispose();
        }
        else
        {
            sqlcon.Close();
            sqlcon.Dispose();
        }
    }
}

AFTER - My quickie refactor/clean:

private void RunScriptOnDB(string filename, string database)
{
   string commandText = String.Empty;
   try
   {
      using (StreamReader sr = Utility.GetStreamOfFile(filename))
      {
         commandText = sr.ReadToEnd();
      }
   }
   catch (FileNotFoundException fileEx)
   {
      msg.Text = fileEx.Message;
      return;
   }
   using (SqlConnection connection = new SqlConnection(String.Format("Persist Security Info=False;Integrated Security=SSPI;Initial Catalog={0};Data Source=(local);",database))
   {
      using (SqlCommand command = new SqlCommand(commandText, connection))
      {
         try
         {
            connection.Open();
            command.ExecuteNonQuery();
            msg.Text = "Successful";
         }
         catch (SqlException sqlEx)
         {
            msg.Text = sqlEx.Message;
         }
      }
   }
}

About Scott

Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.

facebook twitter subscribe
About   Newsletter
Sponsored By
Hosting By
Dedicated Windows Server Hosting by SherWeb

Disclaimer: The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.