In a recent post I described setting up a database test framework to test an MS SQL database. The purpose of creating this framework was to ensure that in stored procedure heavy applications, we could still get a high level of automated test coverage (which is incredibly important for any application that uses any kind of continuous delivery process). That framework didn’t include an easy way to iterate through a result set and was limited to testing just the number results returned. (
See this post for the original framework. Let me know if you need the source and ill get it zipped up).
I have now extended the framework to include the capability
of executing a stored procedure with parameters, and then iterate through a
result set by mapping the output to a data model.
Model the data
First set up a model of your data
public class CustomerBasicAccountModel
{
public
string CustomerID { get; set; }
public
string CustomerSortCode { get; set; }
public string CustomerAccountNumber { get;
set; }
public
string CustomerAffiliation { get; set; }
}
To facilitate reading of the data returned from a stored
procedure I extend the datareader class to consume the reader using linq. This
will allow me to easily manage the data once it is returned from the database.
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
namespace Database.Integration.DAL
{
static class
Extensions
{
public
static IEnumerable<T> Select<T>(this SqlDataReader reader,
Func<SqlDataReader, T> projection)
{
while
(reader.Read())
{
yield return projection(reader);
}
}
}
}
Finally in the test we can do something like this
[Test]
public void TestSortCode()
{
using
(var session = _dbFactory.Create())
{
var
z = session.GetTransaction().Connection.CreateCommand();
z.CommandTimeout = 0;
z.CommandType = CommandType.StoredProcedure;
var query = session.CreateQuery(
@"[GetCustomerBasicAccount]");
query.AddParameter("@customerid", 3, DbType.Int32);
var
dataReader = query.GetAllResults();
while (dataReader.IsClosed==false | dataReader.Read())
{
using (dataReader)
{
customerBasicAccount = dataReader.Select(r => new CustomerBasicAccountModel
{
customerSortCode =
r["CustomerSortCode"] is DBNull
? null
:
r["CustomerSortCode"]
}).ToList();
}
Assert.AreEqual(customerBasicAccount[0].customerSortCode, 278222);
}
}
}
Although this is a somewhat crude way to run integration tests on a database, it really does the trick. My original idea was that developers would simply use existing data mapping within a project and write the integration tests using that, but it has always been difficult to get them to commit to doing this. This framework is generic and can be used in any .net project with ease.
