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

  • With 81% of employees using their phones at work, companies have stopped asking: "Is corporate data leaking from personal devices?" and started asking: "How do we effectively prevent corporate data from leaking from personal devices?" The answer has not been simple. ZixOne raises the bar on BYOD security by not allowing email data to reside on the device. In addition, Zix allows employees to maintain complete control of their personal device, therefore satisfying privacy demands of valued employees and the …

  • Managing your company's financials is the backbone of your business and is vital to the long-term health and viability of your company. To continue applying the necessary financial rigor to support rapid growth, the accounting department needs the right tools to most efficiently do their job. Read this white paper to understand the 10 essentials of a complete financial management system and how the right solution can help you keep up with the rapidly changing business world.

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds