try-catch-FAIL

Failure is inevitable

NAVIGATION - SEARCH

Quick-and-Easy Database Integration Tests with SpecsFor

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?

About Matt Honeycutt...

Matt Honeycutt is a software architect specializing in ASP.NET web applications, particularly ASP.NET MVC. He has over a decade of experience in building (and testing!) web applications. He’s an avid practitioner of Test-Driven Development, creating both the SpecsFor and SpecsFor.Mvc frameworks.

He's also an author for Pluralsight, where he publishes courses on everything from web applications to testing!

blog comments powered by Disqus