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

start example
 /*   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' 
end example

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.

click to expand
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

start example
 /*   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();   } } 
end example

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 




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net