Disposing the Sql Connection

Question

Just wondering, Would the SqlConnection be diposed/closed when this method is done? Or do i have to explicitly call the close method at the end?

   using (SqlCommand cmd = new SqlCommand(sql, GetConnection()))
   {
       SqlDataReader reader = cmd.ExecuteReader();
       while (reader.Read())
       {
       }
   }

SqlConnection GetConnetion()
{
 return new SqlConnection("connectionstring");
}

I know i can do something like this:

SqlConnection conn = GetConnetion();
SqlCommand cmd =new SqlCommand(sql, conn);
//Do Something
conn.Close()
cmd.Dispose()

But just curious how the using block will work in this case. Cheers

Answer 1

The using statement will take care of this for you.

Answer 2

No, the connection object won't be automatically disposed in your example. The using block only applies to the SqlCommand object, not the connection.

To ensure that the connection is disposed, make sure that the SqlConnection object is wrapped in its own using block:

using (SqlConnection conn = GetConnection())
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
    // don't forget to actually open the connection before using it
    conn.Open();
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            // do something
        }
    }
}

Answer 3

Oops. You want to use the using on your connection, not on your command.

Answer 4

Use using but on the connection, not on the SqlCommand. The Dispose method on the connection will close the connection (return it to the pool, if pooling is enabled). Also place an using around the SqlDataReader too:

using(SqlConnection conn = GetConnection())
{
  SqlCommand cmd = new SqlCommand(sql, conn);
  using (SqlDataReader reader = cmd.ExecuteReader())
  {
    do 
    {
      while (reader.Read())
      {
      }
    } while (reader.NextResult());
  } 
}

Answer 5

Here and Here is something which could help you understanding what is going on.

Answer 6

Luke's answer is the correct one in terms of what you specifically asked regarding the disposal of the connection.

For completeness, what you could also do is to use the SqlCommand.ExecuteReader(CommandBehaviour) method instead of the parameterless one, passing in CommandBehvaiour.CloseConnection:

using (SqlCommand cmd = new SqlCommand(sql, GetConnection()))
{
    using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
    {
        while (reader.Read())
        {}
    }
}

This signifies that when the SqlDataReader is closed (when it is disposed of in the using construct), it will in turn close the connection that it is using.

I'm not keen on this approach though, as there is some implied logic and it is not obvious what exactly is closing the connection.