14.21 Ensuring Data Integrity Using Transactions

You want to use a transaction when performing a database update.

Technique

You use a transaction when you must make a series of updates to a data source. You want to ensure that, as the updates are made, the changes are not committed unless all the commands sent to the database server are successful. To implement transactions, the .NET framework defines a class named SqlTransaction that works in conjunction with an SqlCommand object. You create an SqlTransaction object by assigning it to the return value of the BeginTransaction method defined in the SqlConnection class. This SqlTransaction object is then assigned to the Transaction property of the SqlCommand object. As the updates occur, the transaction object keeps track of the changes being made and retains the original state of the data source should an update fail. If all the commands are successful, you call the Commit method from the SqlTransaction object to finish the transaction. If an exception is thrown while you are performing updates, then an update did not work, and any data changes must revert back to their old state. You do so with the exception catch block by calling the Rollback method defined in the SqlTransaction class. Listing 14.7 demonstrates how to use the SqlTransaction class in conjunction with an SqlConnection and an SqlCommand object. Within the try block, you can see two attempts to create new products within the Products table of the Northwind database. The second INSERT statement is intentionally wrong because the ProductName field in the table cannot be null . Because of this statement, the updates fail and the transaction rolls the data back to its previous state, which means the changes made from the first INSERT statement are not committed.

Listing 14.7 Supporting Transactions

 using System; using System.Data; using System.Data.SqlClient; namespace _21_Transaction {     class Class1     {         [STAThread]         static void Main(string[] args)         {             SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Data [ic:ccc]Source=VCSMARKHSCH6;Initial Catalog=Northwind;");             connection.Open();             SqlCommand insertCommand = connection.CreateCommand();             SqlTransaction transaction;             // Start a local transaction             transaction = connection.BeginTransaction();             // assign connection and transaction to command             insertCommand.Connection = connection;             insertCommand.Transaction = transaction;             try             {                 // this command will succeed                 insertCommand.CommandText =                   "Insert into Products (ProductName ) VALUES ('New Product')";                 insertCommand.ExecuteNonQuery();                 // this command intentionally fails                 // since ProductName cannot be null                 insertCommand.CommandText =                   "Insert into Products (ProductName) VALUES (null)";                 insertCommand.ExecuteNonQuery();                 transaction.Commit();                 Console.WriteLine("Database Updated Successfully");             }             catch(Exception e)             {                 transaction.Rollback();                 Console.WriteLine( e.Message );             }             finally             {                 connection.Close();             }         }     } } 

Comments

Transactions play a vital role for applications that work with critical data. If a single command fails during a sequence of updates to a database, then to ensure data integrity, the changes do not happen because any updates might have adverse side effects. Suppose an application that performs money transfers for stocks first removes the money from your bank account ”and just before it uses the money to buy shares, the power goes out. Without transaction support, you would lose that money or at least go through a couple miles of red tape to get it back.