Skip to main content

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

In Part 1 of this series we started with a basic Data-Reader / SQL Connection/Command pattern and illustrated how it is possible to abstract the parsing of the Data Reader into a standalone object that can be fully unit tested in isolation of the calling code. In Part 2 of the series we made a very simple optimisation to the “DataReader” convertor and updated the tests to capture/verify the changes.

In part 3 of the series we put this all together into a repository pattern to create a reusable and testable data access layer. The first step is to create an interface for the repository.

namespace DataAccess.Example
{
using System.Collections.Generic;
using System.Data.BoilerPlate;
 
public interface IRespository
{
IEnumerable<TEntity> Get(IQuery query, IConvertDataReader<TEntity> dataReaderConvertor);
}
}

The intent implied by the interface is that "Get" will be responsible for returning an enumerable list of a generic. To do this we pass in an implementation of IQuery and an implementation of IConvertDataReader for the generic we are to return. We already have an implementation of IConvertDataReader that we can use from the previous post. In this example the implementation of IQuery just returns SQL text that can be executed and is shown below.

namespace DataAccess.Example
{
public interface IQuery
{
string Text { get; }
}
}
 
namespace DataAccess.Example
{
public class GetAllCustomersQuery : IQuery
{
public string Text
{
get
{
return "SELECT id, Firstname, Surname FROM Customer";
}
}
}
}

The final piece of the jigsaw is the implementation of the repository interface, in this instance for SQL but it could be any data provider that returns an implementation of IDataReader. A basic implementation of the SQL repository is shown below:

namespace DataAccess.Example
{
using System.Collections.Generic;
using System.Data.BoilerPlate;
using System.Data.SqlClient;
 
public class SqlRepository : IRespository
{
private readonly SqlConnectionStringBuilder config;
 
public SqlRepository(SqlConnectionStringBuilder config)
{
this.config = config;
}
 
public IEnumerable<TEntity> Get<TEntity>(IQuery query, IConvertDataReader<TEntity> dataReaderConvertor)
{
using (var connection = new SqlConnection(this.config.ConnectionString))
{
using (var command = connection.CreateCommand())
{
command.CommandText = query.Text;
connection.Open();
 
using (var dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
yield return dataReaderConvertor.Parse(dataReader);
}
}
}
}
}
}
}

We now have a repository object that can be used against any SQL database to return a list of any type of object that can be populated using a standard SQL SELECT statement; the code below shows how to put this together.

var customers = new SqlRepository(connectionString).Get(
new GetAllCustomersQuery(),
new CustomerDRConvertorPart2()).ToList();

To understand the power of this pattern, consider the following code that is all that is needed to update the previous code to return a different list of customers; in this case all those with the first name of "Paul".

namespace DataAccess.Example
{
public class GetAllCustomersCalledPaul : IQuery
{
public string Text
{
get
{
return "SELECT id, Firstname, Surname FROM Customer WHERE Firstname = 'Paul'";
}
}
}
}
 
var customers = new SqlRepository(connectionString).Get(
new GetAllCustomersCalledPaul(),
new CustomerDRConvertorPart2()).ToList();

Part 4 shows how the code can be modified so it can be extended to handle different "query types".

Comments

Popular posts from this blog

Mocking HttpCookieCollection in HttpRequestBase

When unit testing ASP.NET MVC2 projects the issue of injecting HttpContext is quickly encountered.  There seem to be many different ways / recommendations for mocking HttpContextBase to improve the testability of controllers and their actions.  My investigations into that will probably be a separate blog post in the near future but for now I want to cover something that had me stuck for longer than it probably should have.  That is how to mock non abstract/interfaced classes within HttpRequestBase and HttpResponseBase – namely the HttpCookieCollection class.   The code sample below illustrates how it can be used within a mocked instance of HttpRequestBase.  Cookies can be added / modified within the unit test code prior to being passed into the code being tested.   After it’s been called, using a combination of MOQ’s Verify and NUnit’s Assert it is possible to check how many times the collection is accessed (but you have to include the set up calls) and that the relevant cookies have …

Injecting HttpContextBase into an MVC Controller

It is a shame that when the ASP.NET MVC framework was released they did not think to build IoC support into the infrastructure. All the major components of the MVC engine appear to magically inherit instances of HttpContext and it’s related objects – which can cause no end of problems if you are trying to utilise Unit Testing and IoC. Reading around various articles on the subject just to get around this one problem requires the implementation of several different concepts and you are still left with a work around. The code below, along with the other links referenced in this article is my stab at resolving the issue. There’s probably nothing new here, but it does attempt to relate all the information needed to do this for Castle Windsor. The overview is that all controllers will need to inherit from a base controller, which takes an instance of HttpContext into it’s constructor. It then overrides the property HttpContext in the main controller class, supplying it’s own version…

Unit Testing Workflow Code Activities - Part 1

When I first started looking into Windows Workflow one of the first things that I liked about it was how it separated responsibilities. The workflow was responsible for handling the procedural logic with all it's conditional statements, etc. Whilst individual code activities could be written to handle the business logic processing; created in small easily re-usable components. To try and realise my original perception this series of blog posts will cover the unit testing of bespoke code activities; broken down into: Part One: Unit testing a code activity with a (generic) cast return type (this post)Part Two: Unit testing a code activity that assigns it's (multiple) output to "OutArguments" (Not yet written)So to make a start consider the following really basic code activity; it expects an InArgument<string> of "Input" and returns a string containing the processed output; in this case a reverse copy of the value held in "Input".namespace Ex…