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();
As part of his fantastic ‘What is .NET standard‘ presentation at DDD12, Adam Ralph provided an amazing amount of detail in such a short amount of time. One of the most valuable points, which is completely obvious when you think about it, is how you should work with .NET standard when creating libraries. NET standard now comes in a multitude of flavours: currently 1.0, 1.1, 1.2, 1.3, 1.4, 1.5, 1.6 and 2.0. When starting out . . .
If you’re trying to access a class library (.NET Standard) from a traditional console application (in VS2017 those can be found under ‘Windows Classic Desktop’) you will run into problems; which can feel a little strange for something that was pretty simple in VS2015 and earlier. You can add a reference to the class library project (Resharper will even volunteer to add the dependency / namespace reference if you don’t already have it). But the . . .