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
}