Sharing connection and transaction in a Data Access Layer

views:

answers:

+2 Q:

Sharing connection and transaction in a Data Access Layer

Hello, I'm building a Data Access Layer for my asp.net application. I would like to be able to share connection between different classes in order to manage transaction, but I don't know how to do that.

Example:

I have 2 classes, Order and OrderDetail.

I will call my DAL Order class for a SQL insert of a new order.

Inside the Insert method, I want to call my OrderDetail class to insert my order's details, and I would do that with same connection and transaction.

Could someone suggest me some architecture design to do that? Or maybe someone could provide some resource in internet?

I hope the example is clear, my english sucks!

Thanks.

+2 A:

I suggest you focus on sharing the transaction and leave the connection code as you have it now. Connections are pooled so opening connections should have minimal performance impact. You must use the same transaction however, otherwise your insert of orders and order details isn't an atomic operation (if your code fails halfway, you end up with an incomplete order in your database).

The best way to 'share' your transaction is by using the TransactionScope class. It creates a so-called ambient transaction. Every SqlConnection you open inside the scope of an ambient transaction automatically becomes part of this transaction.

You no longer have to use (or should use) SqlConnection.BeginTransaction if you use ambient transactions.

Ronald Wildenberg 2010-08-08 12:45:38

Thank you Ronald, TransactionScope seems to be what I need.It could be ok in the Order class, I insert the Order Header, and then I call multiple times my OrderDetail's Insert method for the details, all inside TransactionScope.But, I could also call directly the OrderDetail's Insert method, when I modify an order to add a detail, and in this case OrderDetail's Insert should have his own transaction.What about that?I hope you understand what I mean.

opaera 2010-08-08 12:56:16

I understand your point but you do not need to provide another transaction inside OrderDetail's Insert method because that transaction is already available. You can test this by calling Transaction.Current inside this method. You will see that it returns a transaction, even though you opened it inside your Order method.

Ronald Wildenberg 2010-08-08 13:11:16

Thanks again Ronald.I'm reading about TransactionScope and I found this article:http://blogs.msdn.com/b/dbrowne/archive/2010/05/21/using-new-transactionscope-considered-harmful.aspxAbout timeout and isolationlevel, what do you suggest for an app where many insert operations (in sql server 2008) are done ? (e.g. setting up a catalog with thousands of items)

opaera 2010-08-08 13:24:53

I suggest you do some additional reading about isolation levels and ACID properties yourself :) Here's a good starting point: http://en.wikipedia.org/wiki/Isolation_(database_systems). I totally agree with the post. I hardly ever use Serializable because of performance degradation. It depends on your application however.

Ronald Wildenberg 2010-08-08 13:45:06

ansaurus

tags:

views:

answers:

Sharing connection and transaction in a Data Access Layer

related questions