Do I need to close SQL Server connection with the using keyword?

Question

I keep finding conflicting results for this question. Let's look at this C# code of running an SQL query:

using (SqlConnection cn = new SqlConnection(strConnectString))
{
    cn.Open();

    using (SqlCommand cmd = new SqlCommand(strSQL, cn))
    {
        cmd.ExecuteNonQuery();
    }

    //Do I need to call?
    cn.Close();
}

Do I need to call that last cn.Close()? The reason I'm asking is that in a heavy traffic web app I run out of connections in a pool.

Answer 1

The using keyword as used here:

using (SqlConnection cn = new SqlConnection(strConnectString))
{
    // Stuff
}

is short for:

SqlConnection cn = null;
try
{
    cn = new SqlConnection(strConnectString);
    // Stuff
}
finally
{
    if (cn != null) cn.Dispose();
}

cn.Dispose() will be called immediately after cn goes out of scope of the using, which in turn immediately closes the connection (because SqlConnection.Dispose() does just that).

UPDATE

This should not be confused with garbage collection. GC is non-deterministic in .NET, which is exactly why the IDisposable inteface and Dispose Pattern were introduced. IDisposable allows expensive resources to be released in a timely, deterministic manner.