Updating a Data Source with a Dataset

Switch View :

ScriptFree

Visual Basic and Visual C# Concepts

After your dataset has been modified and validated, it is likely that your application will want to send the new data back to a data source. In order to update a data source with the contents of a dataset, you call the Update method of a data adapter. The data adapter will execute the appropriate command (INSERT, UPDATE, or DELETE) based on the RowState of the row in the dataset.

Note Because attempting to update a data source with the contents of a dataset can result in errors, you should invoke the data adapters Update method from within 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:

Invoke the data adapters update method within a Try...Catch block.
If an exception is caught, locate the data row that caused the error. For more information, see Responding to Database Update Errors.
Reconcile the problem in the data row (programmatically if possible, or by presenting the erroneous row to the user for modification), and then reattempt the update.

To update a data source with a dataset

Call the Update method of a data adapter, passing the name of the dataset that contains the values to be written to the data source.

The following example shows how to attempt an update to a data source from within a Try...Catch block with the contents of myDataset.

' Visual Basic
Try
   oleDbDataadapter1.Update(myDataset)
Catch x As Exception
   ' Error during Update, add code to locate error, reconcile 
   ' and try to update again.
End Try

// C#
try
{
   oleDbDataAdapter1.Update(myDataset);
}
catch (Exception e)
{
   // Error during Update, add code to locate error, reconcile 
   // and try to update again.
}

Updating Related Tables in a Dataset

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

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

To update a data source with a dataset that contains two or more related tables

Call the Update method of each data adapter.

The following example shows how to update a data source with a dataset that contains related tables. In order to follow the above sequence, three temporary DataTables will be created to hold the differing records. Then the Update method will be called for each subset of rows from within a Try...Catch block. If update errors occur, the suggested course of action is to branch off and resolve them. Then the dataset commits the changes. Finally, dispose of the temporary datatables to release the resources.

' Visual Basic
Private Sub UpdateDB()
    Dim DeletedChildRecords As DataTable = _
        DsNorthwind1.Orders.GetChanges(DataRowState.Deleted)
    Dim NewChildRecords As DataTable = _
        DsNorthwind1.Orders.GetChanges(DataRowState.Added)
    Dim ModifiedChildRecords As DataTable = _
        DsNorthwind1.Orders.GetChanges(DataRowState.Modified)
    Try
        If Not DeletedChildRecords Is Nothing Then
            daOrders.Update(DeletedChildRecords)
            DeletedChildRecords.Dispose()
        End If 
        daCustomers.Update(DsNorthwind1, "Customers")
        If Not NewChildRecords Is Nothing Then
            daOrders.Update(NewChildRecords)
            NewChildRecords.Dispose()
        End If
        If Not ModifiedChildRecords Is Nothing Then
            daOrders.Update(ModifiedChildRecords)
            ModifiedChildRecords.Dispose()
        End If
        DsNorthwind1.AcceptChanges()
    Catch ex As Exception
        ' Update error, resolve and try again
    End Try
End Sub

// C#
void UpdateDB()
{
    DataTable DeletedChildRecords = dsNorthwind1.Orders.GetChanges(DataRowState.Deleted);
    DataTable NewChildRecords = dsNorthwind1.Orders.GetChanges(DataRowState.Added);
    DataTable ModifiedChildRecords = dsNorthwind1.Orders.GetChanges(DataRowState.Modified);
    try
    {
        if (DeletedChildRecords != null)
        {
            daOrders.Update(DeletedChildRecords);
            DeletedChildRecords.Dispose();
        }
        if (NewChildRecords != null)
        {
            daOrders.Update(NewChildRecords);
            NewChildRecords.Dispose();
        }
        if (ModifiedChildRecords != null)
        {
            daOrders.Update(ModiifiedChildRecords);
            ModifiedChildRecords.Dispose();
        }
        DsNorthwind1.AcceptChanges();
    }
    catch (Exception ex)
    {
        // Update error, resolve and try again
    }
}

Updating a Data Source with a Dataset

Updating Related Tables in a Dataset

See Also