How to handle transactions at Business LogicLayer level

[psycho]

I am working on an N-tier application using following components:
1. Data Access Layer using DLINQ which consists of Data Context class
and Table Mapping classes.
2. Business Logic Layer.
3. Presentation Layer (normal ASP.NET pages)
The problem is that I have to handle database transactions which can
span multiple tables. So where should I place the transaction code. I
think I should do it in BLL. But how do I control the transaction at
BLL layer level. Please suggest.

Thanks in advance.
- param

 

[Andy B]

Something that I have always tried to do is create all of the sql
transactions in the stored procedures of the database and then working with
the c#/vb code would be a lot easier. You wouldn't have to be always making
inserts to multiple tables all the time in the application code since thats
not the applications job anyways. I am just getting into the entity model
framework and it looks pretty good especially when you can write only 3
lines of vb code and insert values into about 30 different tables with 100%
success rate and pretty fast as well. I of course didn't use that in a real
life application, it was just a junk test that I created to see how much
entity framework would make my life easier or more complicated.

 

[sloan]

I don't know if there is a right or wrong answer to this....However, I will
give my opinion.

First, are you using Sql Server (or another RDBMS)?

......

Option1:
With Sql Server 2000 and 2005, you can handle multiple table
transactions inside a usp (user stored procedure). You can accomplish this
through alot of scalar values OR using XML.
With 2008, you can accomplish this using
http://channel9.msdn.com/posts/ashishjaiman/SQL-Server-2008-Table-valued-parameters/
Table Valued Parameters. I have experience with 2000/2005 and am only
getting into 2008 with this particuliar need.
You would handle the Transactions at the USP level.

Here is the most basic example of a usp transaction on 2 updates:
http://groups.google.com/group/micr...programming/msg/12a7386e84ab4654?dmode=source
THis isn't a great example, but has the nuts/bolts of the logic.
Actually, I would use the:
dbo.uspEmployeeDepartmentJobTitleUpdate
from the next URL I mention immediately below as an example.

Option2:
https://www.microsoft.com/communiti...7ae-bf53-9380538fa8a9&lang=en&cr=US&sloc=&p=1

You can put the transaction code in DotNet code. The above link shows some
examples of that I coded up. I didn't get alot of feedback on the post fyi.
But at least you have the code examples.
The example uses 3 tables, Emp, Dept, JobTitle.

Now, we have to discuss what you mean and I mean by BAL and DAL. Some
people have the BAL call "Helpers" and consider that the DAL. Example. The
BAL calling (directly) the SqlHelper class of the DAAB (circa 4-5 years
ago).

Some people (like me) have a DAL layer, and uses the SqlHelper or
EnterpriseLibrary.Data ~as helpers.....and not "instead of" the DAL.
You can see a code example of this here:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry
(Also check my 1.1 version of that same article at
http://sholliday.spaces.live.com/Blog/ or
http://sholliday.spaces.live.com/feed.rss )

If you're using a true DAL (and not simply (and errantly in my opinion)
calling a Helper class from your BAL).......then you ~can code up the
transactions in the DAL.

If I were talking to 2 different databases, I would use this approach. I
would pick on of the DAL (DotNet) syntaxes from the URL (listed underneath
of Option2 above) and use that.

If I were using a non SQL Server RDBMS like Oracle, I would use this
approach as well...since my experience with Oracle and Xml was not a found
one.

...................

So my nutshell advice:
1. If you're using Sql Server, I would try XML to pass all your data down
and use usp's (BEGIN TRAN, COMMIT TRAN, ROLLBACK TRAN). << My opinion.
2. If you're using multiple databases or something non sql
server.......then (A) Make sure you have an actual DAL layer defined (and
are NOT simply using the "Helper" classes like SqlHelper or
EnterpriseLibrary.Data)........and then put in DotNet transactions. The URL
(mentioned above under Option 2) should provide the syntax(es) for that.

................

Good luck.

 

[psycho]

I have found a way to manage transactions using TransactionScope.

using (TransactionScope scope = new TransactionScope
(TransactionScopeOption.RequiresNew))
{
User.Save();
UserGroup.Save();

scope.Complete();

}