2

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.

skaffman
  • 398,947
  • 96
  • 818
  • 769
Shantanu Gupta
  • 20,688
  • 54
  • 182
  • 286

2 Answers2

8

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.
pjvds
  • 938
  • 6
  • 11
  • I appreciate your resopnse. "Vague when escalation happens." I am not clear with what escalations can occur. I am very new to TransactionScope. It seems easy to me on the cost of performance when using all transactions on single database. But what escalations can occur in TransactionScope. – Shantanu Gupta Nov 07 '11 at 14:30
  • 3
    @ShantanuGupta TransactionScope can escalate the transaction to MSTDSC, a distributed transaction service that is used when multiple parties join a transaction. For example, multiple databases. So when query multiple database within a single transaction scope, the transaction manager will escalate the transaction to MSDTSC. Most information about escalation (and the trouble) can be read here: http://stackoverflow.com/questions/1690892/transactionscope-automatically-escalating-to-msdtc-on-some-machines – pjvds Nov 07 '11 at 14:47
  • 2
    An important note is that multiple connections to the same database also escalate the transaction. – Phillippe Santana May 08 '13 at 12:13
5

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.

Tom Winter
  • 1,813
  • 3
  • 18
  • 23