CODEWITHSUNDEEP
c#sql-server-2008.net-3.5data-access-layer
g45rg34d
g45rg34d

Reputation: 9660

Reusing SqlConnection within a DAL method

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.

Upvotes: 0

Views: 1465

Answers (4)

Sinaesthetic
Sinaesthetic

Reputation: 12211

This method holds a connection and just changes the parameters with each iteration. Just feed it your normal info and a delegate that returns a list of parameters, basically just telling it how to build the parameters list. Type T is the object type that you are building the parameter list from. It returns false only if all items fail. 

public static bool ExecuteBulkNonQuery<T>(string connectionString, CommandType commandType, 
            string commandText, IEnumerable<T> listItems, Func<T,SqlParameter[]> setParameters)
        {
            var fails = 0;
            using (var conn = new SqlConnection(connectionString))
            {
                using (var comm = new SqlCommand(commandText, conn))
                {
                    comm.CommandType = commandType;
                    conn.Open();
                    foreach (var obj in listItems)
                    {
                        comm.Parameters.Clear();
                        comm.Parameters.AddRange(setParameters.Invoke(obj));
                        fails += comm.ExecuteNonQuery();
                    }
                    return fails != 0;
                }
            }
        }

Upvotes: 0

bniwredyc
bniwredyc

Reputation: 8839

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.

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294367

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.

Upvotes: 5

Kangkan
Kangkan

Reputation: 15571

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.

Upvotes: 1

Related Questions