Creating a test DB context in tests using xUnit

In cases where your application has a non-trivial data scheme (people, products, orders, prices, volumes, states, depending on a bunch of parameters, etc.), it can be easier to have some data dump recreated on a test environment, or taken from production, and use it for tests. In this case, several data dumps may be needed, for each of the cases that automatic tests should be able to roll and roll back to the test environment. In this article I will try to show how this can be done using fixtures and collections of the xUnit framework. The whole solution is based on xUnit version 2.0 dated March 16, 2015 .

Test execution scenario in context

The simplest scenario for data-driven tests might look like this:

1. create a database for a collection of tests
2. update the database to the latest version (optional)
3. run automated tests
4. delete db

Now I do not want to dwell on cases when it is necessary to update the database, because These technical details are not important for this article. But I would like to note that ADO.NET does not allow you to execute scripts that contain GO. If you want to automatically run scripts, build your system so that you can roll each script individually. Even the SQL Server Management Objects (SMO) library breaks GO scripts and runs the pieces individually (verified). Thus, the second paragraph will not be considered in the article. XUnit will help us with the rest. For a description of the general concept of xUnit contexts, see their documentation .

Fixture is a class created before running tests from a single suite. I call a suite a class with tests so that tautalogies are not received. Collection is a class that describes a group of suites, but is never created during test execution. It is for description only. This will be shown in the examples below.

Lifecycle Fixtures and Collections

You can find examples on GitHub . Starting with xUnit 2.0, the authors replaced IUseFixture with ICollectionFixture and IClassFixture.

To demonstrate how xUnit instantiates classes, I created three suites. Two of them must be run in the same context.

    public class CollectionFixture : IDisposable
    {
        public CollectionFixture()
        public void Dispose()
    }
    public class ClassFixture : IDisposable
    {
        public ClassFixture()
        public void Dispose()
    }
    [CollectionDefinition("ContextOne")]
    public class TestCollection : ICollectionFixture
    {
        public TestCollection() // TestCollection is never instantiated
    }
    [Collection("ContextOne")]
    public class TestContainerOne : IClassFixture, IDisposable
    {
        public TestContainerOne()
        [Fact]
        public void TestOne()
        [Fact]
        public void TestTwo()
        public void Dispose()
    }
    [Collection("ContextOne")]
    public class TestContainerTwo : IDisposable
    {
        public TestContainerTwo()
        [Fact]
        public void TestOne()
        public void Dispose()
    }
    public class TestContainerThree
    {
        [Fact]
        public void TestOne()
    }

To see the lines in the output window as below, the tests must be run in debug mode. I want to note one point about the papallellism of running tests in xUnit. By default, tests are executed synchronously within the same suite, or collection, if any. In other cases, tests are performed in parallel. You can read more about this here . Therefore, in reality, the output may differ slightly on your computer, but I sorted it for greater indications.

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 can see, to group several tests in one context, you can use ICollectionFixture. At the same time, IClassFixture can adjust the environment settings for a particular suite. And it is important to note that the suite constructor is called for each individual test, no matter how many there are. In Dispose it is reasonable to have the cleaning code of the corresponding scope (test, suite or collection).

Implementation details

Now it should be obvious that you can create a class that executes the script described above and attach it to the tests using ICollectionFixture or IClassFixture, depending on the specific tasks. In my example, I used a collection that restores the database before tests, and in Dispose () drops it.

It is worth noting about the following problems of this approach:

• When restoring a database from backup, internal information about files is used. In the case of parallel execution of tests, this can lead to their fall due to a name conflict in the restored databases. To solve the problem, you need to restore the database with moving files. This is in the example on GitHub.
• In general, a database can have a different number of files (data, log, file stream, etc.). This situation must be handled correctly. But for the purposes of this article, I assume that only Data and Log are needed. The remaining files are ignored using partial recovery ( PARTIAL ).

Below are T-SQL examples for restoring and deleting a database.

IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '')
BEGIN
    DECLARE @Table TABLE
    (
      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)
    )
    INSERT INTO @Table EXEC ( 'RESTORE FILELISTONLY FROM DISK = ''''')
    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')
    EXEC ('RESTORE DATABASE [] FROM DISK = ''''
            WITH
            MOVE '''+@LogicalNameData+''' TO ''\_Data.mdf'', 
            MOVE '''+@LogicalNameLog+''' TO ''\_Log.ldf'', 
            REPLACE, PARTIAL'
        )
END

ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [{0}]

A few comments for example on GitHub. To make fixture with the database context universal, you need to pass the connection string and the path to the backup file to the constructor. You can use the SqlConnectionStringBuilder class to determine the database name in the connection string for other scripts, as creation and deletion scripts should be executed in the context of the [master] database. If you need to delete the database after a certain set of tests, do it forcibly by calling Dispose (). It will, of course, be called by xUnit itself, but it is non-deterministic and, perhaps, a little earlier your tests will fail because of a database conflict.