Why People Don't Use Data Readers

A problem with data readers is their connected nature. Every data reader has one database connection tied to it. With .NET 1.X the limitation is one open data reader per database connection, while .NET 2.0 brings new features which allow more than one active result set per connection, but the connection still exists. This means that passing a data reader to the client means also passing the responsibility of closing the data reader and the connection.

Developers also tend to avoid using data readers because they are read-only, forward-only and therefore a bit unsuitable for complex calculation scenarios which might involve the need to access data more than once. This means that with data readers you would need to keep the connection and reader open for a longer time or reacquire the result set, increasing the time the connection is in use.

Using Delegates to Control DAL Responsibility

Delegates are a way to reference methods based on their signature and return type, allowing an instantiated delegate to reference a given method assuming that the method matches the delegate definition. Delegate instances can be passed as method parameters which means that methods themselves can be passed and again invoked by the class/method which receives the delegate as an argument.

Delegates are one solution to the previously mentioned responsibility problem with data readers. In most common data binding scenarios an IDataReader instance is pulled from the DAL, assuming data readers and the data binding are in use, resource releasing is left up to the client. With delegates we can turn this so that a data binding method taking an IDataReader instance as a parameter, is itself given as a parameter to the DAL. With this idea the responsibility of cleanup is up to the DAL, because DAL invokes the delegate instance, waits for it to finish execution and then closes the data reader and the database connection.

DAL

public class DataComponent
{
 //Delegate to declare accepted databinding callback method
 public delegate void IDataReaderHandler(IDataReader reader);


 //Get the data reader callback using given delegate
 public static void GetAuthor(string authorID,IDataReaderHandler handler)
 {
  //Connection scope -  using pubs database
  using(SqlConnection conn=new SqlConnection("server=.;Trusted_Connection=True;DATABASE=pubs"))
         {
   //Query
   SqlCommand command=new SqlCommand("select * from authors where au_id=@ID",conn);
   command.Parameters.Add("@ID",SqlDbType.NVarChar,11).Value=authorID; 
   conn.Open();


   //DataReader scope - calling the delegate method and finishing it before
   //going out of scope and closing the reader
   using(SqlDataReader rdr=command.ExecuteReader(CommandBehavior.CloseConnection))
   {
    handler(rdr);
   }
  }
 }
}

Page (code-behind)

public class WebForm1 : System.Web.UI.Page
{
 protected System.Web.UI.WebControls.DataGrid DataGrid1;


 private void Page_Load(object sender, System.EventArgs e)
 {
  //Bind grid with given author id
  if(!IsPostBack)
   DataComponent.GetAuthor("172-32-1176",new DataComponent.IDataReaderHandler(BindGrid)); 
 }


 //Bind the datagrid when this is called by the delegate
 private void BindGrid(IDataReader reader)
 {
  DataGrid1.DataSource = reader;
  DataGrid1.DataBind();
 }
}

The point is that data readers can be dangerous if they aren’t in control. With this simple idea you can mitigate the potential threat of running out of resources and maybe even consider passing data readers to the client in proper scenarios. It’s still not as flexible as the data table but with this you can build data readers as part of your DAL to get the maximum flexibility out of ADO.NET.