Switch View

How to: Update Data Using a TableAd...

This page is specific to

Microsoft Visual Studio 2005/.NET Framework 2.0

Other versions are also available for the following:

Data Access in Client and Middle-Tier Programming

How to: Update Data 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.

Note
Because attempting to update a data source with the contents of a dataset can result in errors, you should place the code that calls the adapter's `Update` method inside of a try/catch block.

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

Call the adapter's Update method within a try/catch block.
If an exception is caught, locate the data row that caused the error. For more information, see How to: Locate Rows that Have Errors.
Reconcile the problem in the data row (programmatically if possible, 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.

To update a database with a dataset 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.

Visual Basic

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");
}

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

Updating Two Related Tables in a Dataset with a TableAdapter

When updating related tables in a dataset, it is important to update in the proper 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:

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

Note
If you are updating two or more related tables, then you should include all the update logic within a transaction. A transaction is a process that assures all related changes to a database are successful before committing any changes. For more information see, Performing Transactions.

To update two related tables using a TableAdapter

Create three temporary data tables to hold the differing records.
Call the Update method for each subset of rows from within a try/catch block. If update errors occur, you should branch off and resolve them.
Commit the changes to the database.

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.

Visual Basic

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();
        }
    }
}

void UpdateDB()
{
    NorthwindDataSet.OrdersDataTable deletedChildRecords =
        (NorthwindDataSet.OrdersDataTable)northwindDataSet.get_Orders().GetChanges(DataRowState.Deleted);

    NorthwindDataSet.OrdersDataTable newChildRecords =
        (NorthwindDataSet.OrdersDataTable)northwindDataSet.get_Orders().GetChanges(DataRowState.Added);

    NorthwindDataSet.OrdersDataTable modifiedChildRecords =
        (NorthwindDataSet.OrdersDataTable)northwindDataSet.get_Orders().GetChanges(DataRowState.Modified);

    try
    {
        if (deletedChildRecords != null)
        {
            northwindDataSetOrdersTableAdapter.Update(deletedChildRecords);
        }

        northwindDataSetCustomersTableAdapter.Update(northwindDataSet.get_Customers());

        if (newChildRecords != null)
        {
            northwindDataSetOrdersTableAdapter.Update(newChildRecords);
        }

        if (modifiedChildRecords != null)
        {
            northwindDataSetOrdersTableAdapter.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();
        }
    }
}

Saving Data to a Database

To update a database with a dataset using a TableAdapter

Updating Two Related Tables in a Dataset with a TableAdapter

To update two related tables using a TableAdapter

See Also

Concepts

Other Resources