How to deal with transactions when creating a database connection for each query
- by webnoob
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.
