RobVious RobVious - 27 days ago 15
C# Question

CREATE DATABASE statement not allowed within multi-statement transaction

I'm trying to build a quick test that deletes and recreates a database every time it runs. I have the following:

[TestClass]
public class PocoTest
{
private TransactionScope _transactionScope;
private ProjectDataSource _dataSource;
private Repository _repository = new Repository();
private const string _cstring = "Data Source=.;Initial Catalog=test_db;Trusted_Connection=True";

[TestInitialize]
public virtual void TestInitialize()
{
_dataSource = new ProjectDataSource(_cstring);
_dataSource.Database.Delete();
_dataSource.Database.CreateIfNotExists();
_transactionScope = new TransactionScope();
}
[TestMethod]
public void TestBasicOperations()
{
var item = _repository.AddItem(new Item(){Details = "Test Item"});
// AddItem makes a call through the data context to add a set and then calls datacontext.SaveChanges()
}


[TestCleanup]
public void TestCleanup()
{
// rollback
if (_transactionScope != null)
{
_transactionScope.Dispose();
}
}


However when I run the test I get the following error:


Result Message: Test method
Project.Repository.UnitTests.PocoTest.TestBasicOperations threw
exception: System.Data.SqlClient.SqlException: CREATE DATABASE
statement not allowed within multi-statement transaction.


ProjectDataSource is here:

public class ProjectDataSource : DbContext, IProjectDataSource
{

public ProjectDataSource() : base("DefaultConnection")
{

}

public ProjectDataSource(string connectionString) : base(connectionString)
{

}

public DbSet<Set> Sets { get; set; }
}


Repository:

public class Repository : IRepository
{
private readonly ProjectDataSource _db = new ProjectDataSource();
public Item AddItem(Item item)
{
_db.Items.Add(item);
_db.SaveChanges();
return item;
}
}


Why is this happening?

Also - if it makes any difference - the error doesn't occur if I comment out the AddItem line in TestMethod.

Answer

In case anyone else runs into this issue:

In my Repository class, I have another definition of what's commonly labeled a "dbContext" - ProjectDataSource. This means that one context was created in my test class, while another was created in my Repository object. Sending the connectionstring to my repo class solved the problem:

In Repository:

public class Repository : IRepository
    {
        private readonly ProjectDataSource _db;

        public Repository(string connectionString)
        {
            _db = new ProjectDataSource(connectionString);   
        }

        public Repository()
        {
            _db = new ProjectDataSource();   
        }

From my test:

private TransactionScope _transactionScope;
        private Repository _repository;
        private ProjectDataSource _dataSource; 
        private const string _connectionString = "Data Source=.;Initial Catalog=test_db;Trusted_Connection=True";

        [TestInitialize]
        public virtual void TestInitialize()
        {
            _repository = new Repository(_connectionString);
            _dataSource = new ProjectDataSource(_connectionString);
            _dataSource.Database.Delete();
            _dataSource.Database.CreateIfNotExists();
            _transactionScope = new TransactionScope();
        }
Comments