Lesson 3: Processing Transactions

Lesson 3: Processing Transactions

ADO.NET lets you group database operations into transactions. A transaction is a group of commands that change the data stored in a database. The transaction, which is treated as a single unit, assures that the commands are handled in an all-or-nothing fashion if one of the commands fails, all of the commands fail, and any data that was written to the database by the commands is backed out. In this way, transactions maintain the integrity of data in a database.

In this lesson, you ll learn how to implement transaction processing in a Web application for data sets and for databases.

After this lesson, you will be able to

• Understand the importance of transaction processing for Web applications that use data access

• Explain the levels of transaction processing that ADO.NET supports

• Manage changes to a data set as a transaction

• Create transaction objects for a database

• Track commands performed on a database through a transaction object

• Commit or roll back (undo) changes to a database using the transaction object

Estimated lesson time: 30 minutes

Understanding Transactions

As stated earlier, a transaction is a group of database commands that are treated as a single unit. Database programmers determine what database commands belong in a transaction by using the ACID test: commands must be atomic, consistent, isolated, and durable. Commands belong in a transaction if they are:

• Atomic

  In other words, they make up a single unit of work. For example, if a customer moves, you want your data entry operator to change all of the customer s address fields as a single unit, rather than changing street, then city, then state, and so on.

• Consistent

  All the relationships between data in a database are maintained correctly. For example, if customer information uses a tax rate from a state tax table, the state entered for the customer must exist in the state tax table.

• Isolated

  Changes made by other clients can t affect the current changes. For example, if two data entry operators try to make a change to the same customer at the same time, one of two things occurs: either one operator s changes are accepted and the other is notified that the changes weren t made, or both operators are notified that their changes were not made. In either case, the customer data is not left in an indeterminate state.

• Durable

  Once a change is made, it is permanent. If a system error or power failure occurs before a set of commands is complete, those commands are undone and the data is restored to its original state once the system begins running again.

Transaction processing is particularly important for Web applications that use data access, because Web applications are distributed among many different clients. In a Web application, databases are a shared resource, and having many different clients distributed over a wide area can present these key problems:

• Contention for resources

  Several clients might try to change the same record at the same time. This problem gets worse the more clients you have.

• Unexpected failures

  The Internet is not the most reliable network around, even if your Web application and Web server are 100 percent reliable. Clients can be unexpectedly disconnected by their service providers, by their modems, or by power failures.

• Web application life cycle

  Web applications don t follow the same life cycle as Windows applications Web forms live for only an instant, and a client can leave your application at any point by simply typing a new address in his or her browser.

Transaction processing follows these steps:

1. Begin a transaction.

2. Process database commands.

3. Check for errors.

4. If errors occurred, restore the database to its state at the beginning of the transaction. If no errors occurred, commit the transaction to the database.

In ADO.NET, transactions are handled in different ways, depending on the level you re working at:

• Data sets provide transaction processing through the RejectChanges and Update methods.

  Data sets also provide an AcceptChanges method that resets the state of records in a data set to Unchanged.

• Database connection objects provide transaction processing through the Transaction object.

  Transaction objects track commands performed on a database and provide the Rollback, Commit, and Save methods to restore database state, commit changes, or create a save point within a transaction, respectively.

• The System.EnterpriseServices namespace provides enterprise-level transactions through the ContextUtil class.

  Enterprise-level transactions use the Microsoft Distributed Transaction Coordinator (MS DTC) provided with Microsoft SQL Server 2000 to track transactions across multiple Web forms and across multiple COM+ components.

Data Set Transactions

Data sets provide implicit transaction processing, because changes to a data set are not made in the database until you invoke the Update method on the data adapter object. This lets you perform a set of commands on the data and then choose a point at which to make the changes permanent in the database.

If an error occurs during the Update method, none of the changes from the data set is made in the database. At that point, you can either attempt to correct the error and try the Update method again or undo the changes pending in the data set using the data set s RejectChanges method. For example, the following code displays the Contacts list in a DataGrid control and allows the user to delete rows by clicking Delete in the DataGrid:

Visual Basic .NET

Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Check if this is the first time page is displayed. If Not IsPostBack Then ' On first display: ' Fill the data set. adptContacts.Fill(dsContacts) ' Save data set as state variable. Session("dsContacts") = dsContacts Else ' On subsequent displays: ' Get the data set from the state variable. dsContacts = Session("dsContacts") End If ' Bind to data set. grdContacts.DataBind() End Sub Private Sub grdContacts_ItemCommand(ByVal source As Object, _ ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) _ Handles grdContacts.ItemCommand ' If the Delete button was clicked. If e.CommandName = "Delete" Then Dim intContactID ' Get selected row's ContactID. intContactID = _ CInt(grdContacts.Items(e.Item.ItemIndex).Cells(3).Text) Dim rowDelete As dsContacts.ContactsRow ' Get the row to delete from the data set. rowDelete = dsContacts.Contacts.FindByContactID(intContactID) ' Delete the row. rowDelete.Delete() ' Refresh the data grid. grdContacts.DataBind() End If End Sub

Visual C#

private void Page_Load(object sender, System.EventArgs e) { // Check if this is the first time page is displayed. if (!IsPostBack) { // On first display: // Fill the data set. adptContacts.Fill(dsContacts); // Save data set as state variable. Session["dsContacts"] = dsContacts; } else // On subsequent displays: // Get the data set from the state variable. dsContacts = (dsContacts)Session["dsContacts"]; // Bind to data set. grdContacts.DataBind(); } private void grdContacts_ItemCommand(object sender, System.Web.UI.WebControls.DataGridCommandEventArgs e) { // If the Delete button was clicked. if (e.CommandName == "Delete") { int intContactID; // Get selected row's ContactID. intContactID = Convert.ToInt16 (grdContacts.Items[e.Item.ItemIndex].Cells[3].Text); dsContacts.ContactsRow rowDelete; // Get the row to delete from the data set. rowDelete = dsContacts.Contacts.FindByContactID(intContactID); // Delete the row. rowDelete.Delete(); // Refresh the data grid. grdContacts.DataBind(); } }

The following event procedures for the Restore and Commit buttons let the user restore the data set to its previous state or, alternatively, update the database with the deletions made in the preceding code:

Visual Basic .NET

Private Sub butRestore_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles butRestore.Click ' Restore the data set to its original state. dsContacts.RejectChanges() ' Refresh the data grid. grdContacts.DataBind() End Sub Private Sub butCommit_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles butCommit.Click ' Update the database from the data set. adptContacts.Update(dsContacts) ' Save changes to state variable. Session("dsContacts") = dsContacts ' Refresh the data grid. grdContacts.DataBind() End Sub

Visual C#

private void butRestore_Click(object sender, System.EventArgs e) { // Restore the data set to its original state. dsContacts.RejectChanges(); // Refresh the data grid. grdContacts.DataBind(); } private void butCommit_Click(object sender, System.EventArgs e) { int intRows; // Update the database from the data set. intRows = adptContacts.Update(dsContacts); // Save changes to state variable. Session["dsContacts"] = dsContacts; // Refresh the data grid. grdContacts.DataBind(); }

The RejectChanges method in the preceding butRestore_Click event procedure returns the data set to its state before the row was deleted. The data set s AcceptChanges method is the inverse of RejectChanges it resets the DataRowState property for all the changed rows in a data set to Unchanged and removes any deleted rows.

The AcceptChanges method prevents the Update method from making those changes in the database, however, because Update uses the rows DataRowState property to determine which rows to modify in the database. For this reason, the AcceptChanges method is useful only when you do not intend to update a database from the data set.

Database Transactions

You can manage transactions at the database level through a transaction object. Since there are three types of database connections in ADO.NET, there are also three types of transaction object: SqlTransaction, OracleTransaction, and OleDbTransaction.

To use either type of transaction object, follow these steps:

1. Open a database connection.

2. Create the transaction object using the database connection object s BeginTransaction method.

3. Create command objects to track with this transaction, assigning the Transaction property of each command object to the name of the transaction object created in step 2.

4. Execute the commands. Because the purpose of transaction processing is to detect and correct errors before data is written to the database, this is usually done as part of an error-handling structure.

5. Commit the changes to the database or restore the database state, depending on the success of the commands.

6. Close the database connection.

The following code uses a DataGrid control to display a list of contacts from the Contacts database. The DataGrid control includes a column of buttons that allow the user to delete contacts. The DeleteContact function uses a transaction to ensure that a contact s calls are deleted if the contact is deleted. This helps ensure the integrity of the database.

Visual Basic .NET

Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Fill the data set. adptContacts.Fill(dsContacts) ' Bind to data set. grdContacts.DataBind() End Sub Private Sub grdContacts_ItemCommand(ByVal source As Object, _ ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) _ Handles grdContacts.ItemCommand ' If the Delete button was clicked. If e.CommandName = "Delete" Then Dim intContactID ' Get selected row's ContactID. intContactID = _ CInt(grdContacts.Items(e.Item.ItemIndex).Cells(3).Text) ' Delete the contact information. lblStatus.Text = DeleteContact(intContactID) End If ' Refresh the data set. adptContacts.Fill(dsContacts) ' Refresh the DataGrid. grdContacts.DataBind() End Sub Function DeleteContact(ByVal intContactID As Integer) As String ' Open the database connection. ContactMgmt.Open() ' Declare a transaction object. Dim transDelete As SqlTransaction ' Create the tranasction. transDelete = ContactMgmt.BeginTransaction _ (IsolationLevel.ReadCommitted) ' Create the command to delete from Contacts table. Dim cmdDelete As New SqlCommand("DELETE FROM Contacts" & _  " WHERE ContactDELETE FROM Calls WHERE " & _  " Contact deleted." Catch ' Restore the database state if there was an error. transDelete.Rollback() ' Return error message. Return "Contact could not be deleted." Finally ' Close the database. ContactMgmt.Close() End Try End Function

Visual C#

private void Page_Load(object sender, System.EventArgs e) { // Fill the data set. adptContacts.Fill(dsContacts); // Bind to the data set. grdContacts.DataBind(); } private void grdContacts_ItemCommand(object sender, System.Web.UI.WebControls.DataGridCommandEventArgs e) { // If the Delete button was clicked. if (e.CommandName == "Delete") { int intContactID; // Get selected row's ContactID. intContactID = Convert.ToInt16(grdContacts.Items [e.Item.ItemIndex].Cells[3].Text); // Delete the contact information. lblStatus.Text = DeleteContact(intContactID); } // Refresh the data set. adptContacts.Fill(dsContacts); // Refresh the data grid. grdContacts.DataBind(); } string DeleteContact(int intContactID) { // Open the database connection. ContactMgmt.Open(); // Declare a transaction object. SqlTransaction transDelete; // Create the tranasction. transDelete = ContactMgmt.BeginTransaction (IsolationLevel.ReadCommitted); // Create the command to delete from Contacts table. SqlCommand cmdDelete = new SqlCommand("DELETE FROM Contacts" +  " WHERE ContactDELETE FROM Calls WHERE " +  " Contact deleted."; } catch { // Restore the database state if there was an error. transDelete.Rollback(); // Return error message. return "Contact could not be deleted."; } finally { // Close the database. ContactMgmt.Close(); } }

The transaction object determines how concurrent changes to a database are handled through the IsolationLevel property. The level of protection varies, as specified by the settings described in Table 5-2.

Table 5-2. Isolation Level Settings

Isolation level	Behavior
ReadUncommitted	Does not lock the records being read. This means that an uncommitted change can be read and then rolled back by another client, resulting in a local copy of a record that is not consistent with what is stored in the database. This is called a dirty read because the data is inconsistent.
Chaos	Behaves the same way as ReadUncommitted, but checks the isolation level of other pending transactions during a write operation so that transactions with more restrictive isolation levels are not overwritten.
ReadCommitted	Locks the records being read and immediately frees the lock as soon as the records have been read. This prevents any changes from being read before they are committed, but it does not prevent records from being added, deleted, or changed by other clients during the transaction. This is the default isolation level.
RepeatableRead	Locks the records being read and keeps the lock until the transaction completes. This ensures that the data being read does not change during the transaction.
Serializable	Locks the entire data set being read and keeps the lock until the transaction completes. This ensures that the data and its order within the database do not change during the transaction.

SQL database connections provide one transaction capability that is unavailable for OLE database connections: the ability to create save points within a transaction. Save points let you restore the database state to a specific position within the current transaction. To set a save point within a SQL transaction, use the Save method:

Visual Basic .NET

transDelete.Save("FirstStep")

Visual C#

transDelete.Save("FirstStep");

To restore a SQL transaction to a save point, specify the name of the save point in the Rollback method:

Visual Basic .NET

transDelete.Rollback("FirstStep")

Visual C#

transDelete.Rollback("FirstStep");

Enterprise Transactions

Because transactions can span multiple Web forms, or even multiple components within a distributed application, ASP.NET provides a way for Web forms to work with MS DTC.

To use MS DTC from a Web form, follow these steps:

1. Start a new transaction or continue an existing transaction by setting the document element s Transaction attribute. For example, the following @ Page directive starts a new transaction:

   <%@ Page Language="vb" AutoEventWireup="false"  Codebehind="Transaction3.aspx.vb"  Inherits="MCSDWebAppsVB.Transaction3"  Transaction="RequiresNew"%>

2. Add a reference to the System.EnterpriseServices namespace to your project.

3. Use the ContextUtil class s SetAbort and SetComplete methods to change the status of the transaction as required.

4. Use the Page class s CommitTransaction and AbortTransaction events to respond to changes in the transaction s status.

See the Visual Studio .NET online Help for more information about MS DTC.