How to managing transactions to external database?

Topics: General
May 4, 2011 at 4:00 AM

Hi there,

I am trying to develop a module that integrates with Orchard, the primary purpose of this module is to display client details from our internal sql database.  What i am struggling to understand is how to prevent transactional errors occurring.

I understand that Orchard is creating a new TransactionScope for the lifetime of the request and that my SqlConnection is using the ambient transaction when connecting to the database.

I found the following messages that show a workaround by creating a TransactionScope around the data access code.

Is this the only workaround?

I have data access code in many places, controllers, driver, authorization, filter providers.  Is there a common place i could create this new transaction scope, i.e ActionFilter so that it could be applied to just my module?


Cheers, Rohan

May 4, 2011 at 4:29 AM

Yes, that's the workaround and yes I think it could be done in a filter.

May 4, 2011 at 5:00 AM

Hey Bertrand, thanks for you reply

How would the filter work? A FilterProvider that implements IActionFilter and IAuthorizationFilter? Can i specify a priority on filter so that it is executed before all other filters?

In the case of accessing data in a ContentPartDriver how could i wrap that transaction in an actionfilter?

This is all a bit complicated just for the ability to connect to an additional data source.  

What are you thought on MSDTC and using it alongside Orchard?

May 4, 2011 at 5:18 AM

That should give you a good starting point:

May 4, 2011 at 8:11 AM

@rohanwest - Might I suggest that if you have data access code in many places, such as the places you listed.. you're doing something wrong.  You really shouldn't be doing data access in all those places.  They have very specific fuctions, most of which are not Data access related..

Of course I haven't seen your code, and I don't know your requirements, but data access code everywhere sounds like a code smell to me.