Still perusing my copy of Pro ADO.NET 2.0 by Apress.
In the last post I talked about the basics of SQL Server Transactions. There is also a small 3 page section on SQL Server Savepoints, which is the ability to mark points within the life of your transaction in the case that you may only want to rollback the current transaction to a specific point as opposed to the very beginning.
If you look at a snippet of the SQL Server Transaction Template presented in a previous post, you will notice the transaction.Rollback() statement in the catch block that rolls back the entire transaction when an unexpected exception is thrown:
try
{
// ...
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
SqlTransaction.Rollback and SqlTransaction.Save Methods
The SqlTransaction Rollback method has an overload that will accept the name of a Savepoint if you would rather just roll the transaction back to a specific Savepoint as opposed to rolling back the entire transaction:
transaction.Rollback("FirstUpdate");
As you would expect, the SqlTransaction Class also has a method, called Save, that allows you to create a Savepoint in the life of the transaction:
transaction.Save("FirstUpdate");
Example of SqlTransaction.Rollback and SqlTransaction.Save
Using both the Rollback and Save methods in SqlTransaction to partially rollback a transaction would look something like below. Of course, rather than just rolling back for kicks, a real application would have some business logic for doing so:
using (SqlConnection connection =
new SqlConnection(connectionString))
{
using (SqlCommand command =
connection.CreateCommand())
{
connection.Open();
SqlTransaction transaction =
connection.BeginTransaction();
command.Transaction = transaction;
try
{
command.CommandText = "Update...";
command.ExecuteNonQuery();
// Create a Savepoint
transaction.Save("FirstUpdate");
command.CommandText = "Insert...";
command.ExecuteNonQuery();
// Rollback to FirstUpdate
transaction.Rollback("FirstUpdate");
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}
SQL Server Transaction Savepoints Conclusion
Savepoints give you the flexibility to only roll back and commit portions of a SQL Server Transaction as opposed to rolling back the entire transaction. One may not need the functionality often, but it is nice to know savepoints exist when the time comes.
Recent SQL Server Posts:
David Hayden ( Website / Blog )