16.3 Savepoints

Rolling back a transaction cancels the effect of all statements in that transaction. In some cases, it is only necessary to roll back a portion of the transaction. This can be done using savepoints.

A savepoint is created using the Save( ) method of the Transaction object. The method takes a string argument specifying the name of the savepoint. A transaction is rolled back to the savepoint by calling the RollBack() method and specifying the name of the savepoint as the optional argument. Savepoints are supported only by the SQL .NET managed data provider; nested transactions can be used with the OLE DB data provider to accomplish a similar result. The following example demonstrates how to use savepoints:

String connString = "Data Source=(local);Integrated security=SSPI;" + 
    "Initial Catalog=Northwind;";

SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();

//create command and enlist in transaction
SqlCommand cmd = new SqlCommand("InsertCustomer", conn, tran);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameterCollection cparams  =  cmd.Parameters;
cparams.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
cparams.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
// ... code to define remaining parameters

try
{
    // insert a record into the table
    cmd.Parameters["@CustomerID"].Value="CUST1";
    // ... set the other parameters
    cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    tran.Rollback();
    Console.WriteLine(
      "ERROR: {0}: Transaction rollback (CUST1).", ex.Message);
    conn.Close();
    return;
}

tran.Save("SavePoint1");

try
{
    // insert a second record into the table
    cmd.Parameters["@CustomerID"].Value = "CUST2";
    // ... set the other parameters
    cmd.ExecuteNonQuery();
    
    //if okay to here, commit the transaction
    tran.Commit();

    Console.WriteLine("Transaction commit (CUST1 and CUST2).");
}
catch (SqlException ex)
{
    tran.Rollback("SavePoint1");
    tran.Commit();
    Console.WriteLine(
      "ERROR: {0} Transaction commit (CUST1); " +
      "Transaction rollback (CUST2).", ex.Message);
}
finally
{
    conn.Close();
}