How to: Update Data by Using a TableAdapter

After the data in your dataset has been modified and validated, you probably want to send the updated data back to a database. In order to send the modified data to a database, you call the Update method of a TableAdapter. The adapter's Update method will update a single data table and execute the correct command (INSERT, UPDATE, or DELETE) based on the RowState of each data row in the table. When you save data in related tables, Visual Studio provides a TableAdapterManager component that assists in performing saves in the correct order based on the foreign-key constraints defined in the database. For more information, see Hierarchical Update Overview.

Note

Because trying to update a data source with the contents of a dataset can cause errors, you should put the code that calls the adapter's Update method inside a try/catch block.

The exact procedure to update a data source can vary depending on business needs, but your application should include the following steps:

1. Call the adapter's Update method in a try/catch block.

2. If an exception is caught, locate the data row that caused the error. For more information, see How to: Locate Rows that Have Errors.

3. Reconcile the problem in the data row (programmatically if you can, or by presenting the invalid row to the user for modification), and then reattempt the update (HasErrors, GetErrors).

Saving Data to a Database

Call the Update method of a TableAdapter, passing the name of the data table that contains the values to be written to the database.

Important

When you use a local database, such as an .mdf file, the Copy to Output property of the file must not be set to Copy Always. If the property is set to Copy Always, when you build the project, the file will overwrite any changes that you make to the local database. To correct this issue, right-click the file in Solution Explorer, click Properties, and change the value of Copy to Output.

To update a database that has a dataset by using a TableAdapter

• Enclose the adapter's Update method in a try/catch block. The following example shows how to attempt an update from within a try/catch block with the contents of the Customers table in NorthwindDataSet.

  Try
      Me.Validate()
      Me.CustomersBindingSource.EndEdit()
      Me.CustomersTableAdapter.Update(Me.NorthwindDataSet.Customers)
      MsgBox("Update successful")
  
  Catch ex As Exception
      MsgBox("Update failed")
  End Try
  

  try
  {
      this.Validate();
      this.customersBindingSource.EndEdit();
      this.customersTableAdapter.Update(this.northwindDataSet.Customers);
      MessageBox.Show("Update successful");
  }
  catch (System.Exception ex)
  {
      MessageBox.Show("Update failed");
  }
  

Updating Two Related Tables in a Dataset with a TableAdapter

When you update related tables in a dataset, you must update in the correct sequence in order to reduce the chance of violating referential integrity constraints. The order of command execution will also follow the indices of the DataRowCollection in the dataset. To prevent data integrity errors from being raised, the best practice is to update the database in the following sequence:

1. Child table: delete records.

2. Parent table: insert, update, and delete records.

3. Child table: insert and update records.

   Note

   If you are updating two or more related tables, you should include all the update logic within a transaction. A transaction is a process that ensures all related changes to a database are successful before committing any changes. For more information, see Transactions and Concurrency (ADO.NET).

To update two related tables using a TableAdapter

1. Create three temporary data tables to hold the differing records.

2. Call the Update method for each subset of rows from a try/catch block. If update errors occur, you should branch off and resolve them.

3. Commit the changes to the database.

4. Dispose of the temporary data tables to release the resources.

   The following example shows how to update a data source with a dataset that contains related tables.

   Private Sub UpdateDB()
       Dim deletedChildRecords As NorthwindDataSet.OrdersDataTable =
           CType(NorthwindDataSet.Orders.GetChanges(Data.DataRowState.Deleted), NorthwindDataSet.OrdersDataTable)
   
       Dim newChildRecords As NorthwindDataSet.OrdersDataTable =
           CType(NorthwindDataSet.Orders.GetChanges(Data.DataRowState.Added), NorthwindDataSet.OrdersDataTable)
   
       Dim modifiedChildRecords As NorthwindDataSet.OrdersDataTable =
           CType(NorthwindDataSet.Orders.GetChanges(Data.DataRowState.Modified), NorthwindDataSet.OrdersDataTable)
   
       Try
           If deletedChildRecords IsNot Nothing Then
               OrdersTableAdapter.Update(deletedChildRecords)
           End If
   
           CustomersTableAdapter.Update(NorthwindDataSet.Customers)
   
           If newChildRecords IsNot Nothing Then
               OrdersTableAdapter.Update(newChildRecords)
           End If
   
           If modifiedChildRecords IsNot Nothing Then
               OrdersTableAdapter.Update(modifiedChildRecords)
           End If
   
           NorthwindDataSet.AcceptChanges()
   
       Catch ex As Exception
           MessageBox.Show("An error occurred during the update process")
           ' Add code to handle error here.
   
       Finally
           If deletedChildRecords IsNot Nothing Then
               deletedChildRecords.Dispose()
           End If
   
           If newChildRecords IsNot Nothing Then
               newChildRecords.Dispose()
           End If
   
           If modifiedChildRecords IsNot Nothing Then
               modifiedChildRecords.Dispose()
           End If
   
       End Try
   End Sub
   

   void UpdateDB()
   {
       NorthwindDataSet.OrdersDataTable deletedChildRecords = 
           (NorthwindDataSet.OrdersDataTable)northwindDataSet.Orders.GetChanges(DataRowState.Deleted);
   
       NorthwindDataSet.OrdersDataTable newChildRecords = 
           (NorthwindDataSet.OrdersDataTable)northwindDataSet.Orders.GetChanges(DataRowState.Added);
   
       NorthwindDataSet.OrdersDataTable modifiedChildRecords = 
           (NorthwindDataSet.OrdersDataTable)northwindDataSet.Orders.GetChanges(DataRowState.Modified);
   
       try
       {
           if (deletedChildRecords != null)
           {
               ordersTableAdapter.Update(deletedChildRecords);
           }
   
           customersTableAdapter.Update(northwindDataSet.Customers);
   
           if (newChildRecords != null)
           {
               ordersTableAdapter.Update(newChildRecords);
           }
   
           if (modifiedChildRecords != null)
           {
               ordersTableAdapter.Update(modifiedChildRecords);
           }
   
           northwindDataSet.AcceptChanges();
       }
   
       catch (Exception ex)
       {
           MessageBox.Show("An error occurred during the update process");
           // Add code to handle error here.
       }
   
       finally
       {
           if (deletedChildRecords != null)
           {
               deletedChildRecords.Dispose();
           }
           if (newChildRecords != null)
           {
               newChildRecords.Dispose();
           }
           if (modifiedChildRecords != null)
           {
               modifiedChildRecords.Dispose();
           }
       }
   }
   

See Also

Concepts

TableAdapter Overview

Binding Windows Forms Controls to Data in Visual Studio

Preparing Your Application to Receive Data

Fetching Data into Your Application

Binding Controls to Data in Visual Studio

Editing Data in Your Application

Validating Data

Saving Data

Other Resources

Data Walkthroughs

Connecting to Data in Visual Studio

Change History

Date	History	Reason
April 2011	Addressed possible problems with the Copy to Output setting for a local database file.	Customer feedback.