SQL Transactions with the SqlTransaction Class (ZT)

SQL Transactions with the SqlTransaction Class   [ printer friendly ] Stats   Rating: 4.69 out of 5 by 29 users   Submitted: 05/08/02 Andrew Ma (ajmaonline@hotmail.com)

Introduction Transactions. 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 B Now 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 overview How 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(strConnString); 3 conn.Open(); 4 5 SqlCommand myCommand = new SqlCommand(); 6 SqlTransaction myTrans; 7 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.... 23 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 } catch(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 method http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatasqlclientsqltransactionclasscommittopic.asp This 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 on line 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 method http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatasqlclientsqltransactionclassrollbacktopic.asp Now 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(strConnString); 3 conn.Open(); 4 5 SqlCommand myCommand = new SqlCommand(); 6 SqlTransaction myTrans; 7 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 Activity values('New transfer attempt', '" + DateTime.Now + "')"; 20 myCommand.ExecuteNonQuery(); 21 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.ExecuteNonQuery(); 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 method http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatasqlclientsqltransactionclasssavetopic.asp Our 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 don't 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't attempt to make any guesses but its safe to say that using transactions will have an overhead. Try to use them only when necessary. Conclusion Transactions are good when you have a series of SQL statments to call and errors can occur anywhere in between. They help ensure that either all the statements are executed or none of them are executed. Links Devhood ASP.NET Message Forum - Feel free to ask questions to me or other Devhood members. Devhood C# Message Forum - Feel free to ask questions to me or other Devhood members. http://www.devhood.com - Can't forget to mention this great resource. :-) http://www.asp.net - Good portal to other ASP.NET resources. http://www.brinkster.com - Free ASP.NET hosting BeginTransaction Method - link to MSDN documentation. SqlTransaction class - link to MSDN documentation.