Home > Articles > Programming > Windows Programming

Working With ADO.NET Transactions

  • Print
  • + Share This
Almost any relational database system provides the facility for database transactions. Database transactions are a set of commands that either all fail or all succeed, as a group. ADO.NET provides a set of objects used to create and work with transactions, which will be discussed in this lesson.

See all Sams Teach Yourself on InformIT Programming Tutorials.

This chapter is from the book

In the preceding hour, you saw how to use SQL and OLE DB connection pooling in your applications to increase your application's responsiveness and decrease the amount of system resources used. In this hour, you're going to see how database transactions can help maintain the integrity of your database. A transaction is a set of actions that are executed as a group, with all actions succeeding or all actions failing.

In this hour, you'll see how to

  • Initiate a database transaction using ADO.NET

  • Roll back changes made to a database

  • Save changes made while using a transaction

  • Perform a database transaction in a stored procedure

What Is a Transaction?

Consider this classic transaction example: A bank needs to transfer a million dollars from bank account A to bank account B. First, the money in A needs to be placed in B. Then, after the transfer has been verified, the money needs to be removed from account A. Obviously, if there is any sort of problem placing the money in account B, you do not want to remove it from account A. Likewise, if the money is placed successfully in account B, you want to ensure that it is removed from account A. All actions need to fail or all actions need to succeed.

If you were coding an application to perform the monetary transactions in the last paragraph, you could do so relatively easily without having to use a transaction by intelligently trapping application errors and maintaining state. However, consider that a transaction isn't limited in size. Imagine keeping track of 500 database changes manually! Also, suppose the server is turned off in the middle of your application's processing. You need to make sure that when the application is brought back online, it knows exactly where it stopped so that it can either reverse all the changes made or attempt to continue where it left off.

Fortunately, because transactions exist, you need not worry about any of these problems. By wrapping your database actions in a transaction, you can help ensure that your data remains correct and consistent. In the next few sections, you'll see how to use database transactions.

  • + Share This
  • 🔖 Save To Your Account