Whenever you rollback a transaction, it rolls back all the
operations that you have performed before. Sometimes you need to undo the
transaction to a certain point. For this scenario, you can use the Save Points
functionality. Save Points allows you to rollback a transaction to a certain
point. Let’s take a look at code below, which uses a Save Point.
Listing 6 – Using Save Points in Transaction
public static void AddUserSavePointTransaction()
{
SqlConnection myConnection = newSqlConnection(ConnectionString);
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText = @
"INSERT INTOUsers(FirstName,LastName)VALUES('John', 'Doe')
";myCommand.Connection = myConnection;
SqlTransaction myTransaction = null;
try
{
myConnection.Open();
myTransaction =myConnection.BeginTransaction();
myCommand.Transaction = myTransaction;
myCommand.ExecuteNonQuery();
myTransaction.Save("firstInsertUser");
myCommand.CommandText = @
"INSERT INTOUsers(FirstName,LastName)VALUES('Azam', 'Sharp')";
myCommand.ExecuteNonQuery();
myTransaction.Rollback("firstInsertUser");
}
catch (Exception ex)
{
string exception = ex.Message;
myTransaction.Rollback();
}
finally
{
myTransaction.Commit();
myConnection.Close();
}
}Analysis
As you can see in the code above, I am using the command
object to insert a new user. After executing the command object for the first
time, I create a save point and insert another user. Immediately after
inserting the second user, I rollback the transaction to the save point. Since
the save point is declared after first user is inserted, only the insertion of
first user will succeed, and the insertion of the second user will be
discarded.
[Download
Sample]
