Using a "data builder" class for isolating database data in individual tests

by Michael Henstock 23. November 2012 11:40

This post is mostly a reply to this (http://lostechies.com/jimmybogard/2012/10/18/isolating-database-data-in-integration-tests/) post by Jimmy Bogard.

 
How my company has managed the test databases for integration tests is through a class library to handle inserting, updating and cleaning up of the database for an integration test. This class library has a whole bunch of helper methods which simplifies creating data by defaulting the majority of values on each entity and basic management of table relations. Rather than creating deleting and re-creating the entire database, or tables within the database or running all integration tests within a transaction then rolling back, we use our data builder to put data into the database and at the end of the test it knows what was added and just deletes the added items from each table.
 
The goal of using this class library is to have a test class that looks something like this:
[TestFixture]
public class TestClass
{
    SqlDatabuilder _builder;

    [SetUp]
    public void SetUp()
    {
        _builder = new SqlDatabuilder();
        _builder
            .AddTestRow(1, x => x.field2 = "field 2")
            .AddTest2Row(2, x => x.field2 = "field 2")
            .AddTest2Row(3, x => x.field2 = "field 2")
            ;
    }

    [TearDown]
    public void CleanUp()
    {
        _builder.CleanUp();
    }

    [Test]
    public void TestX()
    {
        // Add assertions working on TestRow class.
    }
}
The intention of the SqlDataBuilder class is to simplify adding data to whatever back end service we're using, while keeping a stack of changes that the data builder creates, then roll those changes back in reverse order when we need to do a clean up. That way the creation of custom data setup for a specific test is easy and the clean up of this data is completely handled by the cleanup step.
 
The main area where things can start to unravel is when the test itself is adding data that the data builder isn't aware of, which can cause foreign key exceptions in the clean up step. How we handle these situations is to have another helper method on the data builder that allows us to add some custom clean up actions that the clean up method calls before the standard clean up roll back occurs. This looks something like this:
[TearDown]
public void CleanUp()
{
    _builder
        .AddCleanUpSql("DELETE FROM test3")
        .CleanUp();
}
The majority of the code is handled in the BaseDataBuilder class which I've attached below. A sample implementation which handles the connection to the database and the wrapper methods for adding data to specific tables is shown here with comments:
// The BaseDataBuilder class implements a lot of the plumbing around inserting, updating and deleting data.
// This class is for calling the generic methods with specific helper methods that default all the data.
public class SqlDatabuilder : FluentDataBuilder.BaseDatabuilder
{
    private System.Data.IDbConnection _dbConnection;
    protected override System.Data.IDbConnection dbConnection
    {
        get
        {
            return _dbConnection jQuery152036569983838126063_1365248451800
                (_dbConnection = new System.Data.SqlClient.SqlConnection("Data Source=localhost;Integrated Security=SSPI;database=test;"));
        }
        set
        {
            _dbConnection = value;
        }
    }

    public SqlDatabuilder AddCleanUpSql(
        string sql)
    {
        this.AddDeleteSql(sql);

        return this;
    }

    // The DataContainer class contains the local copy of the databuilders data that has been created
    // This property is implementing the container for a specific table in this case, the table behind TestRow
    private FluentDataBuilder.DataContainer<TestRow, int> _testRowContainer;
    public FluentDataBuilder.DataContainer<TestRow, int> TestRowContainer
    {
        get
        {
            return _testRowContainer jQuery15201317599972244352_1365248810643
                (_testRowContainer = new FluentDataBuilder.DataContainer<TestRow, int>(this, "TestRow"));
        }
    }

    private FluentDataBuilder.DataContainer<TestRow, int> _testRow2Container;
    public FluentDataBuilder.DataContainer<TestRow, int> TestRow2Container
    {
        get
        {
            return _testRow2Container jQuery15201766282683238387_1365330478024
                (_testRow2Container = new FluentDataBuilder.DataContainer<TestRow, int>(this, "TestRow2"));
        }
    }

    // A helper method which adds a new TestRow to the TestRowContainer, defaults the field1 property with the
    // identifier parameter, then calls the InsertEntity helper method which inserts the value straight into the database.
    public SqlDatabuilder AddTestRow(int identifier, Action<TestRow> setProperties = null)
    {
        TestRowContainer.Add(
            identifier,
            x => 
                {
                    x.field1 = identifier;

                    if (setProperties != null)
                    {
                        setProperties(x);
                    }
                },
            x => InsertEntity("test", x, t => t.field1));

        return this;
    }

    // Another helper method similar to the AddTestRow, except it ensures that TestRowContainer has a current value
    // Then adds an item to the TestRow2Container, with information from the TestRowContainer.Current item.
    public SqlDatabuilder AddTest2Row(int indentifier, Action<TestRow> setProperties = null)
    {
        TestRowContainer.EnsureCurrentSet();

        TestRow2Container.Add(
            indentifier,
            x =>
            {
                x.field1 = indentifier;
                x.field2 = TestRowContainer.Current.field1.ToString();

                if (setProperties != null)
                {
                    setProperties(x);
                }
            },
            x => InsertEntity("test", x, t => t.field1));

        return this;
    }
}
The great thing about having the FluentDataBuilder.DataContainer implementation allows the tests to get the most recently added entity (via the Current property) for identifiers or any other settings, and a dictionary based off a key which gives the tests quick access to any of the prepared test data.

 

FluentDataBuilder.zip (3.87 kb)

Tags:

C# | Unit Tests

Comments are closed