Introducing Transactions

Introducing Transactions In Chapter 3, you saw how you can group SQL statements together into transactions. The transaction is then committed or rolled back as one unit.
Nội dung trích xuất từ tài liệu:
Introducing TransactionsIntroducing TransactionsIn Chapter 3, you saw how you can group SQL statements together into transactions. Thetransaction is then committed or rolled back as one unit. For example, in the case of abanking transaction, you might want to withdraw money from one account and deposit itinto another. You would then commit both of these changes as one unit, or if theres aproblem, roll back both changes. Youll be introduced to using transactions in ADO.NETin this section.There are three Transaction classes: SqlTransaction, OleDbTransaction, andOdbcTransaction, and you use an object of one of these classes to represent a transactionin ADO.NET. Ill show you how to use an object of the SqlTransaction class in thissection.Lets consider an example transaction that consists of two INSERT statements. The firstINSERT statement will add a row to the Customers table, and the second one will add arow to the Orders table. The new row in the Orders table will reference the new row inthe Customers table, and the two INSERT statements are as follows:INSERT INTO Customers CustomerID, CompanyName) VALUES J3COM, Jason Price Corporation)INSERT INTO Orders ( CustomerID) VALUES ( J3COM)You can use the following steps to perform these two INSERT statements using aSqlTransaction object: 1. Create a SqlTransaction object and start the transaction by calling the BeginTransaction() method of the SqlConnection object. 2. Create a SqlCommand object to hold the SQL statement. 3. Set the Transaction property for the SqlCommand object to the SqlTransaction object created in step 1. 4. Set the CommandText property of the SqlCommand object to the first INSERT statement. This INSERT statement adds a row to the Customers table. 5. Run the first INSERT statement using the ExecuteNonQuery() method of the SqlCommand object. This method is used because an INSERT statement doesnt return a result set. 6. Set the CommandText property of the SqlCommand object to the second INSERT statement. This statement adds a row to the Orders table. 7. Run the second INSERT statement using the ExecuteNonQuery() method of the SqlCommand object. 8. Commit the transaction using the Commit() method of the SqlTransaction object. This makes the two new rows added by the INSERT statements permanent in the database.Listing 8.9 illustrates these steps.Listing 8.9: EXECUTETRANSACTION.CS/* ExecuteTransaction.cs illustrates the use of a transaction*/using System;using System.Data;using System.Data.SqlClient;class ExecuteTransaction{ public static void Main() { SqlConnection mySqlConnection = new SqlConnection( server=localhost;database=Northwind;uid=sa;pwd=sa ); mySqlConnection.Open(); // step 1: create a SqlTransaction object and start the transaction // by calling the BeginTransaction() method of the SqlConnection // object SqlTransaction mySqlTransaction = mySqlConnection.BeginTransaction(); // step 2: create a SqlCommand object to hold a SQL statement SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); // step 3: set the Transaction property for the SqlCommand object mySqlCommand.Transaction = mySqlTransaction; // step 4: set the CommandText property of the SqlCommand object to // the first INSERT statement mySqlCommand.CommandText = INSERT INTO Customers ( + CustomerID, CompanyName + ) VALUES ( + J3COM, Jason Price Corporation + ); // step 5: run the first INSERT statement Console.WriteLine(Running first INSERT statement); mySqlCommand.ExecuteNonQuery(); // step 6: set the CommandText property of the SqlCommand object to // the second INSERT statement mySqlCommand.CommandText = INSERT INTO Orders ( + CustomerID + ) VALUES ( + J3COM + ); // step 7: run the second INSERT statement Console.WriteLine(Running second INSERT statement); mySqlCommand.ExecuteNonQuery(); // step 8: commit the transaction using the Commit() method // of the SqlTransaction object Console.WriteLine(Committing transaction); mySqlTransaction.Commit(); mySqlConnection.Close(); }}Note If you wanted to undo the SQL statements that make up the transaction, you can use the Rollback() method instead of the Commit() method. By default, transactions are rolled back. Always use the Commit() or Rollback() methods to explicitly indicate whether you want to commit or roll back your transactions.The output from this program is as follows:Running first INSERT statementRunning second INSERT statementCommitting transactionIf you want to run the program more than once, youll need to remove the row added tothe Customers and Orders table using the following DELETE statements (you can do thisusing the Query Analyzer tool):DELETE FROM OrdersWHERE CustomerID = J3COMDELETE FROM CustomersWHERE CustomerID = J3COM