System.Transactions LTM - promoting to DTC

In an earlier post I wrote about how the ADO.NET 2.0 System.Transactions LTM mechanism for SQL Server 2005 gets promoted from LTM into a full DTC transaction when more than one connections are opened against the same SS2K5 database (the resource). This is bound to happen when using TableAdapaters, as they open and close their own SQL connections using the configured common connection string.

One possible solution is to open a single, common SQL connection and pass it around to all methods in the biz-logic and data access logic (a data session object) to ensure that all SQL and TableAdapters use a single connection to stay within the LTM limitation (i.e. avoid getting DTC). The drawback of this is that it is contrary to "aquire late, release early" use of SQL connections, and also introduces explicit code to open/close a connection. Thus, it re-introduces the negative aspects of explicit transactions, making it hard to reuse the components e.g. in higher level processes and services.

An alternative, simpler solution is available when you use the TableAdapter only for reading data: use the TransactionScopeOption.Suppress option when setting up the ambient transaction context of your method. This ensures that the current LTM transaction will not be affected by the TableAdapter read/fill/get operation. It does not matter that the TableAdapater opens a second SQL connection automatically, promoting to DTC will not happen as the code is using two different transaction contexts:

Using transxScope As TransactionScope = New TransactionScope(TransactionScopeOption.Suppress)

'code that utilize TableAdapter for reading data here

transxScope.Complete()
End Using

You must review your usage of TableAdapaters and ensure that they can be used outside transactions. If you use them for updating the database, or if you depend on reads to be inside a transaction (e.g. for row/table locking purposes, transaction isolation level), then you are out of luck with LTM. These scenarios requires the use of DTC or, when you require LTM, the passing-an-open-connection-around solution. The latter can be caused by DTC not being allowed by the customer's operations department.