Unfortunately the answer isn't as simple as your question is.
Transactions implemented in the database are a lot simpler because the
database maintains transactions via log mechanisms. It can offer you
fantastic control over isolation levels - which in turn give you a much more
responsive application.
In contrast, transactions on non-database entities are controlled by a
transaction co-ordinator - MSDTC if in case you are using ObjectContext in
classic ASP. This will invariably bump up your isolation levels in most
instances, and hence lead to a practically unusable production application.
An easy way to create a good implementation is using .NET 2.0/SQL Server
2005/System.Transactions - but apparently you are too far from that
architecture.
Given all of these, I would keep most of your transactions in the DAL,
rather than BL. It sounds like a good idea to make BL operations
transactional, but that requirement is best addressed by proper design of
the BL, not expecting MSDTC/MTS/COM+/EnterpriseServices to take care of this
for you.
- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
