I have used the 'transcation context' aspect of component services, such as [Transaction(TransactionOption.Required)] in .NET and MTSTransactionMode in VB6, in a lot of components I have implemented since the first release of MTS/COM+. I just love having declarative transactions through the context, as this gives maximum flexibility in the ways components can be instanciated, mixed and used. EnterpriseServices does, however, incure some performance overhead due to e.g. using the DTC (Microsoft Distributed Transaction Manager).
ADO.NET 2.0 provides a promising new mechanism that is similar to the COM+ transaction context, through the System.Transactions namespace (MSDN Mag intro article). System.Transactions provides a new Lightweight Transaction Manager (LTM) that in combination with SQL Server 2005 is capable of providing transaction context through the TransactionScope class, without the overhead of DTC. The LTM is the starting point of a SS2K5 transaction, and it can be promoted into using DTC when other resource managers are involved in a transaction.
I was very disappointed when I implemented my first nested TransactionScope and ran my unit test on the biz logic method. The code gave this exception:
"Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool."
This was caused by code that involved two TableAdapters within a single transaction. Each TableAdapter contains its own SqlConnection that it opens and closes when appropriate. All connections are identical and use the same SS2K5 database (resource manager). Still, LTM decides that due to two connections being opened, a full DTC transaction is needed. Microsoft has confirmed this "limitation", which I say is rather a design error in System.Transactions. It is afterall the same resource (database) within the same resource manager (SS2K5). Quote MSDN: "The LTM is used to manage a transaction inside a single app domain that involves at most a single durable resource."
What is the point of using System.Transactions instead of System.EnterpriseServices when even the simplest real life scenario with multiple TableAdapters causes DTC to be required ? This is required e.g. when updating an order and its order lines (one DataSet, two DataTables, two TableAdapers). Microsoft should really start providing samples that go beyond single connection, single class, single component, single assembly applications.
I recognize that System.Transactions is a lightweight framework as opposed to System.EnterpriseServices, but it should not cause "bad" component interface design such as passing open SqlConnection objects around as parameters to avoid DTC for a single resource.
[UPDATE] If you need to stay LTM, you should consider using the DbConnectionScope provided by the ADO.NET team. Note that this will use a single connection for the duration of the scope; staying LTM, but also keeping the connection resource open longer - which counters connection pooling advantages.
[UPDATE] Read about the LTM improvement in .NET 3.5 and SQL Server 2008 and some less known System.Transactions gotchas here.