Walkthrough: Saving Data to a Database (Multiple Tables)

One of the most common scenarios in application development is to display data on a form in a Windows application, edit the data, and send the updated data back to the database. This walkthrough creates a form that displays data from two related tables and shows how to edit records and save changes back to the database. This example uses the Customers and Orders tables from the Northwind sample database.

You can save data in your application back to the database by calling the Update method of a TableAdapter. When you drag items from the Data Sources window, code to save data is automatically added for the first table dropped onto a form. Any additional tables added to a form require the manual addition of any code required to save data. This walkthrough shows how to add code to save updates from more than one table.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

Tasks illustrated in this walkthrough include:

• Creating a new Windows Application project.

• Creating and configuring a data source in your application with the Data Source Configuration Wizard.

• Setting the controls of the items in the Data Sources Window. For more information, see How to: Set the Control to be Created when Dragging from the Data Sources Window.

• Creating data-bound controls by dragging items from the Data Sources window onto your form.

• Modifying a couple records in each table in the dataset.

• Modifying the code to send the updated data in the dataset back to the database.

Prerequisites

In order to complete this walkthrough, you will need:

• Access to the Northwind sample database. For more information, see How to: Install Sample Databases.

Creating the Windows Application

The first step is to create a Windows Application. Assigning a name to the project is optional at this step but we will give it a name because we are planning on saving it later.

To create the new Windows Application project

1. From the File menu, create a new project.

2. Name the project UpdateMultipleTablesWalkthrough.

3. Select Windows Application and click OK. For more information, see Creating Windows-Based Applications.

   The UpdateMultipleTablesWalkthrough project is created and added to Solution Explorer.

Creating the Data Source

This step creates a data source from the Northwind database using the Data Source Configuration Wizard. You must have access to the Northwind sample database to create the connection. For information on setting up the Northwind sample database, see How to: Install Sample Databases.

To create the data source

1. On the Data menu, click Show Data Sources.

2. In the Data Sources window, click Add New Data Source to start the Data Source Configuration Wizard.

3. Select Database on the Choose a Data Source Type page, and then click Next.

4. On the Choose your Data Connection page do one of the following:

   • If a data connection to the Northwind sample database is available in the drop-down list, select it.

     -or-

   • Select New Connection to open the Add/Modify Connection dialog box. For more information, see Add/Modify Connection Dialog Box (General).

5. If your database requires a password, select the option to include sensitive data, and then click Next.

6. Click Next on the Save connection string to the Application Configuration file page.

7. Expand the Tables node on the Choose your Database Objects page.

8. Select the Customers and Orders tables, and then click Finish.

   The NorthwindDataSet is added to your project and the tables appear in the Data Sources window.

Setting the Controls to be Created

For this walkthrough the data in the Customers table will be in a Details layout where data is displayed in individual controls. The data from the Orders table will be in a Grid layout displayed in a DataGridView control.

To set the drop type for the items in the Data Sources Window

1. Expand the Customers node in the Data Sources window.

2. Change the control of the Customers table to individual controls by selecting Details from the control list on the Customers node. For more information, see How to: Set the Control to be Created when Dragging from the Data Sources Window.

Creating the Data-bound Form

You can create the data-bound controls by dragging items from the Data Sources window onto your form.

To create data-bound controls on the form

1. Drag the main Customers node from the Data Sources window onto Form1.

   Data-bound controls with descriptive labels appear on the form, along with a tool strip (BindingNavigator) for navigating records. A NorthwindDataSet, CustomersTableAdapter, BindingSource, and BindingNavigator appear in the component tray.

2. Drag the related Orders node from the Data Sources window onto Form1.

   Note

   The related Orders node is located below the Fax column and is a child node of the Customers node.

   A DataGridView control and a tool strip (BindingNavigator) for navigating records appear on the form. An OrdersTableAdapter and BindingSource appear in the component tray.

Adding Code to Update the Database

You can update the database by calling the Update methods of the Customers and Orders TableAdapters. By default, an event handler for the BindingNavigator's Save button is added to the form's code to send updates to the database. This procedure modifies that code to send updates in the proper order to eliminate the possibility of raising referential integrity errors. The code also implements error handling by wrapping the update call in a try-catch block. You can modify the code to suit the needs of your application.

Note

For clarity this walkthrough does not use a transaction, but 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).

To add update logic to the application

1. Double-click the Save button on the BindingNavigator to open the Code Editor to the bindingNavigatorSaveItem_Click event handler.

2. Replace the code in the event handler to call the Update methods of the related TableAdapters. The following code first creates three temporary data tables to hold the updated information for each DataRowState (Deleted, Added, and Modified). Then updates are executed in the proper order. The code should look like the following:

   Me.Validate()
   Me.OrdersBindingSource.EndEdit()
   Me.CustomersBindingSource.EndEdit()
   
   Dim deletedOrders As NorthwindDataSet.OrdersDataTable = CType(
       NorthwindDataSet.Orders.GetChanges(Data.DataRowState.Deleted), NorthwindDataSet.OrdersDataTable)
   
   Dim newOrders As NorthwindDataSet.OrdersDataTable = CType(
       NorthwindDataSet.Orders.GetChanges(Data.DataRowState.Added), NorthwindDataSet.OrdersDataTable)
   
   Dim modifiedOrders As NorthwindDataSet.OrdersDataTable = CType(
       NorthwindDataSet.Orders.GetChanges(Data.DataRowState.Modified), NorthwindDataSet.OrdersDataTable)
   
   
   Try
       ' Remove all deleted orders from the Orders table.
       If Not deletedOrders Is Nothing Then
           OrdersTableAdapter.Update(deletedOrders)
       End If
   
       ' Update the Customers table.
       CustomersTableAdapter.Update(NorthwindDataSet.Customers)
   
       ' Add new orders to the Orders table.
       If Not newOrders Is Nothing Then
           OrdersTableAdapter.Update(newOrders)
       End If
   
       ' Update all modified Orders.
       If Not modifiedOrders Is Nothing Then
           OrdersTableAdapter.Update(modifiedOrders)
       End If
   
       NorthwindDataSet.AcceptChanges()
   
   Catch ex As Exception
       MsgBox("Update failed")
   
   Finally
       If Not deletedOrders Is Nothing Then
           deletedOrders.Dispose()
       End If
   
       If Not newOrders Is Nothing Then
           newOrders.Dispose()
       End If
   
       If Not modifiedOrders Is Nothing Then
           modifiedOrders.Dispose()
       End If
   End Try
   

   this.Validate();
   this.ordersBindingSource.EndEdit();
   this.customersBindingSource.EndEdit();
   
   NorthwindDataSet.OrdersDataTable deletedOrders = (NorthwindDataSet.OrdersDataTable)
       northwindDataSet.Orders.GetChanges(DataRowState.Deleted);
   
   NorthwindDataSet.OrdersDataTable newOrders = (NorthwindDataSet.OrdersDataTable)
       northwindDataSet.Orders.GetChanges(DataRowState.Added);
   
   NorthwindDataSet.OrdersDataTable modifiedOrders = (NorthwindDataSet.OrdersDataTable)
       northwindDataSet.Orders.GetChanges(DataRowState.Modified);
   
   try
   {
       // Remove all deleted orders from the Orders table.
       if (deletedOrders != null)
       {
           ordersTableAdapter.Update(deletedOrders);
       }
   
       // Update the Customers table.
       customersTableAdapter.Update(northwindDataSet.Customers);
   
       // Add new orders to the Orders table.
       if (newOrders != null)
       {
           ordersTableAdapter.Update(newOrders);
       }
   
       // Update all modified Orders.
       if (modifiedOrders != null)
       {
           ordersTableAdapter.Update(modifiedOrders);
       }
   
       northwindDataSet.AcceptChanges();
   }
   
   catch (System.Exception ex)
   {
       MessageBox.Show("Update failed");
   }
   
   finally
   {
       if (deletedOrders != null)
       {
           deletedOrders.Dispose();
       }
       if (newOrders != null)
       {
           newOrders.Dispose();
       }
       if (modifiedOrders != null)
       {
           modifiedOrders.Dispose();
       }
   }
   

Testing the Application

To test the application

1. Press F5.

2. Make some changes to the data of one or more records in each table.

3. Press the Save button.

4. Check the values in the database to verify that the changes were saved.

   Note

   For example code that demonstrates how to update the list of Orders when you change Customers or add new Customer and Orders data, see How to: Insert New Records into a Database and Walkthrough: Saving Data from Related Data Tables (Hierarchical Update).

Next Steps

Depending on your application requirements, there are several steps you may want to perform after creating a data-bound form in your Windows application. Some enhancements you could make to this walkthrough include:

• Adding search functionality to the form. For more information, see How to: Add a Parameterized Query to a Windows Forms Application.

• Editing the data source to add or remove database objects. For more information, see How to: Edit a Dataset.

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

Overview of Data Applications in Visual Studio

Connecting to Data in Visual Studio

Change History

Date	History	Reason
May 2011	Added links to additional sample code.	Customer feedback.