Strategies to Avoid Transaction Escalation in System.Transactions

Question

So, based on the answer to my previous question, transactions do get elevated from the LTM to the DTC if multiple connections are opened during a transaction, even if the connections all have the same connection string.

So, my next question is, what strategies could one employ to avoid this "feature?" It seems to me that, based on resource usage, I want to make sure the LTM is used as much as possible. The only way I can think of doing that, in a properly object-oriented business logic layer, is to make a request-level static connection object at the data access layer and share that amongst calls until the request is complete (the implied knowledge here is that the business object entities are discreet and don't know what order they'd get called in, additionally is the fact that one would not want to bubble a connection object up to the business object layer as that would be data storage implementation details bleeding into another layer).

Does anyone else have any ideas that don't completely ruin the layer encapsulation of an n-tier system?

Answer 1

What I've used is a TransactionHelper class update all the commands in a TableAdapter to replace the connection and transaction with those from the TableAdapter that initiates the transaction. You can find some code that does this, which you can adapt as needed, on Scott Lanford's blog, CodeExperiment. Ryan Whitaker has a similar approach.

Note that since I've moved to using LINQToSQL I no longer have this problem. You may want to consider using LINQToSQL or nHibernate as a alternate solution. Either would have good support for local transactions.