Getting rid of the DTC using TableAdapters

DTC is not good for your performance, and it's a pain in the ass to get rid of..

An interesting link is http://weblogs.asp.net/ryanw/archive/2006/03/30/441529.aspx, this solution uses Reflection to set the SqlTransaction object to the commands of your TableAdapter.

But, we wanted to use the (new in .NET 2.0) TransactionScope object instead of the SqlTransaction object to control our transactions.. If I just use the TableAdapters of my typed DataSets as such, i got the DTC running, which isn't good for your performance. So I tried some stuff and I concluded that I had to control the connections of the TableAdapters.

Instead of letting the TableAdapters create their own SqlConnection objects, I had to give them the reference to one and the same SqlConnection object for each TableAdapter.

If you look at my blogpost "Use assemblies containing typed DataSets: How to handle the connection strings?" you can see how I use a Factory class to control my TableAdapters. When invoking the methods to ask the Factory for your desired TableAdapter, you'll have to pass the ConnectionString.
Well, now I made it so that I have to pass a SqlConnection object.

Conclusion: To get rid of the DTC, you'll have to use 1 and the same SqlConnection object for all your TableAdapters AND you'll have to Open() the connection before you use a TransactionScope and you'll have to Close() the connection after the use of a TransactionScope object.

Maybe I'll post an example program, but I haven't got much time.. If you want one, please mail me, I'll do my best..