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);
   myConnection.Open();
   SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

   return myReader;    
}

public void GetEmployees()
{
 SqlDataReader dr = CreateMySqlDataReader();
 while(dr.Read())
   {
      //Do your operations
   }
   //Closing Datareader will implicitly close the connection
   dr.Close();    

}

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

Happy Coding!!