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 ORCS Web
Friday, July 02, 2004 11:19:23 AM UTC
I remember reading loads of lists/articles etc where people were confused over the using statement over an SqlConnection. Some said it removed the connection from the pool, others said not. Reflecting over the SqlConnection base type System.Data.DbConnectionBase in .NET v2.0 I see it removes both the user options and the pool group. Does this mean indeed that using dispose DOES remove the connection from the pool? If this is the case then surely we should just be closing the SqlConnection. Can you clarify this??

protected override void Dispose(bool disposing)
{
if (disposing)
{
this._userConnectionOptions = null;
this._poolGroup = null;
this.Close();
}
base.Dispose(disposing);
}

Marshall brooke
Friday, July 02, 2004 2:02:28 PM UTC
Marshall - I believe the connection pooling happens at a lower level (unmanaged ADO code). Setting the poolgroup to null would just affect data in the managed wrapper class (DbConnectionBase). Just because the managed world has no more reference to the database connection, doesn't mean the underlying OS doesn't.
That is my understanding, it is by no means an authoritive answer.

I'm wondering what's up with that Utility.GetStreamOfFile()? There is a built in System.IO.File.OpenText(filename) which I imagine does the same thing.
Friday, July 02, 2004 4:45:16 PM UTC
Ya, I know...he's pulling the file out of the Manifest Resource of his own assembly. It's confusing. I'd have probably done that different as well.
Scott Hanselman
Comments are closed.

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