Categories: Dotnet/.NET - C#JavaProgramming

Wrap Your Unit Tests in Transactions

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.

Chinh Do

I occasionally blog about programming (.NET, Node.js, Java, PowerShell, React, Angular, JavaScript, etc), gadgets, etc. Follow me on Twitter for tips on those same topics. You can also find me on GitHub. See About for more info.

Next Learning Java from a .NET Developer's Perspective »

Previous « My Silverlight 2.0 Hello World Application

View Comments

Gary Woodfine says:

November 8, 2011 at 10:19 am

This appraoch makes your tests a little clunky, and you are adding more logic to your tests than required. Checking that CRUDs work is a part of the Database Unit tests.

Strictly speaking your appraoch above is not for Unit Tests, but can be more closely attributed to Integration Tests.
Rob Wilson says:

April 16, 2012 at 7:49 pm

Love this idea. So simple, yet very effective.

I have to slightly disagree with Gary. How would you then run an automated unit test without having to manually update your test data each time? For example, say your unit test inserts "Johh Doe" with a Client_Insert proc. Let's say your unit test then runs a read to make sure the insert was successful, and you have to test if "John Doe" now exists. No matter what specifically you test, you would have to update the test manually before you can run it again.

Additionally, you can still test your CRUD operations AND use transactions. Why not run whatever you are testing (say, an insert), and then run a read operation to verify, inside the same transaction scope? That way, you can fully test your CRUD operations, while still having the flexibility to roll it back when the test is complete.

I personally like to separate my Database Unit Tests from Data Access Unit Tests.. meaning that I won't test my CRUD operations in the same unit test that is testing something like a DAO.

Great article Chinh Do!