C # - Using transactions at the business level (SQLServer 2005+, Oracle) - good examples

Question

I'm going to create a service using a three-tier architecture, and I'm really worried about how to handle transactions in transactional mode.

I know that I have 2 options: IDbTransaction and TransactionScope ... but I did not really decide what to go for, although I did a lot of research.

I would go for TransactionScope, but I do not want to enable DTC ... plus I need support for SQLServer2005 and Oracle. (I know that I only need to have one connection open at a time)

I would like to see good examples / patterns of their use for both cases ... Good links will be very good.

Something like the BL and DAL class would look like ... but also the way transactions / connections are created and wrap between them.

Edit1: I am looking for something to implement this (but for both options):

 using(var scope = new TransactionScope()) { // transactional methods datalayer.InsertFoo(); datalayer.InsertBar(); scope.Complete(); } 

Edit2: Since Denis offered me a very good alternative ... I'm still waiting for someone to show me a good example of a model with the interaction between the business layer and the data layer using " TransactionScope "

Thanks.

Answer 1

In this case, I use several repositories and several UnitOfWork approaches. Suppose you have CustomerRepository and InvoiceRepository. If you need to do this:

 customerRepository.Add(customer); invoiceRepository.Add(bill); 

and have these two as a transaction, then what I am doing is creating a repository. I give them the same UnitOfWork as:

 IUnitOfWork uow = UnitOfWork.Start(); ICustomerRepository customerRepository = new CustomerRepository(uow); IInvoiceRepository invoiceRepository = new InvoiceRepository(uow); 

so the above statements:

 customerRepository.Add(customer); invoiceRepository.Add(bill); uow.Commit(); 

All the magic is lower, depending on what you use as data technology (ORM, for example, NHibernate or maybe raw ADO.NET), and this is not recommended in most cases).

For a good example, in the repository template and UnitOfWorks, go through this tutorial , but note that you cannot activate several UnitOfWorks modules in it (and it requires several applications, which is actually so there is no real problem there). In addition, the tutorial uses NHibernate, so if you are not familiar with the concept of ORM, I suggest you enter it (if the schedule allows it).

one more thing: you also have more complex templates, such as a conversation session, etc., but this is advanced material in which I now have my own head if you want to take a look at uNhAddIns projects (for NHibernate also)

Answer 2

I would go for TransactionScope because it is much easier to use, since you do not need to transfer the transaction object or pass it to each method. It is ambient . This means that in most cases, developers can almost forget about transactions, write cool business-oriented methods, and then add transactional wrappers (using "use"), where it is really necessary, afterwards. (it sounds idyllic, I know, but it's almost that).

Contrary to popular belief, using TransactionScope does not mean that MSDTC will be enabled, see here for a brief description:

Avoid Unwanted Escalation for Distributed Transactions

And if you really need a distributed transaction, well, how do you plan to do this without MSDTC? What's even more interesting with TransactionScope, it will migrate to MSDTC, if necessary, without changing the code.

Answer 3

You can abstract these two technologies by implementing a thin shell that you use at your business level. Let's say something like:

 using (IUnitOfWork unitOfWork = UnitOfWork.Start()) { // do something unitOfWork.Commit(); } 

Then you can use an implementation that only opens the database transaction. If you need to attract another transactional system, you simply switch the implementation of the unit of work to the one that opens the TransactionScope, and not just a database transaction.

Answer 4

UnitOfWork is ideal for clear transaction processing (among other things). Here's a good implementation of the pattern used in some heavy-load applications:

https://github.com/NikGovorov/Taijutsu/tree/master/Sources/Sources/Taijutsu-Infrastructure

Although you will have to implement a DataProvider for the ORM of your choice (NH, EF, ...), but this is pretty trivial.