public delegate T ReaderHandler<T>(IDataReader reader);
public delegate T CommandHandler<T>(IDbCommand cmd);

I believe that they are pretty simple to grasp, lets see the usage of those delegates in the generic template.

Step 2 – The generic “command executer” template:

/// <summary>
/// Simple command executer “design pattern”.
/// </summary>
/// <typeparam name=”T”>The type to return</typeparam>
/// <param name=”cmd”>The command</param>
/// <param name=”handler”>The handler which will receive the open command and handle it (as required)</param>
/// <returns>A generic defined result, according to the handler choice</returns>
public static T ExecuteCommand<T>(IDbCommand cmd, CommandHandler<T> handler) //*1
{
   try
   {
      using (IDbConnection conn = ProviderFactory.CreateConnection()) //*2
      {
         cmd.Connection = conn;

         // Trace the query & parameters.
         DatabaseTracer.WriteToTrace(TraceLevel.Verbose, cmd, “Data Access Layer – Query profiler”); //*3

         conn.Open();

         return handler(cmd); //*4
      } //”using” will close the connection even in case of exception.
   }
   catch (Exception e)
   {
      // Trace the exception into the same log.
      Tracer.WriteToTrace(TraceLevel.Error, e, “Data Access Layer – Exception”); //*5

      throw WrapException(e); //*6
   }
}

I know, this is hard to watch at first glance, but I’ll walk you through:

*1: Notice the generic type “T” – this will be necessary for returning different types depending on programmer choice (see step 4 for further details).
*2: Create a connection – I’m using some factory I’ve built in order to return a strongly typed connection depending on the selected provider in the application configuration(*.config) file.
*3: Trace the command (CommandText, Parameters etc) – DataBaseTracer class check the “switch” I’ve declared in the .config file and trace the query only if it was requested. This will give me that ability to trace all the queries later on in the production environment (good for prodction debugging).
*4: Send the live command(the connection was opened) to the handler so it can use the command for its needs.
*5: Trace the exception, again, only if requests.
*6: Wrap the exception in DALException – myself, as an architect, believe that the Data Access Layer should throw only DALException exceptions.

Step 3 – A first usage of the template:

/// <summary>
/// Execute the db command as reader and parse it via the given handler.
/// </summary>
/// <typeparam name=”T”>The type to return after parsing the reader.</typeparam>
/// <param name=”cmd”>The command to execute</param>
/// <param name=”handler”>The handler which will parse the reader</param>
/// <returns>A generic defined result, according to the handler choice</returns>
public static T ExecuteReader<T>(IDbCommand cmd, ReaderHandler<T> handler)
{
   return DalServices.ExecuteCommand<T>(cmd,
      delegate(IDbCommand liveCommand) //*1
      {
         IDataReader r = liveCommand.ExecuteReader();
         return handler(r);
      });
}

This one is even harder to follow, but relax, it’s not as bad as you might think.
*1: You can see that I’m using anonymous delegate for the CommandHandler<T>, so the delegate gets the live command object from the ExecuteCommand method and call ExecuteReader() on it. Afterwards, it sends the reader to the ReaderHandler<T> handler (given as parameter).

Step 4 – Real life example, using ExecuteReader<T> to parse a reader into List<Person> and string:

/// <summary>
/// Retrieve the persons according to the specified command.
/// </summary>
/// <param name=”getCmd”>The command to run.</param>
/// <returns>Typed collection of person.</returns>
protected List<Person> ExecuteReader(IDbCommand cmd)
{
   return DalServices.ExecuteReader<List<Person>>(cmd,
      delegate(IDataReader r)
      {
         List<Person> persons = new List<Person>();
         
         while (r.Read())
            // Create a Person object, fill it by the reader and add it to the “persons” list.
      
         return persons;
      });
}

/// <summary>
/// Retrieve the persons xml according to the specified command.
/// </summary>
/// <param name=”getCmd”>The command to run.</param>
/// <returns>Xml representation of the persons.</returns>
protected string ExecuteReader(IDbCommand cmd)
{
   return DalServices.ExecuteReader<string>(cmd,
      delegate(IDataReader r)
      {
         StringBuilder res = new StringBuilder();
         
         while (r.Read())
            // Build the person object xml and add it to “res”.
      
         return res;
      });
}

Step 5 – Leveraging the command executer template:

Now that we understand the template, let’s wrap some more execution “modes”. You can add to it later on, according to your needs.

/// <summary>
/// Execute the db command in “NonQuery mode”.
/// </summary>
/// <param name=”cmd”>The command to parse</param>
/// <returns>Affected rows number</returns>
public static int ExecuteNonQuery(IDbCommand cmd)
{
   return DalServices.ExecuteCommand<int>(cmd,
      delegate(IDbCommand liveCommand)
      {
         return liveCommand.ExecuteNonQuery();
      });
}

/// <summary>
/// Execute the db command in “Scalar mode”.
/// </summary>
/// <typeparam name=”T”>The type to return after parsing the reader.</typeparam>
/// <param name=”cmd”>The command to execute</param>
/// <returns>A generic defined result, according to the handler choice</returns>
public virtual T ExecuteScalar<T>(IDbCommand cmd)
{
   return DalServices.ExecuteCommand<T>(cmd,
      delegate(IDbCommand liveCommand)
      {
         return (T)liveCommand.ExecuteScalar();
      });
}

Conclusions:

Now that every command run via ExecuteCommand<T> method I can capture the entire communication between my Application and my database and make some enhanced production debugging, if I’ll need to. In addition, I can decide what to do in case of an exception or add some generic validation checks later on.

All in just one place.

This is a “real life” usage of the power Generics & delegates give us; Using them may be harder at first, but playing with them for a little bit can give you the ability to “patternize” your repetitive code and thus give you a tremendous power to control and manipulate it.

I hope I was clear enough,
Any feedbacks would be great !