Introduction
This tip presents an easy way to test a data access layer which uses data readers to read data from database and returns POCO (or list of them).
Background
I've had this idea while answering a question in forums. How to easily test a data access class which uses data readers? Well, mocking IDataReader
can be a lot of work. Especially if there are many columns or rows, or both. It would be great if we can transform a list of objects into a data reader and then check the results against the same list.
Using the Code
Let's assume we have data access class with a method like this:
public List<Person> GetAll()
{
var result = new List<Person>();
using (var reader = _databaseFacade.ExecuteReader("usp_person_all"))
{
var salaryIndex = reader.GetOrdinal("salary");
while(reader.Read())
{
var person = new Person
{
Id = (int)reader["person_id"],
Name = (string)reader["name"],
DateOfBirth = (DateTime)reader["date_of_birth"],
IsVegetarian = ((string)reader["is_vegetarian"]) == "Y",
Salary = reader.IsDBNull(salaryIndex) ? (decimal?)null : reader.GetDecimal(salaryIndex)
};
result.Add(person);
}
}
return result;
}
And we want to test it with this code:
var expected = new List<person>
{
new Person{Id=1508,Name="Mary",DateOfBirth=new DateTime(1978, 4, 27)},
new Person{Id=2415,Name="Theodor",DateOfBirth=new DateTime(1976, 3, 20),Salary=50000m},
new Person{Id=3486,Name="Peter",DateOfBirth=new DateTime(1979, 2, 23),IsVegetarian=true},
new Person{Id=4866,Name="Michael",DateOfBirth=new DateTime(1980, 12, 14)},
new Person{Id=5345,Name="Rachel",DateOfBirth=new DateTime(1983, 1, 8)},
};
var dataReader = ... var personDataAccess = ...
var actual = personDataAccess.GetAll();
CollectionAssert.AreEqual(expected, actual);
Implementing IDataReader
is rather boring but not very complicated. The real trick is accessing object's properties using an index to array. We could extract property information via reflection, build the index and then get the property value via reflection again. But it would be much nicer if we can have a function which simply flattens the property values into an array of object
like this:
var row = new object[]
{
person.Id,
person.Name,
person.DateOfBirth,
person.IsVegetarian,
person.Salary
};
LINQ expressions come to help here. Our data reader stub will have two parameters: list of objects and a mapping function expression in the form of new object initialization:
public DataReaderStub(IEnumerable<TObject> items, Expression<Func<TObject, TRow>> mapper)
Please note that we want TRow
to be an anonymous type as this will save us a lot of work. Because of that however, we cannot instantiate DataReaderStub
directly, but an extension function will do the job for us.
public static IDataReader AsDataReader<TObject, TDataRow>
(this IEnumerable<TObject> items, Expression<Func<TObject, TDataRow>> mapper)
{
return new DataReaderStub<TObject, TDataRow>(items, mapper);
}
Now we can create the data reader. Note that the columns get renamed. This is useful as naming convention is often different in database than in application code.
var dataReader = expected.AsDataReader(x => new
{
person_id = x.Id,
name = x.Name,
date_of_birth = x.DateOfBirth,
is_vegetarian = x.IsVegetarian ? "Y" : "N",
salary = x.Salary
});
So what's happening inside the DataReaderStub
constructor? First, we examine the mapper expression and get all members of the new anonymous type. These are all properties. We use this information to initialize the structure of the data reader i.e., columns and their types.
var newExpression = (NewExpression)mapper.Body;
var members = newExpression.Members.Cast<PropertyInfo>().ToArray();
_nameIndex = new Dictionary<string, int>(members.Length); _name = new string[members.Length];
_type = new Type[members.Length];
for (int i = 0; i < members.Length; i++)
{
var name = members[i].Name;
var type = members[i].PropertyType;
_name[i] = name;
_type[i] = Nullable.GetUnderlyingType(type) ?? type;
_nameIndex.Add(name, i);
}
Here comes the most interesting part where we create the function which will turn an instance of TObject
into array of objects. We use the arguments of the object initializer to access the values. We will then convert each into object
and wrap them in an array. All of these expression use the same parameter - an instance of TObject
. We will reuse it as our function will have the same parameter.
var parameter = ((MemberExpression)newExpression.Arguments[0]).Expression as ParameterExpression;
var arrayItems = newExpression.Arguments.Select(x => Expression.Convert(x, typeof(object)));
var newArray = Expression.NewArrayInit(typeof(object), arrayItems);
var toArray = Expression.Lambda<Func<TObject, object[]>>(newArray, parameter);
_objectToArray = toArray.Compile();
The Read()
method of the data reader is not complicated - read next object from input list an transform it to object[]
. All the GetXXX()
methods then use the row data directly.
private readonly IEnumerator<TObject> _enumerator; private object[] _row;
public bool Read()
{
_row = null;
var ok = _enumerator.MoveNext();
if (ok)
_row = _objectToArray(_enumerator.Current);
return ok;
}
public decimal GetDecimal(int i)
{
return (decimal)_row[i];
}
And this is the list after being loaded into a data table:
Points of Interest
This is all very nice but... from the testing point of view, there is a question mark. Namely, we are deriving our test results directly from our expectations. I'm not saying this makes the test invalid, but certainly is something to be aware of.
Another concern is performance. It may be useful to split the data reader into two parts, one holding the structure, the other holding the state. Then you can cache the structure to save yourself the penalty of building it over and over again.