Transaction Savepoints

Today I learned how to roll back multiple database queries using transaction savepoints. This is useful when you have several related record inserts, updates, or deletes that should all succeed or all fail in order to maintain your data integrity. You can associate a transaction with a database connection, create a transaction savepoint, and then rollback all your queries if there is an error or commit the queries. Since I am mostly programming in C# nowadays I will provide sample code in C#:

string strSQL;
SqlCommand objCmd = new SqlCommand();
SqlConnection objCn = new SqlConnection(strCn);
objCn.Open();
SqlTransaction transaction = objCn.BeginTransaction();
// create a savepoint to rollback all database changes if necessary
transaction.Save("BeforeInserts");
try {
strSQL = "INSERT INTO table_name VALUES(column_name, column_name)";
objCmd.Connection = objCn;
objCmd.CommandText = strSQL;
objCmd.Transaction = transaction;
objCmd.ExecuteNonQuery();
strSQL = "INSERT INTO table_name VALUES(column_name, column_name)";
objCmd.Connection = objCn;
objCmd.CommandText = strSQL;
objCmd.Transaction = transaction;
objCmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
lblMessage.Text = "Error: " + ex.Message + "rn" + ex.StackTrace + "rn";
// rollback all database changes
transaction.Rollback("BeforeInserts");
}
finally
{
transaction.Commit();
// additional database changes cannot use the transaction after it has been committed
strSQL = "UPDATE table_name SET column_name = '""' + value + "'";
objCmd.CommandText = strSQL;
objCmd.ExecuteNonQuery();
objCn.Close();
}