Home .NET Creating a test DB context in tests using xUnit

Creating a test DB context in tests using xUnit

by admin

When your application has a non-trivial data scheme (people, products, orders, prices, volumes, states depending on a bunch of parameters, etc.) it may be easier to have some data dump recreated on the test environment, or taken from production, and use it for tests.In this case you may need several data dumps for each case which the automated tests should be able to 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 script in context

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

  1. Create a database for the test collection
  2. update the database to the latest version (optional)
  3. run automatic tests
  4. delete the database

Now I do not want to dwell on the cases when you need to update the database, because these technical details are not important for this article. But I would like to point out that ADO.NET doesn’t allow you to run scripts that have GO in them. If you want to automatically roll scripts, build your system so that you can roll each script individually. Even the SQL Server Management Objects (SMO) library breaks down scripts by GO and runs the pieces individually (tested).Thus, the 2nd point will not be covered in this article. The rest will be dealt with by xUnit. You can find a description of the general concept of xUnit contexts in their documentation
Fixture is a class created before running tests from a single suite. I call a suite a class with tests, to avoid tautology. Collection is a class that describes a group of suites, but is never created during test execution. It only serves to describe it. This will be shown in the examples below.

Life cycle of Fixtures and Collections

You can find examples at GitHub Since xUnit 2.0, the authors have replaced IUseFixture with ICollectionFixture and IClassFixture.
To demonstrate how xUnit creates instances of classes, I created three suites. Two of them have to 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<CollectionFixture>{public TestCollection() // TestCollection is never instantiated}[Collection("ContextOne")]public class TestContainerOne : IClassFixture<ClassFixture> , 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 like below in the output window, the tests must be run in debug mode. I want to make one point about the palliency of running tests in xUnit. By default, tests are executed synchronously within a single suite, or collection if there is one. In other cases, tests are executed in parallel. For more information about this you can read here So, the actual output may be slightly different on your computer, but I’ve sorted it out for clarity.

CollectionFixture : ctorClassFixture : ctorTestContainerOne : ctorTestContainerOne : TestOneTestContainerOne : disposedTestContainerOne : ctorTestContainerOne : TestTwoTestContainerOne : disposedClassFixture : disposedTestContainerTwo : ctorTestContainerTwo : TestOneTestContainerTwo : disposedCollectionFixture : disposedTestContainerThree : TestOne

So you can see that to group multiple tests into a single 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 place cleanup code for 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 hook it to tests using ICollectionFixture or IClassFixture, depending on the specific task. My example uses a collection that restores the database before the tests, and drops it in Dispose().
It is worth noting the following problems with this approach :

  • When restoring a database from a backup, internal file information is used. In the case of parallel execution of tests, it can lead to their fall due to the name conflict of the restored databases. To solve the problem, you need to restore the database with file relocation. It 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 other files are ignored by the partial recovery ( PARTIAL ).

Below are T-SQL examples for database recovery and deletion.

IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '<DBNAME> ')BEGINDECLARE @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 = ''<PATH_TO_BACKUP_FILE> ''')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 [<DBNAME> ] FROM DISK = ''<PATH_TO_BACKUP_FILE> ''WITHMOVE '''+@LogicalNameData+''' TO ''<PATH> \<DBNAME> _Data.mdf'', MOVE '''+@LogicalNameLog+''' TO ''<PATH> \<DBNAME> _Log.ldf'', REPLACE, PARTIAL')END


Some comments for example on GitHub. To make the database context fixture universal, you have to pass connection string and path to the backup file to the constructor. You can use SqlConnectionStringBuilder class to define database name in connection string for other scripts, because creation and deletion scripts must be executed in database context [master]. If you need to delete the database after some set of tests, do it forcibly by calling Dispose(). It will, of course, be called by xUnit itself, but it’s nondeterministic and it’s possible that your tests will collapse a little earlier due to a database conflict.

You may also like