c# - 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)