C# - Usage of transactions in business layer (SQLServer 2005+ , Oracle) - good examples

Question

I am gonna build a service using 3-tier architecture and I am really worried about how to handle operations in a transacted way.

I know I have 2 options: IDbTransaction and TransactionScope... but I am not really decided for which one to go, although I did a lot of research.

I would go for TransactionScope but I don't want to involve DTC... plus I need support for SQLServer2005 and Oracle. (I am aware that I need to have only one connection opened at a time)

I would like to see good examples/patterns of their usage for both cases... Good links would do just fine.

Something like how a BL class and DAL class would look like... but also how transaction/connections are created and carried between them.

Edit1: I am looking for somekind of implementation of 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 still wait for somebody to show me a good example of a model with interaction between business layer and data layer using 'TransactionScope'

Thank you.

Answer 1

What I use in this case currently is multiple repositories and multiple UnitOfWork approach. Lets say that 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 do is on repository creation I give them the same UnitOfWork, like:

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

so that statements above are now:

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

All magic is beneath, dependent on what you use as data technology (either ORM like NHibernate, or maybe raw ADO.NET - and this is not recommended in most cases).

For a good example on repository pattern and UnitOfWorks, go through this tutorial, but note that in it you can't have multiple UnitOfWorks active (and few applications need that actually, so no real problem there). Also, the tutorial uses NHibernate, so if you are not familiar with ORM concept, I suggest you get into it (if your timetable allows it).

one more thing: you also have more advanced patterns here, like session per conversation and such, but this is advanced stuff in which I'm having my head wrapped in right now, if you want take a look at uNhAddIns projects (for NHibernate also)

Answer 2

I would go for TransactionScope, because it's much simpler to use, as you don't need to carry over a transaction object, or pass it to every method. It's ambient. It means, most of the time, developers can almost forget about transactions, write cool business-oriented methods, and then, later, add transaction wrappers (using 'using') where it's really needed, afterwards. (sounds idyllic I know, but it's almost that).

Contrary to popular belief, using a TransactionScope does not means MSDTC will be involved, see here for a recap on this:

Avoid unwanted Escalation to Distributed Transactions

And, if you really need to have a distributed transaction, well, how do you plan to do it without MSDTC anyway? What's interesting with TransactionScope again, is it will escalate to MSDTC if needed, without changing nothing in your code.