CODEWITHSUNDEEP
.nettransactionstransactionscope
Shantanu Gupta
Shantanu Gupta

Reputation: 21108

What to use - SqlTransaction or TransactionScope in my architecture

We are using 3 layered architechture which contains SqlHelper -> DAL(Data access layer) -> BAL -> UI

Any class within a DAL can call another DAL same way any BAL can call another BAL or DAL of its own.

eg.

class Customer_DAL { display_CusDal(); }
class Customer_BAL { display_CusBal(); }   

class Product_DAL { display_ProDal(); }
class Product_BAL { display_ProBal(); }


display_CusDal()
{
 //call display_ProDal()
 //Do some work
}

display_CusDal function should run as a transaction which intern means any insertion made within this function should be associated with transaction object.

Since display_CusDal can call display_ProDal which might or might not be inserting data in table in another transaction, so I need to handle these in transactions.

What approach should I use.

Upvotes: 2

Views: 3439

Answers (2)

Tom Winter
Tom Winter

Reputation: 1923

I just want to add some emphasis to @pjvds reply about transactions being elevated to MSDTC. We had tried using TransactionScope and it worked great in our development environment. Our code always caused the elevation, but our development machine coincidently had MSDTC installed, turned on and configured. Then we deployed to our production servers and things didn't work. MSDTC was turned off. And some of our production servers are at client locations where we don't control the server. We didn't want to require our clients to turn on and configure MSDTC. So we took out TransactionScope and rolled our own.

Upvotes: 5

pjvds
pjvds

Reputation: 956

SqlTransactions only work with SQL and require you to add usage of them explicitly. The advantage of the TransactionScope is that more parties can join the transaction. So not only SqlTransactions, but also transactions from other types. Also a lot of code is taken away when using the transaction scope.

On the other hand, using transaction scope can introduce quite some wierd behavior. Stuff gets escalated to MSDTC in vague situations.

For your situation the transaction scope sounds like the best option. Via this way it is very easy to join multiple actions executed on the Customer_DAL and Product_DAL, or multiple actions on the same DAL, in a single transaction.

Pro's for SqlTransactions

  • Easy to understand.
  • Explicit
  • You have total control.

Con's for SqlTransaction

  • Requires more code.
  • Harder to join with more parties.
  • Harder to join with other transactions.
  • Binds your transactions to SQL (so no support when switching to other durable storage)

Pro's for TransactionScope

  • Easy to use.
  • Keeps your code simple, you don't need to worry about Customer_DAL and Product_DAL mixing.
  • Great transaction management out of the box. Including escalation to MSDTC when multiple databases are used.

Con's for TransactionScope

  • Vague when escalation happens.

Upvotes: 8

Related Questions