Home » CodeDigest


Implicitly Closing the underlying SqlConnection when using SqlDataReader Object
Submitted By Satheesh Babu B
On 4/19/2010 9:00:47 AM
Tags: ADO.Net,CodeDigest  

Implicitly Closing the underlying SqlConnection when using SqlDataReader Object


At times, we may need to return the SqlDataReader object to a different method from the original data access method. It is obvious that it is required to close the connection object whenever we complete our data access. Since, the caller method will have only the datareader object returned from the data access method we can't explicitly close the underlying connection object.

The ExecuteReader() method of SqlDataReader object will accept a enum parameter called CommandBehavior which can be used to handle this scenario. When the ExecuteReader () method is called with CommandBehavior.CloseConnection as parameter, the underlying connection object will be automatically closed once the datareader is closed. Refer the below code,


public SqlDataReader CreateMySqlDataReader()
   SqlConnection myConnection = new SqlConnection(myConnectionString);
   SqlCommand myCommand = new SqlCommand("Select * from Employee", myConnection);
   SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

   return myReader;    

public void GetEmployees()
 SqlDataReader dr = CreateMySqlDataReader();
      //Do your operations
   //Closing Datareader will implicitly close the connection



In the above code, when dr.Close() is called in GetEmployees() method it will also closes the underlying connection object.

Happy Coding!!


Do you have a working code that can be used by anyone? Submit it here. It may help someone in the community!!

Recent Codes
  • View All Codes..