DROP DATABASE statement cannot be used inside a user transaction

5 votes
2 answers
6315 views
sql-server c# testing sql-server-localdb unit-test
                          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()
    {
    	public static List Items;
    	
    	public override Seed(DbContext context)
    	{
    		Items = new List();
    		
    		// 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();
    	
    	var result = controller.AddItem(new Item({Name = "Test"}))
    	
    	var goodResult = result as OkNegotiatedResult();
    	
    	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?
                        
Asked by S. Peter (185 rep)
Apr 5, 2016, 05:22 PM
Last activity: Jan 1, 2023, 09:45 AM
DROP DATABASE statement cannot be used inside a user transaction

Related Questions
