Chinh Do

I have found that the key to writing good unit tests that interact with the database is making sure that the data is in a known state before and after the test. System.Transactions makes this very easy. All you have to do is wrap your unit test inside a TransactionScope and rollback at the end.

For example, in the test below, I am testing the update feature of my Customer data access class. To make sure I have a customer to update, I create it myself at the start of test, then I update it. At the end, scope.dispose() rolls back the whole thing. The database should look exactly like it was before my test.

[TestMethod]

public void CanUpdateCustomer()

{

    using (TransactionScope scope = new TransactionScope) {

        Customer customer = CreateTestCustomer();

        customerDac.saveCustomer(customer);

        customer.Phone1 = "804-555-1212";

        customerDac.updateCustomer(customer);

        // TODO: verify that update works

        scope.Dispose(); // roll back

    }

}

Here are a few more tips I have for unit tests that interact with the database:

• Maintain scripts to create/rebuild a starting-point database. The starting-point database may contain some data such as lookup data, codes, etc. If your unit tests work correctly, the database will stay the same across tests. With the rebuild scripts, you can easily rebuild in the case of other mishaps or a bug in one of the unit tests causing corrupt data.
• If you need to create a database record that doesn’t have an auto-generated key, use a random key to minimize the chance that your test will collide with another test running somewhere else. Seed your random generator with Environment.TickCount or a hash code of the machine name or user name.
• With my Transactional File Manager, you can also restore the state of the file system in addition to the database. Transactional File Manager is great at automatically cleaning up any temporary files you create in your unit tests.
• Don’t assume your code is the only thing that is touching the database. If the count of records in a table is x, it won’t necessarily be x + 1 after you add a new record to the table. Somebody else may have added to or deleted from the table while your test is running.

For Java programmers, try using Spring TestContext Framework. It’s a bit more complicated to use but it works just as well.