CODEWITHSUNDEEP
c#asp.net.net
AJM
AJM

Reputation: 32500

SqlConnection with a using clause - calling close on the connection

When I have a SQLConnection within a using clause as illustrated below do I need to explicitly close the connection?

protected SqlConnection Connection 
{
    get
    {
       if (this.connection == null)
       {
           this.connection = new SqlConnection(this.ConnectionString);
       }
       if (this.connection.State != ConnectionState.Open)
       {
           this.connection.Open();
       }

       return this.connection;
    }
} 

using (SqlConnection connection = this.Connection)
{
    using (SqlCommand cmd = connection.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "....";

        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                etc
            }
        }
    }
}

Upvotes: 3

Views: 1316

Answers (3)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131774

No you don't. The Dispose() method of a connection will call Close if the connection is already open.

You should also change your code as John Gathogo suggests to create a new connection object each time it needs one. Your code will fail as it is because the second time you try to use the connection it will already be disposed.

ADO.NET uses connection pooling to keep a pool of open connections which it provides to whoever calls Open. This means that creating and opening new connection doesn't cost anything as long as there are available connections in the pool. Keeping a connection open for longer than necessary will degrade performance.

Upvotes: 3

Guffa
Guffa

Reputation: 700910

The using block will always call Dispose, which will close the connection.

But, you are keeping the connection object and intend to reuse it, which is not possible once it has been disposed. You shouldn't keep the connection object, you should just throw it away and create a new one when needed. The actual connections to the database are pooled, so when you create a new connection object it will reuse one of the connections from the pool. When you dispose the connection object, the actual connection is returned to the pool.

Upvotes: 3

John Gathogo
John Gathogo

Reputation: 4665

You could easily change you code to below and achieve what you want:

   using (SqlConnection connection = new SqlConnection(this.ConnectionString))
   {
      connection.Open();
      using (SqlCommand cmd = connection.CreateCommand())
      {
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.CommandText = "....";

         using (SqlDataReader reader = cmd.ExecuteReader())
         {
            while (reader.Read())
            {
               //etc
            }
         }
      }
   }

The runtime will take care of closing the connection and disposing the resources for you

Upvotes: 1

Related Questions