Is there a way to use System.Transactions with connection pooling?

Question

I think the System.Transaction classes in Framework 2.0 are a great tool, well designed, aimed to simplify code when working with transactions.

But, (big but), it's impossible to use those classes with multiple connections to the same database (same connection string) without promoting to distributed transactions.

http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/3ce488eb-55a8-4535-adc7-c5b29a1523b5/

http://stackoverflow.com/questions/394702/multiple-connections-with-same-connection-string-under-a-single-transaction-elev

Microsoft is working to solve this, but meanwhile ¿anyone knows a reliable, simple workaround to allow connection pooling mechanism while keeping the ability to use System.Transactions? Is it possible?

Answer 1

Not sure what you are trying to achieve:

• If the connections go to different databases you need distributed transactions.
• If the connections go the same database, the opening of connections could be controlled in your code. Why are you opening many connections, could you send the connection object as a parameter and reuse it.

EDIT:

I agree with the comments below that it is best to open and close connections as required. But if a connection is part of a transaction it will not be returned to the pool, in that case you could try to reuse it.