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.