Database Transactions – How to Handle Transactions When Creating a Database Connection for Each Query

cdatabasesql server

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.

Best Answer

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.

Related Topic