How to: Update Data by Using a TableAdapter

Switch View

How to: Update Data by Using a Tabl...

Collapse All

Language Filter

This page is specific to

Microsoft Visual Studio 2008/.NET Framework 3.5

Other versions are also available for the following:

Data Access in Client and Middle-Tier Programming

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 2008 provides a new 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:

Call the adapter's Update method in 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 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.

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.

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

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:

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, 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

Create three temporary data tables to hold the differing records.
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.
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();
        }
    }
}

Concepts

TableAdapter Overview

Displaying Data Overview

Other Resources

Data Walkthroughs

Connecting to Data in Visual Studio

Preparing Your Application to Receive Data

Fetching Data into Your Application

Displaying Data on Forms in Windows Applications

Editing Data in Your Application

Validating Data

Saving Data

Tags

: Add a tag

Community Content

Add new content

Annotations

TableAdapter does not seem to have a Transactional Context

AdrianG | Edit | Show History

There seems to be no reference to the updating of multiple tables within a single Transactional Context. It seems that TableAdapters only follow ACID rules within the context of a single table. If a DataSet with multiple tables exists, than a Transactional context cannot be given, resulting in table changes being stored as multiple transactions. This can result in only a sub-set of the table changes being saved.

Tags

: dataadapter (x) dataset (x) datatable (x) transaction (x) Add a tag

Flag as ContentBug

Possible problem with saving data to database

pat.kujawa | Edit | Show History

If you are having a problem saving the data you've entered into your TableAdapter's DataTable and you are using a local file database, check which file is being used as the database. I thought that I was accessing the .mdf file in my project directory, when it turns out that that file was being copied to the bin/Debug folder every time I ran the program in debug mode. This action was overwriting everything that I had thought was being saved to the database. There is a property setting in Solution Explorer when the database file is selected: Copy to Output Directory. If this property is set to Copy Always, as mine was by default, you will overwrite the database every time you run your program.

Tags

: database (x) fix (x) howto (x) tableadapter (x) Add a tag

Flag as ContentBug

Good article to read with this one

vbjay | Edit | Show History

http://www.bokebb.com/dev/english/1931/posts/19313621.shtml

Tags

: Add a tag

Flag as ContentBug

How do I include a form in the install process?

gadsbywc | Edit | Show History

I am creating an app that when installed it will allow a Justice of the Peace to only access His/Her records. I created a table with two fields: "JOPId" and "UserId". I want to assign these feilds while installing the software. I created a form that allows me to set the data, I want to call this form only during the install process, after the tables are installed. Then when the judge opens the app, they only get their records.

Tags

: Add a tag

Flag as ContentBug