How do you get around multiple database connections inside a TransactionScope if MSDTC is disabled?

Question

I have a web application that issues requests to 3 databases in the DAL. I'm writing some integration tests to make sure that the overall functionality round trip actually does what i expect it to do. This is completely separate from my unit tests, just fyi.

The way I was intending to write these tests were something to the effect of this

[Test]
public void WorkflowExampleTest()
{
    (using var transaction = new TransactionScope())
    {
        Presenter.ProcessWorkflow();
    }
}

The Presenter in this case has already been set up. The problem comes into play inside the ProcessWorkflow method because it calls various Repositories which in turn access different databases, and my sql server box does not have MSDTC enabled, so I get an error whenever I try to either create a new sql connection, or try to change a cached connection's database to target a different one.

For brevity the Presenter resembles something like:

public void ProcessWorkflow()
{
    LogRepository.LogSomethingInLogDatabase();
    var l_results = ProcessRepository.DoSomeWorkOnProcessDatabase();
    ResultsRepository.IssueResultstoResultsDatabase(l_results);
}

I've attempted numerous things to solve this problem.

1. Caching one active connection at all times and changing the target database
2. Caching one active connection for each target database (this was kind of useless because pooling should do this for me, but I wanted to see if I got different results)
3. Adding additional TransactionScopes inside each repository so that they have their own transactions using the TransactionScopeOption "RequiresNew"

My 3rd attempt on the list looks something like this:

public void LogSomethingInLogDatabase()
{
    using (var transaction = 
        new TransactionScope(TransactionScopeOption.RequiresNew))
    {
        //do some database work

        transaction.Complete();
    }
}

And actually the 3rd thing I tried actually got the unit tests to work, but all the transactions that completed actually HIT my database! So that was an utter failure, since the entire point is to NOT effect my database.

My question therefore is, what other options are out there to accomplish what I'm trying to do given the constraints I've laid out?

EDIT:

This is what "//do some database work" would look like

using (var l_context = new DataContext(TargetDatabaseEnum.SomeDatabase))
{
    //use a SqlCommand here
    //use a SqlDataAdapter inside the SqlCommand
    //etc.
}

and the DataContext itself looks something like this

public class DataContext : IDisposable
{
   static int References { get; set; }
   static SqlConnection Connection { get; set; }

   TargetDatabaseEnum OriginalDatabase { get; set; }

   public DataContext(TargetDatabaseEnum database)
   {
       if (Connection == null)
          Connection = new SqlConnection();

       if (Connection.Database != DatabaseInfo.GetDatabaseName(database))
       {
           OriginalDatabase = 
               DatabaseInfo.GetDatabaseEnum(Connection.Database);

           Connection.ChangeDatabase(
               DatabaseInfo.GetDatabaseName(database));
       }           

       if (Connection.State == ConnectionState.Closed)
       {
           Connection.Open() //<- ERROR HAPPENS HERE
       }    

       ConnectionReferences++;                 
   }

   public void Dispose()
   {
       if (Connection.State == ConnectionState.Open)
       {
           Connection.ChangeDatabase(
               DatabaseInfo.GetDatabaseName(OriginalDatabase));
       }

       if (Connection != null && --ConnectionReferences <= 0)
       {
           if (Connection.State == ConnectionState.Open)
               Connection.Close();
           Connection.Dispose();
       }
   }
}

Answer 1

1. Set Enlist=false on connection string to avoid auto enlistment on transaction.

2. Manually enlist connection as participants in transaction scope. (http://msdn.microsoft.com/en-us/library/ms172153%28v=VS.80%29.aspx)

Answer 2

Ok, I found a way around this issue. The only reason I'm doing it this way is because I couldn't find ANY other way to fix this problem, and because it's in my integration tests, so I'm not concerned about this having adverse effects in production code.

I had to add a property to my DataContext to act as a flag to keep track of whether or not to dispose of the connection object when my DataContext is being disposed. This way, the connection is kept alive throughout the entire transaction scope, and therefore no longer bothers DTC

Here's sample of my new Dispose:

internal static bool SupressConnectionDispose { get; set; }

public void Dispose()
{
   if (Connection.State == ConnectionState.Open)
   {
       Connection.ChangeDatabase(
           DatabaseInfo.GetDatabaseName(OriginalDatabase));
   }

   if (Connection != null 
       && --ConnectionReferences <= 0 
       && !SuppressConnectionDispose)
   {
       if (Connection.State == ConnectionState.Open)
           Connection.Close();
       Connection.Dispose();
   }
}

this allows my integration tests to take the form of:

[Test]
public void WorkflowExampleTest()
{
    (using var transaction = new TransactionScope())
    {
        DataContext.SuppressConnectionDispose = true;

        Presenter.ProcessWorkflow();
    }
}

I would not recommend utilizing this in production code, but for integration tests I think it is appropriate. Also keep in mind this only works for connections where the server is always the same, as well as the user.

I hope this helps anyone else who runs into the same problem I had.

Answer 3

If you don't want to use MSDTC you can use SQL transactions directly.

See SqlConnection.BeginTransaction().