title: SqlConnection and avoiding promotion to MSDTC
body: <p>When we need to do database access in our application, we use the following patterns:</p>
  
  <ul>
  <li>For querying, we have a static factory class with a method <code>CreateOpenConnection</code> which does nothing more than <code>new SqlConnection(myConnectionString)</code> and calls <code>Open()</code> on it. This method gets called before we do a query and the connection is disposed after the query returns.</li>
  <li>For inserts/updates/deletes we use a Unit of Work pattern where the changes are batched up and submitted to the database with a call to <code>work.Commit()</code> like so:</li>
  </ul>
  
  <p>work.Commit:</p>
  
  <pre><code>using (var tranScope = new TransactionScope(TransactionScopeOption.RequiresNew))
  {
      using (var conn = DapperFactory.CreateOpenConnection())
      {
        var count = _changeTracker.CommitChanges(conn);
  
        tranScope.Complete();
  
        return count;
      }
  }
  </code></pre>
  
  <p>This seems to work great for general usage as part of a webservice, but is currently giving me MSDTC trouble when I try to use this in combination with Rebus. </p>
  
  <p>From what I can tell, Rebus (when it handles a message in the queue) creates a new <code>TransactionScope</code> so that in case of a failure to handle the message, stuff can be rolled back. Now, this in itself has worked fine so far. I can open a new <code>SqlConnection</code> inside a Rebus message handler without any issues (however, using our legacy Entity Framework queries <em>and</em> manual SqlConnections inside the same Rebus <code>TransactionScope</code> doesn't work, but I don't consider that an issue right now). But yesterday I asked the following question:</p>
  
  <p><a href="https://stackoverflow.com/questions/16837668/serial-processing-of-a-certain-message-type-in-rebus/16843938#16843938">Serial processing of a certain message type in Rebus</a></p>
  
  <p>To which the answer seems to be to use the saga feature of Rebus. I tried implementing this and configured it so that the Rebus saga gets persisted to a new SQL Server database (with a distinct connection string). Presumably, using that SQL Server persistence opens a <code>SqlConnection</code> of its own, because any time I try to create a <code>SqlConnection</code> now, I get the following exception:</p>
  
  <blockquote>
    <p>Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.</p>
  </blockquote>
  
  <p>Enabling MSDTC is something I would very, <em>very</em> much like to avoid doing, with regards to configuration and performance overhead. And I may be wrong, but it also just doesn't seem necessary. </p>
  
  <p>What I presume is happening here is that Rebus creates an ambient <code>TransactionScope</code> and that the <code>SqlConnection</code> it creates enlists to that scope. And when I try to create my own <code>SqlConnection</code> it also tries to enlist to that ambient scope and because multiple connections are involved, it gets promoted to MSDTC, which fails. </p>
  
  <p>I have an idea on how to fix this, but I don't know if it's the right thing to do. What I would do is:</p>
  
  <ul>
  <li>Add <code>Enlist=false</code> to my application's connection string so that it never enlists to ambient transactions.</li>
  <li>Modify the <code>Commit</code> method so that it doesn't create a new <code>TransactionScope</code> (which my connection won't subscribe to any more because I just told that it shouldn't) but that it uses <code>conn.BeginTransaction</code>.</li>
  </ul>
  
  <p>Like so:</p>
  
  <pre><code>var transaction = conn.BeginTransaction();
  
  try
  {
    var count = _changeTracker.CommitChanges(conn);
    transaction.Commit();
    return count;
  }
  catch
  {
    transaction.Rollback();
    throw;
  }
  finally
  {
    transaction.Dispose();
  }
  </code></pre>
  
  <p>I'm just not sure if this is the right approach and what the possible drawbacks are. </p>
  
  <p>Any tips?</p>
  
  <p><strong>UPDATE:</strong> To clarify, it's not the <code>work.Commit()</code> that's been giving me problems, I'm quite sure that it would work, but I never get there because my <em>querying</em> is what fails. </p>
  
  <p>An example of what fails:</p>
  
  <pre><code>public int? GetWarehouseID(int appID)
  {
    var query = @"
  select top 1 ID from OrganizationUnits o
  where TypeID &amp; 16 = 16 /* warehouse */";
  
    using (var conn = _dapper.OpenConnection())
    {
      var id = conn.Query&lt;int?&gt;(query).FirstOrDefault();
  
      return id;
    }
  }
  </code></pre>
  
  <p>This gets called when a <code>TransactionScope</code> has been created by Rebus, as well as after a <code>SqlConnection</code> is opened by Rebus. Upon opening <em>my</em> <code>SqlConnection</code>, it tries to enlist and crashes</p>