Sharing transaction or connection between multiple instance of the class

1
votes

Guys you must be thinking why I have to do that ... I may be wrong but the I am designing a system

It has following layers

  • Data Access Layer (Handles sql transactions)
  • Data Managers
  • Presentation Layers

Data manager call the data access layer to do the sql transaction. and particular function in Presentation layer call multiple data managers to do its job ...

if each data manager create separate instance for the Data access class and they don’t share the transaction, a function at the presentation layer is executing sql in more than one transactions and one of the transaction fails it will roll back that and will not affect the other transaction. We don’t want that ... we want to roll back all the transactions happens to complete the job of that presentation layer function.

I don’t know how to achieve that ...

Does my design is incorrect? Any suggestions ???

I am using SQL Server 2008 and C# and .Net 4.0

1
sql-servertransactionsconnectiondata-access-layersharing
You need to be using the System.Transactions namespace and then creating or enrolling the into existing transactions where you need to. Be sure to understand isolation levels, and underlying DB/MSDTC defaults. Also see stackoverflow.com/a/2273428/30225 – Preet Sangha
In my view database level transactions should be used sparingly or not at all. Your strategy sounds like it will be very susceptible to deadlocks. – Paul Keister

1 Answers

1
votes

SQL Server supports nested transactions so your data layer/manager can start a transaction first http://msdn.microsoft.com/en-us/library/ms189336(v=sql.105).aspx