Share via


How to: Save Dataset Changes to a Database

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 or data adapter. The adapter's Update method updates a single data table and executes the correct command (INSERT, UPDATE, or DELETE) based on the RowState of each data row in the table.

When saving data in related tables, Visual Studio provides a TableAdapterManager component that assists in performing saves in the proper order based on the foreign-key constraints defined in the database. For more information, see Hierarchical Update Overview.

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:

  1. Execute code that attempts sending updates to the database within 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 possible, or by presenting the invalid row to the user for modification), and then reattempt the update (HasErrors property, GetErrors method).

Saving Data to a Database

Call the Update method of a TableAdapter or data adapter, passing the name of the data table that contains the values to be written to the database. For more information on saving data from a single data table back to a database, see Walkthrough: Saving Data to a Database (Single Table).

To update a database with a dataset using a TableAdapter

  • Enclose the TableAdapter.Update method inside a try/catch block. The following example shows how to attempt an update with the contents of the Customers table in the 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");
    }
    

To update a database with a dataset using a data adapter

  • Enclose the DataAdapter.Update method inside a try/catch block. The following example shows how to attempt an update to a data source with the contents of Table1 in DataSet1.

    Try
        SqlDataAdapter1.Update(Dataset1.Tables("Table1"))
    
    Catch x As Exception
        ' Error during Update, add code to locate error, reconcile 
        ' and try to update again.
    End Try
    
    try
    {
        SqlDataAdapter1.Update(Dataset1.Tables["Table1"]);
    }
    catch (Exception e)
    {
        // Error during Update, add code to locate error, reconcile 
        // and try to update again.
    }
    

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

For detailed information on saving data from multiple tables, see Walkthrough: Saving Data to a Database (Multiple Tables).

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, Transactions and Concurrency (ADO.NET).

  1. Create three temporary DataTables to hold the differing records.

  2. Call the Update method 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.

  3. Commit the changes from the dataset to the database.

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

    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();
            }
        }
    }
    
  • 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 data tables to release the resources.

    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)
            End If
    
            daCustomers.Update(dsNorthwind1, "Customers")
    
            If Not NewChildRecords Is Nothing Then
                daOrders.Update(NewChildRecords)
            End If
    
            If Not ModifiedChildRecords Is Nothing Then
                daOrders.Update(ModifiedChildRecords)
            End If
    
            dsNorthwind1.AcceptChanges()
    
        Catch ex As Exception
            ' Update error, resolve and try again
    
        Finally
            If Not DeletedChildRecords Is Nothing Then
                DeletedChildRecords.Dispose()
            End If
    
            If Not NewChildRecords Is Nothing Then
                NewChildRecords.Dispose()
            End If
    
            If Not ModifiedChildRecords Is Nothing Then
                ModifiedChildRecords.Dispose()
            End If
        End Try
    End Sub
    
    void UpdateDB()
    {
        System.Data.DataTable DeletedChildRecords = 
            dsNorthwind1.Orders.GetChanges(System.Data.DataRowState.Deleted);
    
        System.Data.DataTable NewChildRecords = 
            dsNorthwind1.Orders.GetChanges(System.Data.DataRowState.Added);
    
        System.Data.DataTable ModifiedChildRecords = 
            dsNorthwind1.Orders.GetChanges(System.Data.DataRowState.Modified);
    
        try
        {
            if (DeletedChildRecords != null)
            {
                daOrders.Update(DeletedChildRecords);
            }
            if (NewChildRecords != null)
            {
                daOrders.Update(NewChildRecords);
            }
            if (ModifiedChildRecords != null)
            {
                daOrders.Update(ModifiedChildRecords);
            }
    
            dsNorthwind1.AcceptChanges();
        }
    
        catch (Exception ex)
        {
            // Update error, resolve and try again
        }
    
        finally
        {
            if (DeletedChildRecords != null)
            {
                DeletedChildRecords.Dispose();
            }
            if (NewChildRecords != null)
            {
                NewChildRecords.Dispose();
            }
            if (ModifiedChildRecords != null)
            {
                ModifiedChildRecords.Dispose();
            }
        }
    }
    

See Also

Concepts

What's New in Data Application Development

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