Database Tip: Managing Transactions with a Stack

Although a lot of database work in many web applications involves single table queries and changes, in many cases you make multiple changes to a database. In these cases, you should consider using transactions. A transaction defines a unit of work to be performed, and for the transaction to complete, each part of it has to complete successfully. If everything works, the transaction is committed to the database. If not, all the changes are rolled back. You also have the option to nest transactions, in case you have a very complicated unit of work that is broken into "sub-units" of work.

In some of my own code, I've created a method to manage these nested transactions by using a Stack collection. A stack is a basic computer science concept that helps implement a last-in, first-out structure. When you start a transaction, you push the transaction onto the stack. When you commit the transaction, you pop it off the stack. If you have to roll back a transaction, you need to roll back each nested transaction in reverse order. The stack is perfect for this arrangement.

Here are the functions I added to my database class:

public void BeginTransaction()
{
   SqlTransaction currentTrans = currentConnection.BeginTransaction();
   transactions.Push(currentTrans);
}

public void RollbackTransaction()
{
   SqlTransaction currentTrans;
   while (transactions.Count > 0)
   {
      currentTrans = (SqlTransaction)transactions.Pop();
      currentTrans.Rollback();
   }
}

public void CommitTransaction()
{
   SqlTransaction currentTrans = (SqlTransaction)transactions.Peek();
   currentTrans.Commit();
   transactions.Pop();
}

public SqlTransaction CurrentTransaction
{
   get
   {
      if (transactions.Count > 0)
         return (SqlTransaction)transactions.Peek();
      else
         return null;
   }
}

I have the transactions variable declared as:

private Stack transactions = new Stack();

Each time I run a function within the Database class, I look to see whether the CurrentTransaction is null; if not, I set the Transaction property of each SqlCommand object equal to it. This enlists the command in the transaction. When I'm done with all my work, I call CommitTransaction, which commits the current transaction to the database. If any errors occur, I can call RollbackTransaction, which rolls back all pending transactions. A failure in one part of the transaction means the entire thing needs to be undone in this model.

In regards to using the Stack object, the Peek method returns the top object without removing it from the stack; doing this lets you use the object without removing it from the stack, because a transaction may have three or four actions before it's complete.

About the Author

Eric Smith is the owner of Northstar Computer Systems, a web-hosting company based in Indianapolis, Indiana. He is also a MCT and MCSD who has been developing with .NET since 2001. In addition, he has written or contributed to 12 books covering .NET, ASP, and Visual Basic. Send him your questions and feedback via e-mail at questions@techniquescentral.com.



Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • A modern mobile IT strategy is no longer an option, it is an absolute business necessity. Today's most productive employees are not tied to a desk, an office, or a location. They are mobile. And your company's IT strategy has to be ready to support them with easy, reliable, 24/7 access to the business information they need, from anywhere in the world, across a broad range of communication devices. Here's how some of the nation's most progressive corporations are meeting the many needs of their mobile workers …

  • Java developers know that testing code changes can be a huge pain, and waiting for an application to redeploy after a code fix can take an eternity. Wouldn't it be great if you could see your code changes immediately, fine-tune, debug, explore and deploy code without waiting for ages? In this white paper, find out how that's possible with a Java plugin that drastically changes the way you develop, test and run Java applications. Discover the advantages of this plugin, and the changes you can expect to see …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds