Data-driven test with xUnit

In some cases it’s very challenging to recreate all data for some use case. It much easier to run the application, create some starting point: people, products, orders, prices, amounts, etc. and use such DB during tests. Automated tests should restore such DB in a test sandbox, run tests and drop the DB. All test frameworks have possibility to run some code before and after tests. This article describes Fixtures and Collections and how they could be used to build data-driven integration tests which interact with DB. The described solution sets a data context for separate collections of automated tests, restore, upgrade and drop a test DB. The solution uses xUnit 2.0 version as of March 16, 2015.

Test Scenario for

The simplest scenario for data-driven tests should be as follows depending on your case:

  1. create DB for a collection of tests
  2. upgrade DB for the latest version (optional)
  3. run automatic tests
  4. drop DB

Probably you would need to drop DB if it’s already existed, or upgrade it automatically to the latest version. I’m not going to describe that now because they’re very specific cases. I’d like to mention only one thing about running DB scripts. ADO.NET does not allow execute batches with GO-sentence. You should plan and write your scripts to be executed separately one by one. Even SQL Server Management Objects (SMO) splits such batches by ‘GO’ and run them separately (I checked). So the 2nd point is skipped, and can help solve others with their concept of Fixtures and Collections. These concepts are parts of xUnit shared context which is fully described in their documentation.

Fixture is a class which is instantiated before running each logical block, where the logical block can represent one or several tests. Collection is a class which groups several tests, but not instantiated at all during execution of tests. The topic below describes their life cycle which is important for the case described in the article.

Fixture and Collection Life Cycle

You can find a sample source code at GitHub. In the xUnit 2.0 authors replaced IUseFixture with ICollectionFixture and IClassFixture.

In order to demonstrate how xUnit instantiates the classes I created three test suites (one suite = one class). Two of them should be ran in the same test context, one separately. In the sample code you will see that the collections group tests regardless to their suites and the moments when each of the constructors and Dispose methods is executed. For instance, below is the excerpt from the code.

    public class CollectionFixture : IDisposable
        public CollectionFixture()

        public void Dispose()

    public class ClassFixture : IDisposable
        public ClassFixture()

        public void Dispose()

    public class TestCollection : ICollectionFixture<CollectionFixture>
        public TestCollection() // TestCollection is never instantiated

    public class TestContainerOne : IClassFixture<ClassFixture>, IDisposable
        public TestContainerOne()

        public void TestOne()

        public void TestTwo()

        public void Dispose()

    public class TestContainerTwo : IDisposable
        public TestContainerTwo()

        public void TestOne()

        public void Dispose()

    public class TestContainerThree
        public void TestOne()

You should run the tests in debug-mode to see something in output window. Just a little comment about xUnit parallelism. By default tests are executed synchronously within the same collection or class. In other cases tests are executed in parallel. You can find more information about xUnit parallelism here. So the actual output may vary a bit on your PC, but for the demonstration purpose I adjusted it.

CollectionFixture : ctor
    ClassFixture : ctor
        TestContainerOne : ctor
            TestContainerOne : TestOne
        TestContainerOne : disposed
        TestContainerOne : ctor
            TestContainerOne : TestTwo
        TestContainerOne : disposed
    ClassFixture : disposed
    TestContainerTwo : ctor
        TestContainerTwo : TestOne
    TestContainerTwo : disposed
CollectionFixture : disposed
TestContainerThree : TestOne

Thus you may see that ICollectionFixture can be used to put several tests in the same context and set up some environment. IClassFixture can be used to setting up environment which is specific for a suite. The class constructor also can be used for that purpose too, but it’s called for each test in the suite and you should plan carefully their execution.

Implementation Details

It should be clear now that we can create a fixture-class to encapsulate DB routines and attach it to our tests by means of ICollectionFixture or IClassFixture. I use a collection for this purpose. Its constructor restores DB before tests and the Dispose() method drops the DB in the end.

I found several pitfalls while was implementing the solution with backup files:

  • When DB is restored from a backup file its embedded information about files is used. Your tests will fail if they use the same backup in parallel because of the conflict with names. The moving data and log files prevents such conflicts.
  • Generally backups can use different number of files (data, log, file stream, etc). The script should correctly restore such backups. I assume in the script that data and log exists always. Other files can be ignored using PARTIAL keyword.

Below you can find the scripts which can be run in constructor and Dispose().

IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '<DBNAME>')
      LogicalName VARCHAR(128) ,
      [PhysicalName] VARCHAR(128) ,
      [Type] VARCHAR ,
      [FileGroupName] VARCHAR(128) ,
      [Size] VARCHAR(128) ,
      [MaxSize] VARCHAR(128) ,
      [FileId] VARCHAR(128) ,
      [CreateLSN] VARCHAR(128) ,
      [DropLSN] VARCHAR(128) ,
      [UniqueId] VARCHAR(128) ,
      [ReadOnlyLSN] VARCHAR(128) ,
      [ReadWriteLSN] VARCHAR(128) ,
      [BackupSizeInBytes] VARCHAR(128) ,
      [SourceBlockSize] VARCHAR(128) ,
      [FileGroupId] VARCHAR(128) ,
      [LogGroupGUID] VARCHAR(128) ,
      [DifferentialBaseLSN] VARCHAR(128) ,
      [DifferentialBaseGUID] VARCHAR(128) ,
      [IsReadOnly] VARCHAR(128) ,
      [IsPresent] VARCHAR(128) ,
      [TDEThumbprint] VARCHAR(128)

    DECLARE @LogicalNameData varchar(128), @LogicalNameLog varchar(128)
    SET @LogicalNameData=(SELECT LogicalName FROM @Table WHERE Type='D')
    SET @LogicalNameLog=(SELECT LogicalName FROM @Table WHERE Type='L')

            MOVE '''+@LogicalNameData+''' TO ''<PATH>\<DBNAME>_Data.mdf'', 
            MOVE '''+@LogicalNameLog+''' TO ''<PATH>\<DBNAME>_Log.ldf'', 
            REPLACE, PARTIAL'

The base fixture class should be abstract and require specification of connection string and backup file only. You can use the SqlConnectionStringBuilder class to get a target DB name for the script. The scripts should be called under [master] BD context.

Also you should be aware about xUnit parallelism. If you need definitely drop DB after some tests you should call Dispose() manually.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Post

%d bloggers like this: