I am looking for an answer for what seems to be a very common problem 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
// make 2 calls to the Data Access Layer
int orderId = DALOrder.SaveOrder(myOrder)
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 utilize this transaction across all of the data layer methods. Upon success the business tier can then commit the transaction.
Today I am handling this like so:
Business Tier Code example #2
public int SaveOrder()
SqlConnection myConnection = DataHelper.GetConnection();
SqlTransaction transaction = myConnection.BeginTransaction();
// makes 2 calls to the Data Access Layer, pass the transaction
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.
Perhaps, one that does not require me to pass a sqlTransaction object as a parameter to every single Data Access Layer Save, Update or delete method.
For example, what happens now if the above SaveOrder() business tier method needs to be called by the SaveCustomer() method and the SaveCustomer() method needs to be the transaction root. Today the SaveOrder() method is hardcoded as the root and always
begins a transaction each time it is fired so should that situation arrive, our current methodology may fail.
I am looking for someone who has faced this issue who can offer advice on how to handle transactions between business and data tier layers of an application.
These layers today are logical only and all reside on the same server.
Microsofts examples seem to be simplified samples and only seem to show you how to manage transactions in the same layer or even worse..the same exact function. They do talk a bit about how to handle multiple connections to multiple databases (Distributed transactions) but that does not apply here. I have multiple connections to the same exact database.
Any advice is greatly appreciated.
thank you in advance