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

  • Complex hybrid environments can make it difficult to track interdependencies, increasing the risk of disrupting critical business services. In this white paper by EMA, you'll learn how application discovery and dependency mapping can help you: Meet granular targets for availability, cost, and time-to-revenue for cloud services. Accelerate mean time to repair (MTTR) while communicating better with stakeholders. Manage even the most complex hybrid environments more efficiently and effectively Understand the …

  • When individual departments procure cloud service for their own use, they usually don't consider the hazardous organization-wide implications. Read this paper to learn best practices for setting up an internal, IT-based cloud brokerage function that service the entire organization. Find out how this approach enables you to retain top-down visibility and control of network security and manage the impact of cloud traffic on your WAN.

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date