Improving “Boiler Plate” Data-Reader Code – Part 5

In this post, we will extend the query functionality to handle stored procedures with parameters. To do this we need to create a new query type interface with an example implementation:

public interface IDefineAStoredProcedureQuery : IQuery
{
   string StoredProcName { get; }
   IList<SqlParameter> Parameters { get; }
}
public class GetCustomersByFirstName : IDefineAStoredProcedureQuery
{
   public GetCustomersByFirstName(string firstName)
   {
      this.Parameters = new List<SqlParameter> { new SqlParameter("FirstName", firstName) };
   }
   public string StoredProcName { get { return "GetCustomersByFirstName"; } }
   public IList<SqlParameter> Parameters { get; private set; }
}

Now that we have the ability to create stored procedure queries we need something to handle them. To do this we need a concrete implementation of the interface “IHandleAQuery”:

public class StoredProcedureQueryHandler : IHandleAQuery
{
   public void Assign(SqlCommand command, IQuery query)
   {
      var castQuery = query as IDefineAStoredProcedureQuery;
      command.CommandType = CommandType.StoredProcedure;
      command.CommandText = castQuery.StoredProcName;
      if (castQuery.Parameters != null)
      {
         command.Parameters.AddRange((SqlParameter[])castQuery.Parameters);
      }
   }
}

Finally, we update our factory to handle the new query interface and return the correct handler:

public static class QueryHandlerFactory
{
   public static IHandleAQuery Create(IQuery query)
   {
      if (query is IDefineCommmandTextQuery)
      {
         return new HandleCommandTextQuery();
      }
      if (query is IDefineAStoredProcedureQuery)
      {
         return new StoredProcedureQueryHandler();
      }
      var ex = new NotSupportedException();
      ex.Data.Add("IQuery Type", query.GetType());
      throw ex;
   }
}

This can now be called using the following code; which should highlight the power of this repository pattern. Whilst we have implemented quite a lot of code behind the scenes the only change the consumer sees is what type of query object they are passing in.

var customers = new SqlRepository(connectionString).Get(
   <b>new GetCustomersByFirstName("Paul")</b>,
   new CustomerDRConvertorPart2()).ToList();