Hierarchical Update Overview
Hierarchical update refers to saving updated data from a dataset that has two or more related tables back to a database while maintaining referential integrity. Referential integrity refers to the consistency rules provided by the foreign-key constraints in a database that control the behavior of Inserting, Updating, and Deleting related records. For example, it is referential integrity that forces you to create a customer record before you create orders for that customer.
When developers created data applications in Visual Studio 2005 and earlier versions, automatically generated code saved changed data only to the first table dragged from the Data Sources window. When dragging additional tables from the Data Sources window, users had to manually add the code to save changes in those tables to the database. Not only were users required to add code to call the TableAdapter.Update method for each table, but logic had to be incorporated to perform the specific Inserts, Updates, and Deletes in the appropriate order for each table to prevent referential integrity violations. (For example, new customers must be saved before you add their orders, and orders must be deleted before you remove an existing customer.)
Starting in Visual Studio 2008, typed datasets are enhanced by a new TableAdapterManager component. TableAdapterManager reduces the code that is required for saving data in multiple related tables from several routines with many lines of code to a single method call: TableAdapterManager.UpdateAll(TypedDataset). The TableAdapterManager provides all the underlying logic that is required to maintain referential integrity during update (save) operations between the dataset and the database. For more information, see TableAdapterManager Overview.
Enabling Hierarchical Update in a Dataset
By default, hierarchical update is enabled for all new datasets that are added or created in a project. Turn hierarchical update on or off by setting the Hierarchical Update property of a typed dataset in the Dataset Designer to True or False. For detailed information, see How to: Enable and Disable Hierarchical Update.
Foreign-Key Constraints and Cascading Updates and Deletes
It is important to understand how foreign-key constraints and cascading behavior in the database are created in the generated dataset code.
By default, the data tables in a dataset are generated with relationships (DataRelation) that match the relationships in the database. However, the relationship in the dataset is not generated as a foreign-key constraint. The DataRelation is configured as Relation Only without an UpdateRule or DeleteRule in effect.
By default, cascading updates and cascading deletes are turned off even if the database relationship is set with cascading updates and/or cascading deletes turned on. For example, creating a new customer and a new order and then trying to save the data can cause a conflict with the foreign-key constraints defined in the database. For more information, see How to: Configure Foreign-Key Constraints in a Dataset.
Setting the Order to Perform Updates
Setting the order to perform updates sets the order of the individual Inserts, Updates, and Deletes required to save all the modified data in all tables of a dataset. When hierarchical update is enabled, Inserts are performed first, then Updates, and then Deletes. The TableAdapterManager provides an UpdateOrder property that can be set to perform Updates first, then Inserts, and then Deletes.
Note
It is important to understand that the update order is all inclusive. That is, when updates are performed, Inserts are performed for all tables in the dataset, then Updates are performed for all tables in the dataset, and then Deletes are performed for all tables in the dataset.
To set the UpdateOrder property, after dragging items from the Data Sources Window onto a form, click the TableAdapterManager in the component tray and set the UpdateOrder property in the Properties window. For more information, see How to: Set the Order When Performing a Hierarchical Update.
Creating a Backup Copy of a Dataset Before Performing a Hierarchical Update
When you save data (by calling the TableAdapterManager.UpdateAll() method), the TableAdapterManager attempts to update the data for each table in a single transaction. If any part of the update for any table fails, the whole transaction is rolled back. In most situations, rolling back the transaction returns your application to its original state. However, sometimes you might want to restore the dataset from the backup copy. One example is when you are using auto-increment values. For example, if a save operation is not successful, auto-increment values are not reset in the dataset, and the dataset will continue to create auto-incrementing values, leaving a gap in numbering that might not be acceptable in your application. In situations where this is an issue, the TableAdapterManager provides a BackupDataSetBeforeUpdate property that replaces the existing dataset with a backup copy if the transaction fails.
Note
The backup copy is only in memory during the execution of the TableAdapterManager.UpdateAll method. Therefore, there is no programmatic access to this backup dataset because it either replaces the original dataset or goes out of scope as soon as the TableAdapterManager.UpdateAll method has finished running.
See Also
Tasks
Walkthrough: Saving Data from Related Data Tables (Hierarchical Update)
Concepts
What's New for Data Application Development in Visual Studio 2012