Unidev Tech Blog

I recently needed to update an existing C# solution of multiple projects to add transactions around two different data updates.  I wanted to have this pattern for each one:

• Start transaction
• Delete all existing data
• Insert all new data
• Commit transaction

Because of how the existing code had already being started, I ended up using two different methods, both of which turned out to be fairly simple.

For the first technique I used the TransactionScope object. I simply placed my SQL calls within a TransactionScope block. The TransactionScope object will set the current transaction which SQL will automatically find when you execute the statements. The two functions shown below were already getting their own separate connection to the database.

using (TransactionScope ts = new TransactionScope())

{

            DeleteOldData();

            InsertNewData();

}

For the second technique I used the CommitableTransaction object because the project was using the same database connection for multiple SQL calls.  I just made a new CommitableTransaction object and then enlisted it to my existing connection. Just remember to commit when complete or rollback on exceptions.  Here’s a snippet:

            private SqlConnection connection;

public void start()

      {

   CommittableTransaction tx = new CommittableTransaction();

   connection.EnlistTransaction(tx);

   DeteleInfo();  //This uses the connection object defined above

   SqlCommand command = new SqlCommand(“InsertInfo”, connection);

   command.CommandType = CommandType.StoredProcedure;

   command.ExecuteNonQuery();

   . . .

   tx.Commit();

}

For both of these methods to work, you need to have a reference to System.Transactions.dll as shown below in C#. 

using System.Transactions;

I had to add specifically add a reference to it in my project. I found it here:

C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Transactions.dll

References:

http://quickstarts.asp.net/quickstartv20/howto/doc/transactions/TransactionsWithSQL.aspx