How to NOT execute a command in ambient transaction

Topics: Data Access Application Block
Feb 2, 2007 at 10:14 AM

I'm using DAAB in a multi-base configuration (different connection strings) : the second base is only used for logging.

The problem is that in case of a TransactionScope declared around 2 differents Database objects, transaction is used for BOTH connection. Resulting in a COM+ Transaction, etc.

I've found nothing (no method) related of executing a command without enrolling in an ambient transaction. Are DbConnection and Database able to manage such cases ?
Feb 2, 2007 at 2:42 PM
In Enterprise Library 2.0, you will always get a distributed transaction when executing multiple database commands with TransactionScope because the Database Class will always use a new connection to execute commands.

In Enterprise Library 3.0 there is now support for TransactionScope. The Database Class will now check for an existence of a Transaction and try to use the same open connection. I talk about it briefly here:

TransactionScope and Enterprise Library 3.0 Data Access Application Block - System.Transactions

However, if you are using two differenct connection strings and hence two different connections within TransactionScope ( one for data access and one for logging ), I believe you will always get a distributed transaction using TransactionScope - whether you use ADO.NET or the DAAB.

The only way to avoid it ( that I know of ) is to handle the transaction yourself and just pass an IDbTransaction in the method overloads that exist now.

This may be something worth mentioning in the IssueTracker as I don't know if there is a way in ADO.NET to say I don't want a command to participate in a current transaction. I have never had the situation come up, so I need to personally dig into it some more.




David Hayden
Microsoft MVP C#
Feb 2, 2007 at 5:04 PM
I was speaking about EL 3.0, yes.

For a simple workaround, it will be sufficient to use explicit DbTransaction object instead of a TransactionScope.
So, the question should be : Should Enterprise Library expose methods to allow creation and use of standards DbTransaction objects or not ?

Facts :
- The DbConnection object is not accessible (wrapped and private)
- The Database class exposes methods which takes DbTransaction as parameters.
- There is no way to create standards DbTransaction using the Database object.
- There are already BeginTransaction(), RollbackTransaction() and CommitTransaction() as private methods used for internal use.

Possible solutions :
- Make Begin, Rollback and Commit public ?
- It's the user which should use the DbProviderFactory.CreateConnection(), then create and manage the connection by himself ?
Feb 2, 2007 at 6:31 PM
Actually, the DbConnection is accessible. You're right - it would have to be. How else would you create a DbTransaction :)

public DbConnection CreateConnection ();

is available on the Database Class.




David Hayden
Microsoft MVP C#