5

Not really sure if this question belongs here, but I hope someone could help me out.

I've made integration tests going all the way down to the database (using mssql localDB). I want each test to run independently with it's own data - I want to reseed the database with my fake data before each test is running. I tried to implement it with transactions without success. Here is how I tried to pull it off:

public class TestDbInitializer : DropCreateAlways<MyContext>()
{
    public static List<Item> Items;

    public override Seed(DbContext context)
    {
        Items = new List<Item>();

        // Adding items
        // .. 

        Items.ForEach(x => context.Add(x));

        context.SaveChanges();
    }
}


public class BaseTransactionsTests
{
    private TransactionScope _scope

    [TestInitialize]
    public void Initialize()
    {
        _scope = new TransactionScope();
    }

    [TestCleanup]
    public void Cleanup()
    {
        _scope.Dispose();
    }
}

[TestClass]
public class IntegrationTests : BaseTransactionsTests

private IDependenciesContainer _container;

public static void AssemblyInit(TestContext context)
{
    Database.SetInitializer(new TestDbInitializer());

    _container = new DependenciesContainer();

    // Registers all my application's dependencies
    _container.RegisterAll();
}

[TestInitialize]
public void Initialize()
{
    using (var context = new MyContext("TestsDatabase"))
    {
        context.Initialize(true);
    }
}

[TestMethod]
public void TestAddItem()
{
    var controller = _container.Resolve<MyController>();

    var result = controller.AddItem(new Item({Name = "Test"}))

    var goodResult = result as OkNegotiatedResult<string>();

    if (result == null)
        Assert.Fail("Bad result")

    using (var context = new MyContext("TestsDatabase"))
    {
        Assert.AreEqual(context.Items.Count, TestDbInitializer.Items.Count + 1)
    }
}

I use my dependency injector in my tests, registering all dependencies once (AssemblyInitialize).

I created a DB instance for testings, and a specific DropCreateAlways initializer with a fake data Seed method, which I set as the initializer in the AssemblyInitialize as well.

I want to reseed the database with my fake data before each test run. For that case I implemented the base class which holds a transaction scope.

When I run my tests, the following exception is thrown when Seeding the database in the TestInitialize:

DROP DATABASE statement cannot be used inside a user transaction

How should I deal with it? Moreover, what do you think of my implementation of those integration tests? What could be improved?

2 Answers 2

6

Create a separate initializer that drops the database if it exists, then creates the database outside of any transaction handling code. If this fails for any reason, you could (probably) just fail the test right then and investigate why it failed.

From MSDN:

The CREATE DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.

If you want, you could do this for each individual test:

  1. DROP DATABASE if it exists
  2. CREATE DATABASE
  3.  Create necessary DDL
  4.  Run DML unit test
  5. DROP DATABASE

However, that would be overkill. I'd simply create the database at the start of all tests, and drop it at the end. Each unit test could be wrapped in a transaction which is either rolled-back or committed, depending on the requirements of further tests.

I looked for a resource that explains why CREATE DATABASE cannot be used inside a transaction, but I couldn't find one. Kenneth Fisher provided the following TechNet link, which shows all commands that won't work in a transaction:

Transact-SQL Statements Allowed in Transactions

Our guess is that a lot of them aren't allowed because they affect the file system, which is not transactional.

0
0

I had the same error running drop database from python, from a cursor which executed some dml before drop command, from pyodbc module. Setting sql.autocommit = True helped me. sql is the variable for connection to ms sql instance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.