CREATE DATABASE statement not allowed within multi-statement transaction

Question

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 1

You can not use implicit commits around certain SQL commands. Creating and Deleting databases is an example SQL server will do an AUTOCommit

See the remarks section in the MS SQL help. http://msdn.microsoft.com/en-us/library/ms176061.aspx

and something on Auto Commit for more info... http://msdn.microsoft.com/en-us/library/ms187878%28v=sql.105%29

Answer 2

For your information, this error occurs by design and it happens whenever non-transactionable commands are issued to Microsoft SQL Server within an active transaction.

The solution is, therefore, granting that Database.CreateIfNotExists() hits the database out of any transaction scope. Remember, SQL Profiler is your friend.

You can get a roughly updated list of commands that are not allowed to run whithin transactions.

Note: In case one wonders why am I providing a list based on a Sybase's product, bear in mind that Microsoft SQL Server shares most of its basic genetic with Sybase' engine. For further reading, refer to https://en.wikipedia.org/wiki/Microsoft_SQL_Server