CODEWITHSUNDEEP
sql-serverado.net
Anton P
Anton P

Reputation:

Disposing the Sql Connection

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

Upvotes: 4

Views: 1950

Answers (6)

LukeH
LukeH

Reputation: 269368

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
        }
    }
}

Upvotes: 17

adrianbanks
adrianbanks

Reputation: 82944

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.

Upvotes: 2

Dimi Takis
Dimi Takis

Reputation: 4949

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

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294267

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());
  } 
}

Upvotes: 0

Paul Sasik
Paul Sasik

Reputation: 81459

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

Upvotes: 0

dove
dove

Reputation: 20674

The using statement will take care of this for you.

Upvotes: 1

Related Questions