HOW TO: Roll Back Updates After an Error When You Use DataAdapter and DataSet in ADO.NET and Visual C# .NET (316024)

SUMMARY

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.

back to the top

Steps to Build the Sample

1. Follow these steps to create a new Visual C# .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 C# Projects under Project Types, and then click Windows Application under Templates.
2. In the Form class, add the following line immediately after the using statements:

   using System.Data.SqlClient;
   					

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

            string stConnection = "user id=YourUserID;password=YourPassword;server=YourServer;database=Northwind";
         
            SqlConnection cnNorthwind = new SqlConnection(stConnection);
            SqlCommand cmNorthwind = new SqlCommand("select * from employees",cnNorthwind);
            SqlDataAdapter daNorthwind = new SqlDataAdapter(cmNorthwind);
            SqlCommandBuilder cb = new SqlCommandBuilder(daNorthwind);
            DataSet dsNorthwind = new DataSet();
            DataSet dsNorthwind2  = new DataSet();
            daNorthwind.Fill(dsNorthwind,"Employees");
            // Add code to change or add a record.
            // System.Data.DataRow dr;
            System.Data.DataRow dr  = dsNorthwind.Tables["Employees"].NewRow();
    
    
            dr[1] = "henry";
            dr[2] = "matthews";
            dsNorthwind.Tables["Employees"].Rows.Add(dr);
    
            dsNorthwind2 = dsNorthwind.GetChanges();
            cnNorthwind.Open();
    
                                 
            System.Data.SqlClient.SqlTransaction tr;
            tr = cnNorthwind.BeginTransaction(IsolationLevel.ReadCommitted);
            daNorthwind.SelectCommand.Transaction = tr;
            try
   	    {
   	    daNorthwind.Update(dsNorthwind2,"Employees");
   	    tr.Commit();
   	    dsNorthwind.Merge(dsNorthwind2);
   	    MessageBox.Show("Record(s) added or Updated");
   	    }
   	catch (System.Exception err)
               {
   	    try
   		{
   		MessageBox.Show (err.ToString());
   		tr.Rollback();
   		}
   	    catch (System.Exception TransactionEx)
   		{
   		//Handle Exception
   	       	}
   	   }
   					

4. Modify the parameter of the connection string property of the SqlConnection object as appropriate for your environment.
5. Add a DataGrid control and code to bind the grid to your DataSet if you want to view records, make changes, or add records to the DataSet.

back to the top