Mar 6 2012

Quick-and-Easy Database Integration Tests with SpecsFor

Category: Databases | Testing | SpecsForMatt @ 16:26

SpecsFor makes it very easy to bolt on your own conventions, create your own base classes, and extend its behavior to support your specific testing needs.  I’m working on a project that’s built on LINQ to SQL, and I wanted to start creating integration tests around our stored procedures and views.  Here’s the base class I made to handle establishing a database connection, loading in “seed data,” and then cleaning up after each set of specs once they’re finished.

Here’s the base class for our database specs, annotated with comments:

public abstract class SpecsForDatabase : SpecsFor<BNControlDataContext>
{
    private TransactionScope _scope;

    public override void SetupEachSpec()
    {
        //1) A transaction is started at the beginning of each spec.
        //   This transaction is never committed, so any changes
        //   the spec makes are discarded once all the test cases
        //   in the spec have executed.
        _scope = new TransactionScope();

        base.SetupEachSpec();
    }

    //2) This hook enables full control over the creation of the system(class)-under-test,
    //   which in this case is a LINQ to SQL data context.
    protected override void InitializeClassUnderTest()
    {
        //The test project is pointed at a test database.
        SUT = new BNControlDataContext(ConfigurationManager.ConnectionStrings["csBNControlTest"].ConnectionString);

        //3) For now, the seed data is reloaded at the beginning of each spec suite.
        //   One optimization I'm exploring is handling the initial seed data in a 
        //   setup fixture.
        ReloadTestSeedData();
    }

    private void ReloadTestSeedData()
    {
        //4) The base seed data is stored in a SQL script that's packaged as an embedded resource.
        using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("Database.TestSeedData.sql"))
        using (var reader = new StreamReader(stream))
        {
            var script = reader.ReadToEnd();

            SUT.ExecuteCommand(script);
        }
    }

    public override void TearDown()
    {
        base.TearDown();
        //5) After all the test cases have executed, the transaction is discarded, which
        //   rolls back any changes that the tests made to the database.  This provides
        //   each spec with its own isolated context.
        _scope.Dispose();
    }
}

Here’s an example of using this base class to test the behavior of a view through LINQ to SQL:

public class when_retrieving_entities : SpecsForDatabase
{
    private MyEntityType[] _entities;

    //1) Context is established here using test data builders, exposed
    //   as extension methods of the context.  These builders will use
    //   reasonable defaults but can be easily changed. 
    protected override void Given()
    {
        SUT.BuildEntityType()
            .ID("DummyID1")
            .Finish();
            
        SUT.BuildEntityType()
            .ID("DummyID2")
            .Finish();

        SUT.SubmitChanges();
    }

    //2) The contents of the view are retrieved, which will now include anything
    //   from the base seed data as well as the new entities that were added
    //   in the Given method.
    protected override void When()
    {
        _entities = SUT.MyEntityTypes.ToArray();
    }

    //3) Each test case is then executed.  Given and When are only executed once
    //   for the entire fixture as opposed to once for each test case. 
    [Test]
    public void then_it_returns_the_expected_number_of_rows()
    {
        _entities.Count().ShouldEqual(5);
    }

    [Test]
    public void then_it_returns_the_dummy_entity()
    {
        _entities.SingleOrDefault(e => e.ID == "DummyID1").ShouldNotBeNull();
    }
}

We don’t (yet) expose our stored procedures through LINQ to SQL, so I created a helper method to assist in testing sprocs through ADO.NET:

public class when_retrieving_from_some_sproc : SpecsForDatabase
{
    private dynamic[] _result;

    protected override void Given()
    {
        SUT.BuildEntityType()
            .Named("Dummy Entity")
            .Active(true)
            .Finish();

        SUT.BuildEntityType()
            .Active(false)
            .Finish();

        SUT.SubmitChanges();
    }

    protected override void When()
    {
        var args = new { DateFrom = DateTime.Today.AddDays(-15), DateTo = DateTime.Today.AddDays(5), IsActive = 1};
        _result = SUT.ExecuteProcedure("usp_ActiveEntities_Find", args).ToArray();
    }

    [Test]
    public void then_it_returns_only_active_entities()
    {
        _result.Count().ShouldEqual(1);
    }
    
    [Test]
    public void then_it_returns_the_entity_name()
    {
        ((string)_result[0].Name).ShouldEqual("Dummy Entity");
    }
}

Here’s the source for the helper:

public static class StoredProcedureTesterExtensions 
{
    public static IEnumerable<dynamic> ExecuteProcedure<TParams>(this DataContext context, string procedureName, TParams parameters)
    {
        var connection = (SqlConnection)context.Connection;

        if (connection.State != ConnectionState.Open)
        {
            connection.Open();
        }

        var command = connection.CreateCommand();
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = procedureName;

        foreach (var prop in parameters.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public))
        {
            command.Parameters.AddWithValue(prop.Name, prop.GetValue(parameters, null));
        }

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                var row = new ExpandoObject() as IDictionary<string, object>;

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    row.Add(reader.GetName(i), reader[i]);
                }

                yield return row;
            }
        }
    }
}

Actually using ‘dynamic’ felt a bit odd at first, but it seemed to be at least as good a solution as returning a DataReader or DataSet.

So, what do you think?

Tags:

blog comments powered by Disqus