About a month ago I posted an article on how I like to use SqlDataReaders.  In said article I discussed how, rather than actually retrieving a data reader from my command object and walking the results, I like to walk the data reader in a callback method.  I propose that this strategy helps constrain how data readers are used and better manages their lifetimes.

I want to take it a step further now and talk about some additional enhancements that can be made to enhance the experience of using data readers all the while leveraging some cool .NET 2.0 functionality.  Last month's post was intended to be informative without dwelling on and nitpicking little details.  For example, sure I could have used anonymous delegates as the callback methods, but that would have muddied the discussion.  Today, however, I'm going down that path

I really like the generic QueryReader method that I proposed last month, but there are some issues with it:

• The generic method uses a generic delegate callback.  However, using this mechanism you're required to have the callback method return a value of type T.  This isn't always desirous, especially if the callback method simply populates a control and doesn't want to burden itself with building a result set to return it only to have it iterated immediately to populate the control.  Sure, if the list is going to be used elsewhere and/or more generically, then it's great, but not 100% of the time.
• Along with the previous point, you cannot call an instance of the method with T of type void.
• If the callback method needs some information (e.g. state) on which to operate other than the data reader the signature needs to be enhanced.

To address these issues, I've made some changes and ended up with the following:

internal delegate T DataReaderCallback<T>(object state, SqlDataReader dr);
internal delegate VoidDataReaderCallback(object state, SqlDataReader dr);

internal static class DataAccess {
   internal static T QueryReader<T>(SqlCommand cm, object state, DataReaderCallback<T> callback) {
      SqlConnection cn = getConnection();
      SqlDataReader dr = null;
      try {
         cm.Connection = cn
         dr = cm.ExecuteReader(CommandBehavior.CloseConnection);
         return ( null != callback ) ? callback(state, dr) : default(T);
      }
      finally {
         if ( null != dr ) dr.Dispose();
         if ( null != cn ) cn.Dispose();
         cm.Connection = null;
      }
   }


   internal static void QueryReader(SqlCommand cm, object state, VoidDataReaderCallback callback) {
      SqlConnection cn = getConnection();
      SqlDataReader dr = null;
      try {
         cm.Connection = cn
         dr = cm.ExecuteReader(CommandBehavior.CloseConnection);
         if ( null != callback ) callback(state, dr);
      }
      finally {
         if ( null != dr ) dr.Dispose();
         if ( null != cn ) cn.Dispose();
         cm.Connection = null;
      }
   }
}

By implementing it as such, you have two overloads: one similar to before where the callback method returns a value and the second simply operates on the reader and is done - no return value is set.  Purists might identify that the 'state' parameter is typed as 'object' rather than generic or typed.  Ok, ok, if you want it, go ahead and make that change.

This now gives us the ability to do the following:

using ( SqlCommand cm = new SqlCommand("SELECT Name FROM Account ORDER BY Name") ) {
   VoidDataReaderCallback callback = delegate(object state, SqlDataReader dr) {
      while ( dr.Read() )
         cboAccounts.Items.Add(dr.GetString(0));
   };
   DataAccess.QueryReader(cm, null, callback);
}

Ok, so it's nothing earth shattering, but I like its simple elegance.