Closing the Database Connection using CommandBehavior.CloseConnection

Introduction

Here I would like to introduce the method of closing the database connection using the Commandbehaviour.CloseConnection.

Code Example

If we need to fetch some data from the database then we usually use the sample code written below.

SqlConnection myConnection = new SqlConnection(myConnectionString);

SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);

myConnection.Open();

SqlDataReader myReader;

myReader = myCommand.ExecuteReader();

try

{

   while (myReader.Read())

   {

      Console.WriteLine(myReader.GetString(0));

   }

}

finally

{

   myReader.Close();

   myConnection.Close();

}  

But in the above written code, we have to close the connection explicitly each and every time we open the connection.

To overcome this issue we have another better method to close the connection - mentioned below.

SqlConnection myConnection = new SqlConnection(myConnectionString);

SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);

myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

while (myReader.Read())

{

   Console.WriteLine(myReader.GetString(0));

}

myReader.Close();

//Implicitly closes the connection because CommandBehavior.CloseConnection was specified.

Explanation of the above code

In the above written code we are using the CommandBehavior.CloseConnection, which is a parameter of the ExecuteReader method.

If we are using the CommandBehavior.CloseConnection, then we do not need to close the database connection explicitly because once we close the datareader object then it automatically closes the database connection as well.

MSDN link

http://msdn.microsoft.com/en-us/library/aa326246(v=vs.71).aspx