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:

[code language=”csharp”] 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; } } [/code]

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”:

[code language=”csharp”] 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); } } } [/code]

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

[code language=”csharp”] 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; } } [/code]

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.

[code language=”csharp”] var customers = new SqlRepository(connectionString).Get( <b>new GetCustomersByFirstName("Paul")</b>, new CustomerDRConvertorPart2()).ToList(); [/code]