2010/08/16

How to prevent automatic MSDTC promotion

SQL Server 2005 (and maybe others) automatically promotes its connection to MSDTC when using TransactionScope.
To avoid this, set the connection string parameter Enlist=false to disable automatic transaction enlistment and create a custom connection provider like this:
This code is part of PainlessDAL framework available at http://code.google.com/p/painlessdal/ .

/// 
/// Manages database connections and transactions
/// 
public class DbContext : IDisposable, IEnlistmentNotification
{
 private string _connectionStringName = null;

 public IDbConnection Connection
 {
  get
  {
   return (IDbConnection)GetContextData("DbContextCONNECTION" + this._connectionStringName);
  }
  private set
  {
   if (value == null)
    ClearContextData("DbContextCONNECTION" + this._connectionStringName);
   else
    SetContextData("DbContextCONNECTION" + this._connectionStringName, value);
  }
 }
 private IDbTransaction DbTransaction
 {
  get
  {
   return (IDbTransaction)GetContextData("DbContextTRANSACTION" + this._connectionStringName);
  }
  set
  {
   if (value == null)
    ClearContextData("DbContextTRANSACTION" + this._connectionStringName);
   else
    SetContextData("DbContextTRANSACTION" + this._connectionStringName, value);
  }
 }
 private int UsageCounter
 {
  get
  {
   return (int)(GetContextData("DbContextUSAGECOUNTER" + this._connectionStringName) ?? 0);
  }
  set
  {
   Debug.Print(this._connectionStringName + " usage: " + value.ToString());
   if (value == 0)
    ClearContextData("DbContextUSAGECOUNTER" + this._connectionStringName);
   else
    SetContextData("DbContextUSAGECOUNTER" + this._connectionStringName, value);
  }
 }
 public DbContext(string connectionStringName)
 {
  this._connectionStringName = connectionStringName;
  this.UsageCounter++;
  if (this.Connection == null)
  {
   this.Connection = DbHelper.GetConnection(connectionStringName, true);
  }
  if (Transaction.Current != null && this.DbTransaction == null)
  {
   Transaction.Current.EnlistVolatile(this, EnlistmentOptions.None);
   System.Transactions.IsolationLevel tIsolation = Transaction.Current.IsolationLevel;
   System.Data.IsolationLevel dbIsolation = (System.Data.IsolationLevel)Enum.Parse(typeof(System.Data.IsolationLevel), tIsolation.ToString());
   this.DbTransaction = this.Connection.BeginTransaction(dbIsolation);
  }
 }
 ~DbContext()
 {
  Dispose();
 }
 private object GetContextData(string key)
 {
  if (HttpContext.Current != null)
   return HttpContext.Current.Items[key];
  else
   return CallContext.GetData(key);
 }
 private void SetContextData(string key, object data)
 {
  if (HttpContext.Current != null)
   HttpContext.Current.Items[key] = data;
  else
   CallContext.SetData(key, data);
 }
 private void ClearContextData(string key)
 {
  if (HttpContext.Current != null)
   HttpContext.Current.Items.Remove(key);
  else
   CallContext.FreeNamedDataSlot(key);
 }
 public void Dispose()
 {
  GC.SuppressFinalize(this);
  this.UsageCounter--;
  CloseConnection();
 }
 private void CloseConnection()
 {
  if (this.UsageCounter == 0 && this.DbTransaction == null)
  {
   if (this.Connection != null)
   {
    this.Connection.Dispose();
    this.Connection = null;
   }
  }
 }

 #region IEnlistmentNotification implementation
 public void Commit(Enlistment enlistment)
 {
  Debug.Print("Commit");
  this.DbTransaction.Commit();
  this.DbTransaction.Dispose();
  this.DbTransaction = null;
  CloseConnection();
  enlistment.Done();
 }

 public void InDoubt(Enlistment enlistment)
 {
  throw new NotImplementedException();
 }

 public void Prepare(PreparingEnlistment preparingEnlistment)
 {
  Debug.Print("Prepare");
  preparingEnlistment.Prepared();
 }

 public void Rollback(Enlistment enlistment)
 {
  Debug.Print("Rollback");
  this.DbTransaction.Rollback();
  this.DbTransaction.Dispose();
  this.DbTransaction = null;
  CloseConnection();
  enlistment.Done();
 }
 #endregion
}