To Close or Not to Close?

Whether tis better to close a sql connection in a finally block or let it automatically close with a using statement. That is the question.

I was once asked this question in an interview. Will a Sql Server connection automatically close if it is opened in a C# using statement? At the time, I was used to manually closing connections in the finally section of a try / finally block. This ensured that the connection would always be closed, and returned to the connection pool, even if an exception occurred after opening the connection. Here’s an example of that technique.

static void CloseConnection()

  SqlConnection connection = new SqlConnection(conStr);
  try
  { 
    connection.Open();
  }
  finally
  {
    connection.Close();
  }

  string msg = string.Format ("Connection state after manual close: {0}", connection.State);
    Console.WriteLine(msg);
}

In this case the connection state will show that the connection is closed. But what happens if instead you place the connection object in a C# using statement? This construct ensures that the Dispose method of an object is called inside a finally block, if it implements IDisposable. The question is whether the SqlConnection class calls its Close method from Dispose?

At the time of the interview, since I had never verified the behavior of SqlConnection inside a using construct, I had to admit I did not know the answer. The interviewer then told me that a connection object opened within a using statement was NOT automatically closed. He further went on to say that he always asks this question in interviews so that the person interviewed would at least walk away with this valuable tidbit of knowledge. Here is code demonstrating this technique.

static void UsingConnection()
{
  SqlConnection connection = new SqlConnection(conStr);
  using (connection) 
  {
    connection.Open();
  }

  string msg = string.Format ("Connection state after using block: {0}", connection.State);
    Console.WriteLine(msg);
}

For some after afterward this incident kept gnawing at my brain, like a splinter. It just didn’t seem right that the designers of ADO.Net would exclude this possibility. Well, I finally had the time to check it out, and it turns out the interviewer was just plain wrong! I tried the code in both versions 1.1 and 2.0 of the .Net Framework, and I discovered that the using statement DOES result in a closed connection. Not only that, but I pointed the Reflector tool at both versions of the SqlConnection class and observed that the disassembled code does indeed invoke the Close method from within the Dispose method.

It’s happened a few times that an interviewer was mistaken about the answer to one of the questions they asked. It’s always a good idea to not take it for granted that the interviewer is correct, but independently verify the correctness of their assertions. You have nothing to lose but your ignorance :-).

Click here to download my demo code, which incidentally also shows how to add listeners to the Trace class in System.Diagnostics, to output text both to the Console and a log file.

About Tony Sneed

Married with three children.
This entry was posted in Technical. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s