Trying to work out whether TransactionScope would be useful to us.

Normally, our DataAccessAdapters are wrapped with a Using statement. If I understand correctly then DataAccessAdapter would recognize whether if is in a TransactionScope and talk to it instead of creating its own database transaction.

What I'm not clear about is how connections work in this case and if/how connections are shared.

Ideally, we want each method on a server-side service object to be able to work without having to pass adapters around. However if one method calls another and both have created their own DataAccessAdapter then two connections are made and the DTC kicks in. This knackers us completely since our workstations/servers don't have the correct ports open so this is a no-go.

We are playing with a solution whereby we wrap our code like using(new Session()) { /// }

Session has a DataAccessAdapter property which lazy-instantiates an instance and stores it in ThreadLocalStorage. This way, within any Session using block, we will always get the same Adapter.

Personally I don't like this and would rather split the methods into two - one method (defined on the service interface) that creates an Adapter and passes it to another internal method and to actually do the work using the passed in adapter. The internal method can then be called from one or more public methods.

What do other folks do on the server service side?

Cheers Simon

Normally I'd pass the adapter.

Connections aren't shared. Every adapter creates its own connection, which are then sharing the same transactionscope's transaction. So you shouldn't worry about connections: those are opened/closed very quickly as they come from the pool. The transactionscope governs the transaction, so you start a scope, create a couple of adapters here and there and when they're done, you commit the scope.

Otis wrote:

Connections aren't shared. Every adapter creates its own connection, which are then sharing the same transactionscope's transaction. So you shouldn't worry about connections: those are opened/closed very quickly as they come from the pool. The transactionscope governs the transaction, so you start a scope, create a couple of adapters here and there and when they're done, you commit the scope.

Yes but that our problem - as soon as two connections are created within a TransactionScope (even with exactly the same connection string), some DTC kicks in to coordinate everything.

This I am told (I don't really have any knowlege here) is a problem as we don't have the luxury of being able to open firewall ports etc.

If anyone knows of a way to open two connections within a TransactionScope and not have them need any external components or configuration, I would love to know.

Cheers Simon

Would you please try using one Adapter instance (and set KeepConnectionOpen = true in the CTor)?

Indeed, 2 connections will bump the transaction into DTC mode. So if this is a problem, you can't use transactionscope, as it's not really useful in that case. You then indeed need to pass an adapter around.

I have been trying to avoid transaction scope from day one. Unfortunately, Windows WorkFlow foundation requires this. Also, we are using the ASP.NET membership provider, but have decided that mashing eveything in the profile into a blob was poor design (especially for reporting). So we are using the Table Provider. So our membership calls are half membership calls and half LLBLGen entities. Guess what I have to use to make sure that everything gets written up to the DB correctly.... Transaction Scope.

We bit the bullet and are using it.. It worked fine until we actually put the app into a production evnironment and had to get the app server to talk to the db server via DTC. But that is a completely different story...

good luck,