How to deal with transactions when creating a database connection for each query

Question

In line with this post here I am going to change my website to create a connection per query to take advantage of .NET's connection pooling. With this in mind, I don't know how I should deal with transactions. At the moment I do something like (psuedo code):

GlobalTransaction = GlobalDBConnection.BeginTransaction();
try
{
 ExecSQL("insert into table ..")
 ExecSQL("update some_table ..")
 ....
 GlobalTransaction.Commit();
}catch{
 GlobalTransaction.Rollback();
 throw;
}

ExecSQL would be like this:

using (SqlCommand Command = GlobalDBConnection.CreateCommand())
{
 Command.Connection = GlobalDBConnection;
 Command.Transaction = GlobalTransaction;
 Command.CommandText = SQLStr;
 Command.ExecuteNonQuery();
}

I'm not quite sure how to change this concept to deal with transactions if the connection is created within ExecSQL because I would want the transaction to be shared between both the insert and update routines.

Answer 1

You should be using TransactionScope for transactions in .NET:

using(var tx = new TransactionScope())
{
 ExecSQL("insert into table ..")
 ExecSQL("update some_table ..")
 ....
 tx.Complete();
}

The above code will ensure both statements will belong to one transaction.

You can nest TransactionScopes if needed.

• Jon Raynor: Just keep in mind that transactions will escalate to a distributed transaction if you are connecting to different data sources. MS DTC (Microsoft Distributed Transaction Coordinator) would then be needed to facilitate those types of scenarios.