Sharing connection and transaction in a Data Access Layer

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 engli开发者_开发技巧sh sucks!

Thanks.

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.