Manage Test Data: Can you enlist all db connections into a single transaction?

Question

Hi all,

We are currently using Watin to do UI testing on our web application. In effect we are doing integration testing from top to bottom since we are using a test database and not mocking.

In order to make sure the test database is in an expected state we have previously been using SQL Server's snapshot feature to rollback the database at the beginning of each test. This is fairly slow and also causes an error immediately after the snapshot is restored.

Since each the tests are invoking the UI and potentially using multiple db connections, we have no way of start a transaction on each connection.

I was wondering if it is possible to somehow attach all database connections to a single transaction and roll them back at a later point? This would probably have to happen at the db level itself.

If anyone has any other suggestions on how to reset our test data for each UI test I'd love to hear your ideas.

Answer 1

If you fire up, in process, an instance of the Visual Studio development web server, and then run your WatiN test, then you can wrap the test in a single block like so:

using (new TransactionScope())
{
    var server = new Server(PORT_NUMBER, VIRTUAL_PATH, PHYSICAL_PATH);
    server.Start();
    try
    {
        using (var ie = new IE())
        {
            // TODO: perform necessary testing using ie object
        }
    }
    finally
    {
        server.Stop();
    }
}

and all your database connections will in theory enlist in a single distributed transaction and their changes will all be rolled back when the TransactionScope is disposed.

To run the dev web server in process, you will need to extract WebDev.WebHost.dll from the GAC and reference it in your project - this is the source of the Server class in the snippet above. Please let me know if you need more detailed instructions.

You'll need to make sure MSDTC is running, and if there are firewalls between you and the databases then depending on the port settings you may struggle. One added bonus of firing up the server in process is that WatiN tests can now contribute to measurements of code coverage.