6

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.

Learner
  • 3,297
  • 4
  • 37
  • 62
  • 1
    What I see that you also have difficulties with is how to structure your data layer in the first place, and the best resource for this is really Fowler's Patterns of Enterprise Application Architecture book. Check it out, read it cover to cover – Denis Biondic Jun 13 '11 at 12:51
  • do you _use_ ***ADO.NET*** ? – Kiquenet Aug 10 '16 at 11:11
  • at the time of writing, yes, I used ADO.NET; now we also have EF6 – Learner Aug 10 '16 at 13:03

4 Answers4

11

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)

Denis Biondic
  • 7,943
  • 5
  • 48
  • 79
  • Thanks a lot for your recommendation. This is really good stuff. Actually this is ideal to have I would say. Unfortunately I don't think I can go for an ORM right now, but maybe 'Entity Framework' later. At the moment I will stick to ADO.NET and maybe I will try to adopt some of the stuff described in the link you sent (if I could find a way). – Learner Jun 13 '11 at 12:54
  • 2
    Well it is not really complicated and I see that you are thinking in the right directions. UnitOfWork / repositories, it is all doable with ADO.NET too, just the thing is you will spend lot of time on building really structured data layer like that, because you will not want to have simple repetitive CRUD methods. If you are going the "simpler" way of raw ADO.NET, than try to use Table Data Gateway / Table Modules patterns, with databinding to DataTables which .NET heavily supports (don't use VS designer binding, use manual binding). Both patterns are described in PoEAA Fowler's book. – Denis Biondic Jun 13 '11 at 13:00
  • Try abstracting the ADO.NET layer also, by having something like: SqlExecutor with: IsolationLevel IsolationLevel { get; set; } void StartTransaction(); void Commit(); void Rollback(); void ExecuteNonQuery(string sql); object ExecuteScalar(string sql); etc... – Denis Biondic Jun 13 '11 at 13:01
  • ""Try abstracting the ADO.NET layer also"" - this is exactly what I partially have already... and gives me a good starting point for different db servers. What I don't have flexible enough though is a mechanism of handling transactions outside of data layer. – Learner Jun 13 '11 at 13:15
  • Thanks much... Your info is very valuable and I really appreciate. I will leave the question open to see if there is anybody else to come with some new ideas. – Learner Jun 13 '11 at 13:18
  • If I go this path with raw ADO.Net (no TransactionScope) then we answer the first half of the question. I would like also somebody who has worked with TransactionScope to provide me a good example. – Learner Jun 13 '11 at 13:40
  • 1
    The problem with this approach is that the service class calling repositories hides its dependencies because it needs to instantiate all of the them (repositories) based on the active instance of the Unit of Work. – Sergey Akopov May 10 '16 at 19:25
  • Sergey Akopov, I agree completely, and additionally this pattern has other problems like not being able to inject anything into repositories etc. I have abandoned this approach long time ago, instead I do something like this https://github.com/DenisBiondic/ScopedUnitOfWork (not stable at all right now, but the pattern is there ) – Denis Biondic May 18 '16 at 15:58
4

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.

Simon Mourier
  • 132,049
  • 21
  • 248
  • 298
  • Thanks Simon. I really appreciate... but my question requires also an example of exactly how I would need to use transactionscope inside business layer. "sounds idyllic I know" ... is exactly this to me because I don't really get it how it should be done. Do you know good links at least of how to use it (Business layer, Data Layer, TransactionScope - altogether) – Learner Jun 13 '11 at 10:06
  • 1
    @Cristi - It's quite simple to use, in fact, just like the example code you added, you wrap any code (business or anything else) in a using directive. That's the whole purpose -:) What are you looking for beyond standard google returns? – Simon Mourier Jun 13 '11 at 10:30
  • 1
    I know... I feel pretty dumb... But I would like to know more like: how and where to create the SQLConnection (to be sure is just one for SqlServer2005, to not escalate). I am looking for a link or a good example of how to structure and implement the data layer (and their entities) and business layer operations (using transaction scope). Do you know a good link or something? – Learner Jun 13 '11 at 10:48
  • 1
    Do you think I should edit my question, is there something not clear of what I want? ... Strange that I don't get any answer :) – Learner Jun 13 '11 at 10:50
3

You can abstract these two technologies by implementing a thin wrapper, which you use in your business layer. Lets say something like:

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

You can then use an implementation that just opens a database transaction. If you need to involve another transactional system, you just switch the unit of work implementation to one which opens a TransactionScope rather than the simple database transaction.

Oliver Hanappi
  • 12,046
  • 7
  • 51
  • 68
2

UnitOfWork pattern is perfect for clear transaction handling (among other things). Here's a good implementation of the pattern used in some heavy duty applications:

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

Although you'd have to implement DataProvider for ORM of your choice (NH, EF,...) but it's fairly trivial.

Kostassoid
  • 1,870
  • 2
  • 20
  • 29
  • yeah, it happens. here's another one: https://github.com/Kostassoid/Anodyne/tree/master/src/main/Anodyne-Domain/DataAccess – Kostassoid Aug 10 '16 at 15:08