Reusing SqlConnection within a DAL method

Question

Suppose we have a DAL method

public void BuyProduct(int productId, int quantity, int buyerId);

Within that method we need to call 2 stored procedures:

1. EXEC tblOrders_CreateNewOrder
2. EXEC tblProducts_RecalculateStock

Is it a good practice to create 2 SqlCommands - one per stored procedure and to use a single SqlConnection to execute those commands?

OR

Is it better to create a separate SqlConnection for each SqlCommand?

So basically I am asking: is it a good practice to reuse a single SqlConnection for multiple (2-4) SqlCommands within a single DAL method (obviously reusing SqlConnection across the entire DAL would be dumb)?

PS - please do not ask me why can't I just combine the 2 stored procedures into 1. My answer is - separation of concerns.

Answer 1

The real problem is not the connection, but the transactions. When a logical operation involves multiple DAL physical operations, usually they have to be part of a transcation. Unless corectness is optional... If a transaction spans multiple connections then it has to be elevated to a distributed transaction, with disastrous perofrmance results. So when designing a DAL, always thrive to affinitize a transaction to a connection. This ripples through the DAL API design, as usually the result is that the conneciton and transaction objects have to be explictly handed out to the DAL methods, either as individual parameters or as a 'context' object that aggregates them.