Thursday, 16 February 2012

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(
new GetCustomersByFirstName("Paul"),
new CustomerDRConvertorPart2()).ToList();

No comments:

Post a Comment