Working With ADO.NET Transactions
- Hour 18: Working with Transactions
- Transactions and ADO.NET
- Transactions with Stored Procedures
- Summary
- Q&A
- Workshop
See all Sams Teach Yourself on InformIT Programming Tutorials.
This chapter is from the book
This chapter is from the book
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.