How To Roll Back Updates After an Error When You Use DataAdapter and DataSet in ADO.NET and Visual Basic .NET

This article describes how to roll back updates after an error when you use a DataAdapter and a DataSet object.

If your client application uses an ADO.NET DataSet object to store changes that are made to data or to add new records, you can use a Transaction object, a DataSet object, and a DataAdapter object to roll back any changes if an error occurs. If you use a CommandBuilder object to generate INSERT, UPDATE, and DELETE commands, you must set the Transaction property of the SELECT command of the DataAdapter.

If you use a wizard to generate a typed DataSet and its Command objects to populate the InsertCommand, the UpdateCommand, and the DeleteCommand properties of the DataAdapter, you must set the Transaction property for each InsertCommand, UpdateCommand, and DeleteCommand but not the SelectCommand of the DataSet.

NOTE: This article uses the CommandBuilder.

Steps to Build the Sample

1. Follow these steps to create a new Visual Basic .NET Windows Application project:
   1. Start Microsoft Visual Studio .NET.
   2. On the File menu, point to New, and then click Project.
   3. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click Windows Application under Templates.
2. Add the following Imports statement to the "Form1 Declarations" section:

   Imports System.Data.SqlClient
   					

3. Add a Button control, and then add the following code to the Click event of Button1:

   Dim stConnect As String = "user id=myUserID;password=myPassword;initial catalog=northwind;data source=myServer;Connect Timeout=30"
           Dim cnNorthwind As New SqlConnection(stConnect)
           Dim stSQL As String = "select * from employees"
           Dim cmNorthwind As New SqlCommand(stSQL, cnNorthwind)
           Dim daNorthwind As New SqlDataAdapter(cmNorthwind)
           'sqlCommandBuilder will generate the insert, update and delete command of the DataAdapter
           Dim cmbNorthwind As New SqlCommandBuilder(daNorthwind)
           Dim dsNorthwind As New DataSet()
           Dim trNorthwind As SqlTransaction
           daNorthwind.Fill(dsNorthwind, "Employees")
   
           cnNorthwind.Open()
           
   
   
           ' Add you code to add or change a row(s) here
           'Dim dr As DataRow
           'dr = dsNorthwind.Tables(0).NewRow
           'dr(1) = "John"
           'dr(2) = "Doe"
           'dsNorthwind.Tables(0).Rows.Add(dr)
   
           Dim dsNorthwind2 As New DataSet()
           dsNorthwind2 = dsNorthwind.GetChanges
           trNorthwind = cnNorthwind.BeginTransaction(IsolationLevel.ReadCommitted)
   
           daNorthwind.SelectCommand.Transaction = trNorthwind
   
           Try
               daNorthwind.Update(dsNorthwind2, "Employees")
               trNorthwind.Commit()
               ' if you expect ds2 to be modified by the Update process
               dsNorthwind.Merge(dsNorthwind2, False)
   
               MsgBox("Record(s) add or changed")
           Catch ex As Exception
               Try
                   trNorthwind.Rollback()
                   MsgBox("An error accured and all changes have been Rollback")
               Catch TransacationEx As Exception
                   'Handle Exception
   
               End Try
   
           End Try
   					

4. Modify the parameter of the connection string property of the SqlConnection object as necessary for your environment.
5. Run the code, and note that the changes have been rolled back. You can uncomment the code that adds a DataRow to see the transaction committed.
6. You can add a DataGrid control and code to bind the grid to your DataSet to view records, to make changes, or to add records to the DataSet.

For more information, visit the following Microsoft Developer Network (MSDN) Web sites: