Unit Test Utility: Restoring Database State after each unit test execution

Introduction

Need:

Lets start with an example, suppose we are writing unit tests for following two cases:

1. GetCustomersByIdGreaterThan()
2. AddCustomer()

Initial state of database is like

We run first unit test case for GetCustomersByIdGreaterThan()

[Test]
public void CanGetCustomersByIdGreaterThan()
{
    const int CUSTOMER_ID = 1;
    const int EXPECTED_COUNT = 4;

    IList<Customer> customers = provider.GetCustomersByIdGreaterThan(CUSTOMER_ID);

    if (customers.Count <= 0)
        Assert.Warning("You are not testing what you want to do.");

    foreach (Customer customer in customers)
    {
        Assert.GreaterThan(customer.CustomerId, CUSTOMER_ID);
    }

    Assert.AreEqual(EXPECTED_COUNT, customers.Count);
}

Result: This will pass as there are 4 records whose CustomerId is greater than 1.

Now, we run unit test for AddCustomer()

[Test]
public void CanAddCustomer()
{
    Customer customer = new Customer();
    customer.FirstName = "Atul";
    customer.LastName = "Joshi";

    //Customer customer = new Customer() { FirstName = "Atul", LastName = "Joshi" };

    int newCustomerId = provider.AddCustomer(customer);

    Customer testCustomer = provider.GetCustomerById(newCustomerId);

    Assert.IsNotNull(testCustomer);
}

Result: This will also pass as we are adding customer and getting it back with CustomerId.

Till this stage everything is fine.

But, now if we re-run unit test for GetCustomersByIdGreaterThan(), what will happen? Any guess?????

It will fail. As we are expecting 4 customers having CustomerId > 1, but now we will get 5.

Some, immediate fixes/solution to this scenario are:

1. While writing unit test for AddCustomer() function, do following steps:
   
   • Add customer.
   • Get it by new CustomerId.
   • Perform checks/asserts.
   • Delete this newly added customer manually. ----------- (This is an extra step you need to do.)
2. Execute AddCustomer() unit test first and then execute unit test for GetCustomersByIdGreaterThan() assuming there will be one more new customer.

Drawbacks of this approach:

1. You need to write some extra code like you need to write code to delete customer in AddCustomer() unit test. Actually, this unit test case should not be responsible for doing this (writing code to delete customer).
2. Sometimes you need to reorder your unit tests.
3. Sometimes you need to write unit tests assuming some things. (Like after adding customer, there will 5 customers whose Ids are greater than 1).

Conclusion: You are not always sure your unit tests will pass successfully.

How to tackle such situations, is there any comprehensive way to avoid such circumstances? Answer: YES.

What the article/code snippet does, why it's useful, the problem it solves etc.

Solution

Before going into details of solution, lets look at the basic component which forms the base of solution. It's NDbUnit Framework.

NDbUnit:

"NDbUnit is a .NET library for putting a database into a known state. NDbUnit may be used to increase repeatability in unit tests that interact with a database by ensuring that the database's state is consistent across execution of tests. NDbUnit does this by allowing the unit test to perform an operation on a dataset before or after the execution of a test, thus ensuring a consistent state."

For details about NDbUnit look at the following links:

http://www.ndbunit.org/ - Link for main site.

http://www.ndbunit.org/NDbUnitInActionTutorial/NDbUnitInActionTutorial_viewlet_swf.html - Tutorial movie.

http://www.ndbunit.org/ndoc/NDbUnit.Core.html - NDBUnit API documentation list.

Some basic things that are related to our solution are:

• NDbUnit expects the 'TestData' folder in your unit test solution.
• Inside 'TestData' folder it expects two files:
  
  • Database.xsd
    
    • Purpose: It contains schema for database tables for which you want to restore state.
  • TestData.xml
    
    • Purpose: It contains data which you want to store in table.

This structure in solution will look like:

Utility class

Now, next step is creating a Utility class which will do functionality of restoring database state using NDbUnit.

Lets look at some important class components briefly:

Properties:

BackupDataFilename: XML file path which contains backup data for database.

BackupSchemaFilename: XSD file path which contains table schema for database tables.

DatabaseConenctionString: Database connection string.

TestDataFilename: XML file contains data which you want to store in table after each unit test execution.

TestSchemaFilename: It contains schema for database tables for which you want to restore state.

Methods:

DatabaseFixtureSetup(): This method takes backup for database data and creates BackupData.xml file in 'TestData' folder.

DatabaseFixtureTearDown(): This method restores data from BackupData.xml file to database tables.

DatabaseSetUp(): This method loads data from TestData.xml file to database tables.

SaveTestDatabase(): This is helper method provided to create TestData.xml file.

Rest of the methods are helper methods.

Most of the methods and properties are virtual so that one can override them to provide their own implementation.

Using the code

Simple enough. Inherit unit test class with DatabaseUnitTestBase and add following method to class:

/// <summary>
/// Inherit unit test class with unit test utility class.
/// </summary>
[TestFixture]
public class HibernateDataProviderTest : UnitTest.DatabaseUnitTestBase
{
    DataAccessLayer.HibernateDataProvider provider = null;

    /// <summary>
    /// This method called once before unit test execution starts.
    /// It is responsible for creating backup (BackupData.xml) file for database.
    /// </summary>
    [TestFixtureSetUp]
    public void TextFixtureSetup()
    {
        DatabaseFixtureSetup(); // This method from utility class created database backup file.
        provider = new DataAccessLayer.HibernateDataProvider();
    }

    /// <summary>
    /// This method called once after unit test execution completes.
    /// It is responsible for restoring backup (BackupData.xml) file to database.
    /// </summary>
    [TestFixtureTearDown]
    public void TestFixtureTearDown()
    {
        DatabaseFixtureTearDown(); // This method from utility class restores database backup.
    }

    /// <summary>
    /// This method called before execution of each unit test.
    /// It is responsible for restoring test data (TestData.xml) file to database.
    /// </summary>
    [SetUp]
    public void Setup()
    {
        DatabaseSetUp(); // This method from utility class restores test data for unit test execution.
    }

    [TearDown]
    public void TearDown()
    {
        DatabaseTearDown();
    }

Following these methods add your unit tests.

So, now

• Database data backup will be created before unit test execution starts.
• Test data for Unit test will be loaded before execution of each unit test. So, each unit test will have same database state for execution.
• Database data will be restored from BackupData.xml file after execution of unit tests.

Now, lets revisit our problem of failing GetCustomersByIdGreaterThan() unit test:

State of Customer table before execution of AddCustomer() unit test:

Debug code for AddCustomer() test case, to check intermediate state of database:

Highlighted rows shows newly added customer. So this unit test will pass successfully.

After execution of unit test, state of Customer table is:

So, now if we execute GetCustomersByIdGreaterThan() unit test, it will also pass as database state is restored successfully!! Bingo!!

This magic is happening because of NDbUnit, utility class and [TestFixture], [TestTearDown], [Setup], [TearDown] methods from Unit test.

Now, you can write unit tests for which they are intended to be!

Download code for Utility Class from here.

Download code for sample solution from here.

Happy Programming!!