Does a SQL connection close with "using" if the connection comes from a static class?

Question

Am I closing my SQL connection correctly, by placing it inside a "using" block?

This is how I grab a new connection, execute a query, and return the results:

using (SqlConnection objCS = DB.LMSAdminConn())
{
    objCS.Open();
    SqlCommand objCommand = new SqlCommand("SELECT TOP 1 * FROM users WHERE userid = @userid", objCS);
    objCommand.Parameters.Add("@userid", SqlDbType.Int).Value = userid;
    SqlDataReader reader = objCommand.ExecuteReader();
    while (reader.Read())
    {
        //do something
    }
    reader.Close();
}

The connection itself comes from this call:

public static SqlConnection LMSAdminConn()
{
    return new SqlConnection(ConfigurationManager.ConnectionStrings["lmsadmin"].ToString());
}

I am opening the connection inside a "using" block, and I thought that the connection would be closed as well, because it is opened inside the "using" block. But since the "new SqlConnection" object is actually generated from an outside class, is my connection still getting appropriately closed? SQL Server shows the connection as still being open, but I'm not sure if that is ADO.NET connection pool recycling / sharing, or if the connection is truly being held open. I don't explicitly call .Close() on the connection inside the "using" block.

Do I need to explicitly close the SqlCommand and SqlDataReader objects as well, or are they disposed when we leave the "using" block as well?

Answer 1

A using block is essentially syntactic sugar for having a try/finally block that calls the Dispose method of the object it is acting on, it doesn't matter where that object was created.

For a SqlConnection object, calling Dispose will close the connection. From the docs:

If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. Close and Dispose are functionally equivalent.

Answer 2

Yes, it will close the connection once it loses scope. Sample:

using (SqlConnection sqlConn = new SqlConnection("myConnectionString"))
{
  sqlConn.Open();
  ...
}

This code will be converted into the following by the compiler :

try
{
    SqlConnection sqlConn = new SqlConnection("myConnectionString");
    sqlConn.Open();
    ...
}
finally
{
   sqlConn.Close();
}

As you can see the close() is called in the finally block. This finally block will force close the connection even if there is an exception during run-time within the using block.

Answer 3

You had an idea about connection pools - this is the right idea!

ADO.NET it is so beautiful that it tries to optimize everything and store it in connection pools. But we still need to always close connections!