This post originated from an RSS feed registered with .NET Buzz by Scott Hanselman. Original Post: Why the using statement is better than a sharp stick in the eye, and a SqlConnection refactoring... Feed Title: Scott Hanselman's ComputerZen.com Feed URL: http://radio-weblogs.com/0106747/rss.xml Feed Description: Scott Hanselman's ComputerZen.com is a .NET/WebServices/XML Weblog. I offer details of obscurities (internals of ASP.NET, WebServices, XML, etc) and best practices from real world scenarios. Latest .NET Buzz Posts Latest .NET Buzz Posts by Scott Hanselman Latest Posts From Scott Hanselman's ComputerZen.com

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