Unit Testing Database Library

• Download source code - 54.1 KB
• Download demo - 620 KB

Introduction

I'm a big fan of unit testing; it's a practice that has been discussed in a lot of places, so I'm not going to get into a discussion about the positives and negatives of whether to apply it.

I'm also a big fan of mocking when performing unit testing. By developing objects that are loosely coupled to one another and injecting mocks, you can test large parts of a system without the requirement of an external resource such as a database. However, there comes a time when we do actually want to check that our data access code is working correctly against the database; this is were it gets tricky, and where the purpose of this article and the supplied library come in.

N.B. The current library uses NUnit as the unit testing framework; however, it would not be much work to change this to use another such as MBUnit.

Why testing against a database is hard?

We don't have full control over the database from the test case, if we were testing against our BLL, domain, service layer etc... and if we use a mock of our data access code, we have full control over what we return. This gives a set a of repeatable and predictable set of data to test against. When we test against the database, we cannot guarantee that the data we want to test against hasn't changed - this could be by outside intervention, or if we were not careful, one of our other tests may have changed data.

What steps can we make to help test against the database?

1. Use a separate database for unit testing. This can be quite a challenge depending on how strict you are at scripting objects in the database (using the same database which is used for other environments is asking for trouble).
2. Set guidelines for how this database should be used. Make sure that this database does not get overly out of sync with other environment databases (database compare tools or database source control could help here).
3. Make sure that each test starts with predictable data, and remove this data before the next test.

How the Database Unit Testing Library helps

The library supplied provides a base class that can be inherited from by your test fixture classes. It provides the following features:

• It can be told to run SQL scripts before and after every test via the Setup and the Teardown. This can help make sure data is in a predictable state before a test, and can make sure that it is cleaned up ready for the next test.
• It also wraps up common database access code so that you don't have to manually create connection code over and over. It also does this in a generic way, making use of the ADO.NET DbProviderFactory, so you should be able to use this against different database vendors with only minor config changes (I have only tested against SQL Server 2000).

Using the library

1. From your project that you are going to perform database unit testing, add a reference to the DatabaseUnitTestingLibrary project/DLL.
2. Now, simply create a test fixture class and inherit from the DatabaseTestFixture class.
3. Add an app.config file.
4. Add a new appSetting called data-scripts-directory, and put in the location where you want the SQL scripts to be.
5. Add a connection strings section and name it unit-testing-connection. Now, put in the connectionString and also the providerName attributes.

Running the setup and teardown scripts

The library uses the location specified in the data-scripts-directory setting to find scripts. The library uses the following convention:

• Setup scripts will be post fixed with .setup.sql
• Teardown scripts will be post fixed with .teardown.sql

So, if I had staff setup and teardown scripts, they would be named staff.setup.sql and staff.teardown.sql.

Once you have the SQL scripts in place, you now need to override either or both the SetupDataScriptFiles and TeardownDataScriptFiles properties and provide a string array of the script names without the postfix. For example:

protected override string[] SetupDataScriptFiles
{
    get { return new string[] { "staff" }; }
}

protected override string[] TeardownDataScriptFiles
{
    get { return new string[] { "staff" }; }
}

would run both the staff.setup.sql and the staff.teardown.sql scripts

In my setup scripts, I have tended to put SQL to insert data, and in my teardown scripts, I have tended to put SQL to delete this data and truncate the table to reset the seed column. I think that this will be the same in 95% of cases.

Performing common data access

When performing data access inside a test to verify that correct data exists in the database, the DatabaseTestFixture class provides a property called DataAccess under the hood exposing an object called DataAccessHelper. This object handles opening its own connection, and implements IDisposable, which will tell it when to dispose of its connection. An example of its usage is shown below:

using (DataAccess)
{
    IDataReader reader = DataAccess.ExecuteReader(sql);
    while (reader.Read())
    {
        Assert.AreEqual("Bloggs", reader.GetString(1));
        Assert.AreEqual("Joe", reader.GetString(2));
    }
}

When using DataAccess, make sure that either a using statement is used or a try...finally with Dispose called in the finally block, to make sure that the connection is correctly disposed.

Library usage

This library is under no license, and can be freely used for commercial and non-commercial software. Please feel free to make any changes to the source. Could I please ask that if you do make any changes, you please post a comment here about the changes, or drop me an email, so it can help other people in the CodeProject community? Thanks.

History

• V1.0 - 2008/04/13 - Initial draft.