Transaction Management - BL to DAL

[ferguslogic]

I am looking for an answer for what seems to be a very common proble
but I can't seem to find any answers for it

I am developing a simple C# 2.0 application. It is a winform app whic
is ran on small networks. The app is divided in typical fashion int
3 logical layers UI, business and data. Pretty straight forward

In C# 2.0 I can't seem to locate a good way to handle transactio
management between layers

Here is an example

User needs to save an order

Business Tier Code example #

public int SaveOrder(Order myOrder

//verify the order meets our business requirements prior t
savin
Validate(myOrder

// make 2 calls to the Data Access Laye
int orderId = DALOrder.SaveOrder(myOrder
DALOrderItems.SaveOrderItems(myOrder)
return orderId


Each Data Access method above is located in seperate class modules an
each method opens and closes its own connection to the database

Because the business tier is calling mutliple Data Access methods i
the data tier, I need to create a transaction in the business tie
and pass it to the data tier methods so that they can all be part o
the same transaction

Today I am handling this like so

Business Tier Code example #

public int SaveOrder(

SqlConnection myConnection = DataHelper.GetConnection()
SqlTransaction transaction = myConnection.BeginTransaction()

try(


// makes 2 calls to the Data Access Layer, pass th
transactio
DAL.SaveOrder(order,transaction
DAL.SaveOrderItems(order,transaction)
transaction.Commit()

catch(System.Exception

transaction.RollBack()



In order for this to work my data access layer methods now have t
accept a transaction parameter, look at the transaction paramete
being passed and utilize the connection property of that transactio
object (transaction.Connection) to connect to the database instead o
opening and closing their own connections.

So if a transaction is not passed, the methods open and close thei
own connection, otherwise they use Transaction.Connection and do no
close a connection at all

This works okay, but it is kind of messy

I am looking for an easier way to handle transactions
One that does not require me to pass a sqlTransaction object as
parameter to every single Data Access Layer Save, Update or delet
function

For example, what happens now if the above SaveOrder() business tie
method needs to be called by the SaveCustomer() method and th
CustomerSave method needs to be the transaction root. The code i
SaveOrder() today is hardcoded to start a transaction each time it i
fired and that won't work anymore so now what

I am looking for advice on how to handle transactions betwee
business and data tier layers of an application. These layers toda
are logical only and all reside on the same server but could in th
future reside on different physical servers and could actually becom
physical tiers...who knows

Microsofts examples only seem to show you how to manage transaction
in the same layer or how to handle multiple connections to multipl
databases (Distributed transactions). I have multiple connections t
the same database and I need to share a transaction across thes
connections

I seem to be stuck. Any help or advice is appreciated

thank you in advanc

 

[David Browne]

<DIV>&quot;ferguslogic&quot;
&lt;[email protected]&gt; wrote in message

but I can't seem to find any answers for it.

I am developing a simple C# 2.0 application. It is a winform app which
is ran on small networks. The app is divided in typical fashion into
3 logical layers UI, business and data. Pretty straight forward.

In C# 2.0 I can't seem to locate a good way to handle transaction
management between layers.

Here is an example:

User needs to save an order.

Business Tier Code example #1

public int SaveOrder(Order myOrder)
{
//verify the order meets our business requirements prior to
saving
Validate(myOrder)

// make 2 calls to the Data Access Layer
int orderId = DALOrder.SaveOrder(myOrder)
DALOrderItems.SaveOrderItems(myOrder);
return orderId;
}

Each Data Access method above is located in seperate class modules and
each method opens and closes its own connection to the database.

Because the business tier is calling mutliple Data Access methods in
the data tier, I need to create a transaction in the business tier
and pass it to the data tier methods so that they can all be part of
the same transaction.

Today I am handling this like so:

Business Tier Code example #2

public int SaveOrder()
{
SqlConnection myConnection = DataHelper.GetConnection();
SqlTransaction transaction = myConnection.BeginTransaction();

try()
{

// makes 2 calls to the Data Access Layer, pass the
transaction
DAL.SaveOrder(order,transaction)
DAL.SaveOrderItems(order,transaction);
transaction.Commit();
}
catch(System.Exception)
{
transaction.RollBack();
}
}

In order for this to work my data access layer methods now have to
accept a transaction parameter, look at the transaction parameter
being passed and utilize the connection property of that transaction
object (transaction.Connection) to connect to the database instead of
opening and closing their own connections.

So if a transaction is not passed, the methods open and close their
own connection, otherwise they use Transaction.Connection and do not
close a connection at all.

This works okay, but it is kind of messy.

I am looking for an easier way to handle transactions.
One that does not require me to pass a sqlTransaction object as a
parameter to every single Data Access Layer Save, Update or delete
function.

For example, what happens now if the above SaveOrder() business tier
method needs to be called by the SaveCustomer() method and the
CustomerSave method needs to be the transaction root. The code in
SaveOrder() today is hardcoded to start a transaction each time it is
fired and that won't work anymore so now what?
. . ..

Check out the System.Transactions namespace. It's new in 2.0 and addresses
just this situation.
http://msdn2.microsoft.com/en-us/library/system.transactions.aspx


You can declare a TransactionScope in the Business Layer, and any
transaction-aware components in lower tiers will automatically detect the
transaction and enlist their work in it. Better yet, the transaction is
only (and silently) escelated to a DTC transaction if it needs to be. If
you only access a single SqlConnection, a local SQL Server transaction is
used.

Also check out this sample for a DbConnectionScope

http://blogs.msdn.com/dataaccess/archive/2006/02/14/532026.aspx

For how to share a single SqlConnection across all your tiers without having
to explicitly pass it around.

David

 

[David Jayaraj]

I am using the same concept which u have explained for Transaction, in BLL, but i am getting a exception "The Transaction has Aborted" on the execution reach my con.open() statement.
con is the SqlConnection Object. this object is in my DAL
please help me ...

Thank you..
(e-mail address removed)