Skip to main content

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

Recently we’ve been looking at improving our unit test code coverage and reducing the amount of duplicated code around our bespoke data access layer.  Where possible we have moved over to NHibernate but certain parts of the data access must still be written using the standard ADO.NET connection/command pattern.  Typically hidden right in the middle of this bespoke code is a while loop that is pivoting a data reader into a POCO that is impossible to repeat-ably unit test in a stable environment unless you set up a dedicated data repository for testing or try to wrap up / mock the connection / command objects.   Neither of these options are really desirable as we aren’t really interested in testing / mocking the .NET provider data access objects.

To get around this issue we looked into how we could generate some boiler-plate code that we could roll out across our code base.  This code base will be introduced step by step during this series with the first step covering the abstraction of the data reader processing into a standalone object that can be tested in isolation of the data access code.   The example code that we are looking to migrate is shown below, a typical unrestricted “Get()” call (in this example we don’t have many customers!).

public IEnumerable<Customer> Get()
{
using(var connection = new SqlConnection(this.config.ConnectionString))
{
using(var command = connection.CreateCommand())
{
command.CommandText = "SELECT id, Firstname, Surname FROM Customer";
connection.Open();

using (var dataReader = command.ExecuteReader())
{
while(dataReader.Read())
{
yield return new Customer
{
Id = dataReader.GetGuid(dataReader.GetOrdinal("Id")),
FirstName = dataReader.GetString(dataReader.GetOrdinal("FirstName")),
Surname = dataReader.GetString(dataReader.GetOrdinal("Surname"))
};
}
}
}
}
}

Hidden below (and behind) a concrete implementation of a SqlConnection and and SqlCommand is the code that we are interested in right now:

yield return new Customer
{
Id = dataReader.GetGuid(dataReader.GetOrdinal("Id")),
FirstName = dataReader.GetString(dataReader.GetOrdinal("FirstName")),
Surname = dataReader.GetString(dataReader.GetOrdinal("Surname"))
};

As previously stated, unless we set up a test repository or find a way to wrap / mock these concrete instances we are unable to test the creation from the data reader of the customer POCO. To rectify this we start with an interface defining how we would like the calling code to convert the passed in data reader:

namespace System.Data.BoilerPlate
{
public interface IConvertDataReader<out T>
{
T Parse(IDataReader dataReader);
}
}

Taking a very basic customer POCO object:

namespace DataAccess.Example
{
using System;

public class Customer
{
public Guid Id { get; set; }
public string FirstName { get; set; }
public string Surname { get; set; }
}
}

An implementation of the IConvertDataReader interface can be created quite simply using the code below.  The validation / error checking around each data reader value conversion could be as simple or as complex as you need. Part two of this series will cover a basic optimisation that can be made to this code to potentially speed up access in repeated calls such as a “while(dataReader.Read())” loop.

namespace DataAccess.Example
{
using System.Data;
using System.Data.BoilerPlate;

public class CustomerDRConvertor : IConvertDataReader<Customer>
{
public Customer Parse(IDataReader dataReader)
{
return new Customer
{
Id = dataReader.GetGuid(dataReader.GetOrdinal("Id")),
FirstName = dataReader.GetString(dataReader.GetOrdinal("FirstName")),
Surname = dataReader.GetString(dataReader.GetOrdinal("Surname"))
};
}
}
}

This data reader convertor can now be unit tested in isolation of the code that will be implementing it, using a combination NUnit and Mock to both assert actual returned results and verify expected behaviour. It’s probably worth highlighting at this stage we are using a combination of MsTest and NUnit, doing things this way brings the best of both worlds – in TFS you get automated Unit Testing as part of the CI build as you are referencing MsTest, but by adding an alias to NUnit’s assert you are getting access to NUnit’s fluent API (which we prefer).

namespace DataAccess.Example.Tests
{
using System;
using System.Data;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using NUnit.Framework;
using Moq;
using Assert = NUnit.Framework.Assert;

[TestClass]
public class CustomerDRConvertorTests
{
[TestMethod]
public void CustomerDRConvertor_GoodCall()
{
var dataReader = new Mock<>IDataReader>();
dataReader.Setup(dr => dr.GetOrdinal("Id")).Returns(1);
dataReader.Setup(dr => dr.GetOrdinal("FirstName")).Returns(2);
dataReader.Setup(dr => dr.GetOrdinal("Surname")).Returns(3);

var id = Guid.NewGuid();
const string firstName = "John";
const string surname = "Doe";

dataReader.Setup(dr => dr.GetGuid(1)).Returns(id);
dataReader.Setup(dr => dr.GetString(2)).Returns(firstName);
dataReader.Setup(dr => dr.GetString(3)).Returns(surname);

var convertor = new CustomerDRConvertor();
var customer = convertor.Parse(dataReader.Object);

Assert.That(customer.Id, Is.EqualTo(id));
Assert.That(customer.FirstName, Is.EqualTo(firstName));
Assert.That(customer.Surname, Is.EqualTo(surname));

dataReader.Verify(dr => dr.GetOrdinal(It.IsAny<string>()), Times.Exactly(3));
dataReader.Verify(dr => dr.GetOrdinal("Id"), Times.Once());
dataReader.Verify(dr => dr.GetOrdinal("FirstName"), Times.Once());
dataReader.Verify(dr => dr.GetOrdinal("Surname"), Times.Once());
dataReader.Verify(dr => dr.GetGuid(It.IsAny<int>()), Times.Once());
dataReader.Verify(dr => dr.GetGuid(1), Times.Once());

dataReader.Verify(dr => dr.GetString(It.IsAny<int>()), Times.Exactly(3));
dataReader.Verify(dr => dr.GetString(2), Times.Once());
dataReader.Verify(dr => dr.GetString(3), Times.Once());
}
}
}

Obviously the above is a very basic test, but any solution can be scaled to reflect any addition or complex processing that may take place in the implementation of Parse(IDataReader dataReader) method. In fact in part two of this series we will highlight how we can implement some potential implementation and verify the expected behaviour whilst confirming that the returned results have not changed.

Finally to wrap up part one of this series here is the original code updated to reflect the changes discussed here. We’ve not really changed a lot, but are now able to unit test code that was previously difficult to easily reach.


public IEnumerable<Customer> Get()
{
using (var connection = new SqlConnection(this.config.ConnectionString))
{
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT id, Firstname, Surname FROM Customer";
connection.Open();
using (var dataReader = command.ExecuteReader())
{
var convertor = new CustomerDRConvertor();
while (dataReader.Read())
{
yield return convertor.Parse(dataReader);
}
}
}
}
}

Part 2 of this series shows how the code covered in this post can easily be optimised to increase performance, with tests updated to reflect the change.

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…

Problem installing AWS CLI

It never feels like a good start when you're trying to start out with something and the install fails with an obscure error! I was just trying to install the Amazon CLI following the instructions at https://aws.amazon.com/cli/ and ran into the following error when running 'pip install awscli': Collecting awscli Could not find a version that satisfies the requirement awscli (from versions: ) No matching distribution found for awscli I appeared to have a correct version of Python installed (v2.7) and checking "PIP -v" indicated that 9.0.1 was installed. That all seemed to tick the required boxes but digging around a little more I did see that some people had had issues with various versions of PIP so I found / ran the following to upgrade to the latest vesion: curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py python get-pip.py This installed v9.0.3 of PIP which burst into life when I re-ran 'pip install awscli' and everything seems to be ok. Like…