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

By default .NET creates a pooled SqlConnection. So creating new connections (provided all of them use the same connection string) does not cause an overhead until the pool is completely exhausted.

Answer 2

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.

Answer 3

Yes, it's a good practice to reuse a single SqlConnection for multiple SqlCommands (within one method). As I understood in your case you also need to use SqlTransaction like this:

public void SomeDALMethod(string connectionString)
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();

        var transaction = connection.BeginTransaction();

        var command1 = new SqlCommand("tblOrders_CreateNewOrder", connection, transaction)
                        {
                            CommandType = CommandType.StoredProcedure
                        };
        var command2 = new SqlCommand("tblProducts_RecalculateStock", connection, transaction)
                        {
                            CommandType = CommandType.StoredProcedure
                        };

        try
        {
            command1.ExecuteNonQuery();
            command2.ExecuteNonQuery();
            transaction.Commit();
        }
        catch (Exception ex)
        {
            // Commit failed
            try
            {
                transaction.Rollback();
            }
            catch (Exception ex2)
            {
                // Rollback failed
            }
        }           
    }
}

It allows you to rollback execution of the first stored procedure in case of the second sp failed.