SQL Transactions with the Sqltransaction Class [Printer Friendly]
Stats
Rating: 4.69 Out of 5 by 29 Uses Submitted: 05/08/02 andrew ma (ajmaonline@hotmail.com)
. IntroductionTransactions What are transactions Imagine that we have some code that executed in this order:? 1. SQL command to withdraw money from account A 2. Do some processing 3. SQL command to deposit money from account BNow what happens if the code crashes or has errors on step 2. Then the money is lost. to stop this from happening, we can use transactions to ensure that either all the code is executed or that none of the code is executed.For those of you who are already familiar with transactions , you can skip all the reading and just look at the code samples. For those of you who are not familiar with transactions, this tutorial will attempt to explain in a little more detail. This tutorial also assumes some knowledge of SQL and how to execute SQL commands.Transaction overviewHow is this accomplished In this tutorial, we will be looking at the SqlTransaction class (link to MSDN doc) and three of its methods which we will use to implement transactions:? the Commit method, Save method and RollBack method.For a transaction to be implemented, we go through these steps: 1. Tell database to begin transaction 2. Start executing your code 3. If there is an error, abort the transaction 4. If everything executed properly, commit the changes.For the exact details and theory on how transactions are implemented, it would be better if you got a textbook or took a course. I am not really qualified or feel fit to explain transactions in that much detail.First Example
1 public void execSqlstmt (string strconnstring) {
2 sqlconnection conn = new sqlconnection; 3 conn.open ();
4
5 sqlcommand mycommand = new sqlcommand ();
6 sqltransaction myTrans;
Seduce
8 // Start The Transaction Here
9 myTrans = myconnection.begintransaction ();
10
11 // Assign THE Connection Object To Command
12 // Also Assign Our Transaction Object to the Command
13 mycommand.connection = myconnection;
14 mycommand.transaction = myTrans;
15
16 TRY
17 {
18 // 1. SQL Command to withdraw Money from Account A
19 mycommand.commandtext = "Update INTO Accounts set balance = balance - 100 where account = 'a'";
20 mycommand.executenonquery ();
21 // 2. Do some processing here
22 // .... More code Goes here ....
twenty three
24 // 3. SQL Command to Deposit Money from Account B
25 mycommand.commandtext = "Update Into Accounts Set Balance = Balance 100 Where Account = 'B'";
26 mycommand.executenonquery ();
27 myTrans.commit ();
28 console.writeline ("Money Was Transfered SuccessFully.");
29} catches (Exception E) {
30 myTrans.rollback ();
31 console.writeline ("Error: {1}", E.MESSAGE);
32 console.writeline ("Error Reported by {1}.", E.source;
33 Console.writeline ("Money Was Not Transfered.");
34}
35 FINALLY
36 {
37 // Don't forget to close the connection.
38 myconnection.close ();
39}
40}
We will explain how the four steps (described above) are achieved with the SqlTransaction class.First we'll look at how the SqlCommand object was created. Most of it should look familiar to all of you. The interesting lines that I would like to point out is line 9 and 14. in line 9, we use the BeginTransaction method to state the beginning of our transaction. For those of you who are really familiar with transaction, they can be executed with different isolation levels. See the BeginTransaction documentation in MSDN.In line 14, we have to assign our transaction to the SQL command.SqlTransaction.Commit methodhttp: //msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatasqlclientsqltransactionclasscommittopic.aspThis is the commit method that you will want to call after all the code has sucessfully been completed. When a transaction has begun, none of the changes are saved in the database until the commit method is called. This is to ensure that if the code on line 22 fails, then SQL Statement Online 19 Will Not Be Reflected In The Database. When The Commit Method Is Called (Line 27), Then Both SQL Statement '
s changes are saved in the database.SqlTransaction.Rollback methodhttp: //msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatasqlclientsqltransactionclassrollbacktopic.aspNow what happens when an error occurs and we catch it (with a try .. .catch ... finally block)? The transaction must be aborted so that money is not lost and so that either the whole transaction occurs or none of the transaction occurs. If the code fails on line 22, then we have to remove the SQL command that was execute on line 19. This can be achieved with the Rollback method. This method will abort the transaction and all the changes done before the commit will be erased. The database will be the same state as before any SQL statements were executed. Second Example (with the save method) 1 public void execSqlstmt (string strconnstring) {
2 SqlConnection conn = new sqlconnection;
3 conn.open ();
4
5 sqlcommand mycommand = new sqlcommand ();
6 sqltransaction myTrans;
Seduce
8 // Start The Transaction Here
9 myTrans = myconnection.begintransaction ();
10
11 // Assign THE Connection Object To Command
12 // Also Assign Our Transaction Object to the Command
13 mycommand.connection = myconnection;
14 mycommand.transaction = myTrans;
15
16 TRY
17 {
18 // Save an entry in the log
19 mycommand.commandtext = "INSERT INTO ATIVITY VALUES ('" DateTime.now ")"
20 mycommand.executenonquery ();
twenty one
22 // Save a Checkpoint Here
23 MyTrans.Save ("Begintransfer");
24 // 1. SQL Command to withdraw Money from Account A
25 mycommand.commandtext = "Update Into Accounts set balance = balance - 100 where account = 'a'"; 26 mycommand.executenonury ();
27 // 2. Do some processing here
28 // .... More code Goes here ....
29
30 // 3. SQL Command To Deposit Money from Account B
31 mycommand.commandtext = "Update INTO Accounts set balance = balance 100 where account = 'b'";
32 mycommand.executenonquery ();
33 myTrans.commit ();
34 console.writeline ("Money Was Transfered SuccessFully.");
35} catch (exception e) {
36 MyTrans. Rollback ("Begintransfer");
37 console.writeline ("Error: {1}", E.MESSAGE);
38 console.writeline ("Error Reported by {1}.", E.Source);
39 Console.writeline ("Money Was Not Transfered.");
40}
41 FINALLY
42 {
43 // Don't Forget to Close The Connection.
44 myconnection.close ();
45}
46}
SqlTransaction.Save methodhttp: //msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatasqlclientsqltransactionclasssavetopic.aspOur second example differs from the first in two ways First, there is addition code from line 18-23 Second,.. the RollBack method (on line 36) is different.The Save method is a way of adding checkpoints in your transactions. in addition to rolling back the entire transaction, checkpoints allow you to rollback to a certain point of the transaction. in our example, we enter a record into the Activity table to state that a user tried to transfer money. This could be used for auditing purposes or whatever this particular bank wants. When the transaction fails, we do not want to lose the record in the Activity table , so we can set a checkpoint just after the new record is added and when we rollback, we can tell the transaction to rollback to that particular point.The Save method takes one string as a parameter. This is the identifying string for this checkpoint . To rollback to that spot, you would have to call the RollBack method with the same identifying string as the parameter. It is possible to have multiple checkpoints and it is also still possible to rollback the entire transaction if the RollBack method is called without any Parameters.note That Transactions Don't come without a price. I'm not a Performance Expert So I Won '
