2

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.

Community
  • 1
webnoob
  • 2,139
  • 3
  • 19
  • 20

1 Answers1

4

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.

Oded
  • 53,326
  • 19
  • 166
  • 181
  • 1
    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. – Jon Raynor Mar 29 '12 at 19:59