Setting a Savepoint

Chia sẻ: Tuan Nghia | Ngày: | Loại File: PDF | Số trang:6

Thêm vào BST

Báo xấu

54
lượt xem 5
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Setting a Savepoint You can set a savepoint anywhere within a transaction. This allows you to roll back any changes made to database rows after your savepoint.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Setting a Savepoint

Setting a Savepoint You can set a savepoint anywhere within a transaction. This allows you to roll back any changes made to database rows after your savepoint. This might be useful if you have a very long transaction because if you make a mistake after you've set a savepoint, you don't have to roll back the transaction all the way to the start. Setting a Savepoint Using T-SQL You set a savepoint in T-SQL using the SAVE TRANSACTION statement, or the shorthand version, SAVE TRANS. The syntax for this statement is as follows: SAVE TRANS[ACTION] { savepointName | @savepointVariable } where • savepointName specifies a string containing the name you want to assign to your savepoint. • savepointVariable specifies a T-SQL variable that contains your savepoint name. Your variable must be of the char, varchar, nchar, or nvarchar data type. The following example sets a savepoint named SaveCustomer: SAVE TRANSACTION SaveCustomer Let's look at a complete T-SQL example script that sets a savepoint within a transaction. Listing 14.1 shows a T-SQL script that performs the following steps: 1. Begins a transaction. 2. Inserts a row into the Customers table with a CustomerID of J8COM. 3. Sets a savepoint. 4. Inserts a row into the Orders table with a CustomerID of J8COM. 5. Performs a rollback to the savepoint, which undoes the previous insert performed in step 4, but preserves the insert performed in step 2. 6. Commits the transaction, which commits the row inserted into the Customers table in step 2. 7. Selects the new row from the Customers table. 8. Attempts to select the from the Orders table that was rolled back in step 5. 9. Deletes the new row from the Customers table. Listing 14.1: SAVEPOINT.SQL /*
Savepoint.sql illustrates how to use a savepoint */ USE Northwind - step 1: begin the transaction BEGIN TRANSACTION - step 2: insert a row into the Customers table INSERT INTO Customers ( CustomerID, CompanyName ) VALUES ( 'J8COM', 'J8 Company' ) - step 3: set a savepoint SAVE TRANSACTION SaveCustomer - step 4: insert a row into the Orders table INSERT INTO Orders ( CustomerID ) VALUES ( 'J8COM' ); - step 5: rollback to the savepoint set in step 3 ROLLBACK TRANSACTION SaveCustomer - step 6: commit the transaction COMMIT TRANSACTION - step 7: select the new row from the Customers table SELECT CustomerID, CompanyName FROM Customers WHERE CustomerID = 'J8COM' - step 8: attempt to select the row from the Orders table - that was rolled back in step 5 SELECT OrderID, CustomerID FROM Orders WHERE CustomerID = 'J8COM' - step 9: delete the new row from the Customers table DELETE FROM Customers
WHERE CustomerID = 'J8COM' To run the Savepoint.sql script using Query Analyzer, you select File ➣ Open, open the script from the sql directory, and then press F5 on the keyboard or select Query ➣ Execute from the menu. Figure 14.1 shows the Savepoint.sql script being run in Query Analyzer. Figure 14.1: Running the Savepoint.sql script in Query Analyzer Setting a Savepoint Using a SqlTransaction Object You set a savepoint in a SqlTransaction object by calling its Save() method, passing a string containing the name you wish to assign to your savepoint. Assume you have a SqlTransaction object named mySqlTransaction; the following example sets a savepoint named SaveCustomer by calling the Save() method of mySqlTransaction: mySqlTransaction.Save("SaveCustomer"); You can then roll back any subsequent changes made to the rows in the database by calling the Rollback() method of mySqlTransaction, passing the savepoint name to the Rollback() method. For example: mySqlTransaction.Rollback("SaveCustomer"); Let's look at a complete C# program that sets a savepoint within a transaction. Listing 14.2 shows a program that performs the following steps:
1. Creates a SqlTransaction object named mySqlTransaction. 2. Creates a SqlCommand and sets its Transaction property to mySqlTransaction. 3. Inserts a row into the Customers table. 4. Sets a savepoint by calling the Save() method of mySqlTransaction, passing the name SaveCustomer to the Save() method. 5. Inserts a row into the Orders table. 6. Performs a rollback to the savepoint set in step 4, which undoes the previous insert performed in step 5, but preserves the insert performed in step 3. 7. Displays the new row added to the Customers table. 8. Deletes the new row from the Customers table. 9. Commits the transaction. Listing 14.2: SAVEPOINT.CS /* Savepoint.cs illustrates how to set a savepoint in a transaction */ using System; using System.Data; using System.Data.SqlClient; class Savepoint { public static void Main() { SqlConnection mySqlConnection = new SqlConnection( "server=localhost;database=Northwind;uid=sa;pwd=sa" ); mySqlConnection.Open(); // step 1: create a SqlTransaction object SqlTransaction mySqlTransaction = mySqlConnection.BeginTransaction(); // step 2: create a SqlCommand and set its Transaction property // to mySqlTransaction SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.Transaction = mySqlTransaction; // step 3: insert a row into the Customers table Console.WriteLine("Inserting a row into the Customers table "+ "with a CustomerID of J8COM");
mySqlCommand.CommandText = "INSERT INTO Customers ( " + " CustomerID, CompanyName " + ") VALUES ( " + " 'J8COM', 'J8 Company' "+ ")"; int numberOfRows = mySqlCommand.ExecuteNonQuery(); Console.WriteLine("Number of rows inserted = "+ numberOfRows); // step 4: set a savepoint by calling the Save() method of // mySqlTransaction, passing the name "SaveCustomer" to // the Save() method mySqlTransaction.Save("SaveCustomer"); // step 5: insert a row into the Orders table Console.WriteLine("Inserting a row into the Orders table "+ "with a CustomerID of J8COM"); mySqlCommand.CommandText = "INSERT INTO Orders ( " + " CustomerID " + ") VALUES ( " + "'J8COM' "+ ")"; numberOfRows = mySqlCommand.ExecuteNonQuery(); Console.WriteLine("Number of rows inserted = "+ numberOfRows); // step 6: rollback to the savepoint set in step 4 Console.WriteLine("Performing a rollback to the savepoint"); mySqlTransaction.Rollback("SaveCustomer"); // step 7: display the new row added to the Customers table mySqlCommand.CommandText = "SELECT CustomerID, CompanyName "+ "FROM Customers "+ "WHERE CustomerID = 'J8COM'"; SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); while (mySqlDataReader.Read()) { Console.WriteLine("mySqlDataReader[\" CustomerID\"] = "+ mySqlDataReader["CustomerID"]); Console.WriteLine("mySqlDataReader[\" CompanyName\"] = "+ mySqlDataReader["CompanyName"]); }
mySqlDataReader.Close(); // step 8: delete the new row from the Customers table Console.WriteLine("Deleting row with CustomerID of J8COM"); mySqlCommand.CommandText = "DELETE FROM Customers "+ "WHERE CustomerID = 'J8COM'"; numberOfRows = mySqlCommand.ExecuteNonQuery(); Console.WriteLine("Number of rows deleted = "+ numberOfRows); // step 9: commit the transaction Console.WriteLine("Committing the transaction"); mySqlTransaction.Commit(); mySqlConnection.Close(); } } The output from this program is as follows: Inserting a row into the Customers table with a CustomerID of J8COM Number of rows inserted = 1 Inserting a row into the Orders table with a CustomerID of J8COM Number of rows inserted = 1 Performing a rollback to the savepoint mySqlDataReader["CustomerID"] = J8COM mySqlDataReader["CompanyName"] = J8 Company Deleting row with CustomerID of J8COM Number of rows deleted = 1 Committing the transaction