Walkthrough: Saving Data from Related Data Tables (Hierarchical Update)

Saving the data in an application back to the database is fairly simple when you are working with a single table of data and no foreign-key constraints need to be considered. But when you need to save data from a dataset that contains two or more related data tables, you must send the changes to the database in a specific order so that constraints are not violated. When you update modified data in related tables, you can provide the programmatic logic to extract the specific subsets of data from each data table and send the updates to the database in the correct order, or you can use the TableAdapterManager component.

This walkthrough shows how to save the related data by using the TableAdapterManager component. For information about coding related data table updates manually see, Walkthrough: Saving Data to a Database (Multiple Tables).

Prerequisites

To complete this walkthrough, you need:

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

Creating the Windows-based Application

The first step in this walkthrough is to create a new Windows-based application.

To create the new Windows-based application

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

   Note

   Hierarchical Update is supported in Visual Basic and C# projects, so create the new project in one of these languages.

2. Name the project HierarchicalUpdateWalkthrough.

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

   The HierarchicalUpdateWalkthrough project is created and added to Solution Explorer.

Creating the Dataset

Because you need related tables to demonstrate the hierarchical updates, the next step is to create a dataset that contains the Customers and Orders tables from the Northwind database. Create the dataset by using the Data Source Configuration Wizard. You must have access to the Northwind sample database to create the connection. For information about how to set up the Northwind sample database, see How to: Install Sample Databases.

To create the dataset

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. On the Choose a Data Source Type page, click Database and then click Next.

4. On the Choose Your Data Connection page, perform one of the following actions:

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

     -or-

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

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

6. Click Next on the Save the Connection String to the Application Configuration File page.

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

8. Click the check boxes for the Customers and Orders tables, and then click Finish.

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

Changing the Default Data-Bound Controls to Be Created

After populating the Data Sources window, you can choose the controls to be created when you drag items to a Windows Form. For this walkthrough, the data from the Customers table will be displayed in individual controls (Details). The data from the Orders table will be displayed in a DataGridView control (DataGridView).

To set the control for the items in the Data Sources window

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

2. Change the controls to be created for the Customers table to individual controls by clicking Details in 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.

   Note

   The Orders table will use the default control, the DataGridView.

Creating the Data-bound Form

After choosing the controls in the Data Sources window, create the data-bound controls by dragging items onto the form.

To create data-bound controls for the Customers and Orders data

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

   Data-bound controls with descriptive labels appear on the form, together with a TableAdapterManager component, a toolbar (BindingNavigator) for navigating records. A typed DataSet, TableAdapter, and a BindingSource, 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 underneath the Fax node of the customers table and is a child node of the Customers node. The Orders node that appears as a peer to the Customers node represents all orders in the table. The Orders node that appears as a child node of the Customers node represents the related orders.

   A DataGridView control and a toolbar (BindingNavigator) for navigating records appear on the form. A TableAdapter and a BindingSource appear in the component tray.

Modifying the Generated Save Code to Perform the Hierarchical Update

Save changes from the related data tables in the dataset to the database by calling the TableAdapterManager.UpdateAll method and passing in the name of the dataset that contains the related tables. For example, run the TableAdapterManager.UpdateAll(NorthwindDataset) method to send updates from all the tables in NorthwindDataset to the back-end database.

After you drop the items from the Data Sources window, code is automatically added to the Form_Load event to populate each table (the TableAdapter.Fill methods). Code is also added to the Save button click event of the BindingNavigator to save data from the dataset back to the database (the TableAdapterManager.UpdateAll method).

The generated save code also contains a line of code that calls the CustomersBindingSource.EndEdit method. More specifically, it calls the EndEdit method of the first BindingSource added to the form. In other words, this code is only generated for the first table dragged from the Data Sources window onto the form. The EndEdit call commits any changes that are in process in any data-bound controls that are currently being edited. Therefore, if a data-bound control still has focus and you click the Save button, all pending edits in that control are committed before the actual save (the TableAdapterManager.UpdateAll method).

Note

The designer only adds the BindingSource.EndEdit code for the first table dropped onto the form. Therefore, you have to add a line of code to call the BindingSource.EndEdit method for each related table on the form. For this walkthrough, this means you have to add a call to the OrdersBindingSource.EndEdit method.

To update the code to commit changes to the related tables before saving

1. Double-click the Save button on the BindingNavigator to open Form1 in the Code Editor.

2. Add a line of code to call the OrdersBindingSource.EndEdit method after the line that calls the CustomersBindingSource.EndEdit method. The code in the Save button click event should resemble the following:

   Me.Validate()
   Me.CustomersBindingSource.EndEdit()
   Me.OrdersBindingSource.EndEdit()
   Me.TableAdapterManager.UpdateAll(Me.NorthwindDataSet)
   

   this.Validate();
   this.customersBindingSource.EndEdit();
   this.ordersBindingSource.EndEdit();
   this.tableAdapterManager.UpdateAll(this.northwindDataSet);
   

In addition to committing changes on a related child table before saving data to a database, you might also have to commit newly created parent records before adding new child records to a dataset. In other words, you might have to add the new parent record (Customer) to the dataset before foreign key constraints enable new child records (Orders) to be added to the dataset. To accomplish this, you can use the child BindingSource.AddingNew event.

Note

You may or may not have to commit new parent records; it depends on the type of control that is used to bind to your data source. In this walkthrough, you use individual controls to bind to the parent table; this requires the additional code to commit the new parent record. If the parent records were displayed in a complex binding control like the DataGridView, this additional EndEdit call for the parent record would not be necessary. This is because the underlying data-binding functionality of the control handles the committing of the new records.

To add code to commit parent records in the dataset before adding new child records

1. Create an event handler for the OrdersBindingSource.AddingNew event.

   • Open Form1 in design view, click OrdersBindingSource in the component tray, select Events in the Properties window, and then double-click the AddingNew event.

2. Add to the event handler a line of code that calls the CustomersBindingSource.EndEdit method. The code in the OrdersBindingSource_AddingNew event handler should resemble the following:

   Me.CustomersBindingSource.EndEdit()
   

   this.customersBindingSource.EndEdit();
   

Verifying That Hierarchical Updates are Enabled

Hierarchical updates are turned on and off by setting the Hierarchical Update property of the dataset. Hierarchical updates are enabled by default, so for this walkthrough you do not have to change the value of the Hierarchical Update property.

To verify that hierarchical updates are enabled

1. Open the dataset in the Dataset Designer by double-clicking the NorthwindDataSet.xsd file in Solution Explorer.

2. Select an empty area on the design surface.

3. Locate the Hierarchical Update property in the Properties Window and verify that it is set to True.

   Note

   The Hierarchical Update property setting is what controls whether code is generated with a TableAdapterManager and the logic to perform hierarchical updates or not. Setting HierarchicalUpdate to True generates a TableAdapterManager; setting HierarchicalUpdate to False does not generate a TableAdapterManager.

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. Add a new customer and then add a new order for that customer.

4. Click the Save button. The TableAdapterManager handles the logic required for all related updates.

5. Check the values in the database to verify that the changes were saved in each table.

Next Steps

Depending on your application requirements, there are several steps that you may want to perform after you save related data in the Windows-based application. Some enhancements you could make to this application include the following:

• Adding a third table, such as the OrderDetails table, and experimenting with a three-table hierarchy.

• Adding validation code to verify that data meets application requirements in addition to database constraints. For more information, see Validating Data.

See Also

Tasks

How to: Configure Foreign-Key Constraints in a Dataset

How to: Set the Order When Performing a Hierarchical Update

How to: Commit In-Process Edits on Data-Bound Controls Before Saving Data

How to: Implement Hierarchical Update in Existing Visual Studio Projects

Walkthrough: Saving Data from Related Data Tables (Hierarchical Update)

Concepts

Saving Data

Other Resources

Hierarchical Update

DataSets, DataTables, and DataViews (ADO.NET)