Create relationships between datasets in .NET Framework applications
Note
Datasets and related classes are legacy .NET Framework technologies from the early 2000s that enable applications to work with data in memory while the applications are disconnected from the database. The technologies are especially useful for applications that enable users to modify data and persist the changes back to the database. Although datasets have proven to be a very successful technology, we recommend that new .NET applications use Entity Framework Core. Entity Framework provides a more natural way to work with tabular data as object models, and it has a simpler programming interface.
Datasets that contain related data tables use DataRelation objects to represent a parent/child relationship between the tables and to return related records from one another. Adding related tables to datasets by using the Data Source Configuration Wizard, or the Dataset Designer, creates and configures the DataRelation object for you.
The DataRelation object performs two functions:
It can make available the records related to a record you are working with. It provides child records if you are in a parent record (GetChildRows) and a parent record if you are working with a child record (GetParentRow).
It can enforce constraints for referential integrity, such as deleting related child records when you delete a parent record.
It is important to understand the difference between a true join and the function of a DataRelation object. In a true join, records are taken from parent and child tables and put into a single, flat recordset. When you use a DataRelation object, no new recordset is created. Instead, the DataRelation tracks the relationship between tables and keeps parent and child records in sync.
DataRelation objects and constraints
A DataRelation object is also used to create and enforce the following constraints:
A unique constraint, which guarantees that a column in the table contains no duplicates.
A foreign-key constraint, which can be used to maintain referential integrity between a parent and child table in a dataset.
Constraints that you specify in a DataRelation object are implemented by automatically creating appropriate objects or setting properties. If you create a foreign-key constraint by using the DataRelation object, instances of the ForeignKeyConstraint class are added to the DataRelation object's ChildKeyConstraint property.
A unique constraint is implemented either by simply setting the Unique property of a data column to true or by adding an instance of the UniqueConstraint class to the DataRelation object's ParentKeyConstraint property. For information on suspending constraints in a dataset, see Turn off constraints while filling a dataset.
Referential integrity rules
As part of the foreign-key constraint, you can specify referential integrity rules that are applied at three points:
When a parent record is updated
When a parent record is deleted
When a change is accepted or rejected
The rules that you can make are specified in the Rule enumeration and are listed in the following table.
| Foreign-key constraint rule | Action |
|---|---|
| Cascade | The change (update or delete) made to the parent record is also made in related records in the child table. |
| SetNull | Child records are not deleted, but the foreign key in the child records is set to DBNull. With this setting, child records can be left as "orphans"—that is, they have no relationship to parent records. Note: Using this rule can result in invalid data in the child table. |
| SetDefault | The foreign key in the related child records is set to its default value (as established by the column's DefaultValue property). |
| None | No change is made to related child records. With this setting, child records can contain references to invalid parent records. |
For more information about updates in dataset tables, see Save data back to the database.
Constraint-only relations
When you create a DataRelation object, you have the option of specifying that the relation be used only to enforce constraints—that is, it will not also be used to access related records. You can use this option to generate a dataset that is slightly more efficient and that contains fewer methods than one with the related-records capability. However, you will not be able to access related records. For example, a constraint-only relation prevents you from deleting a parent record that still has child records, and you cannot access the child records through the parent.
Manually creating a data relation in the Dataset Designer
When you create data tables by using the data design tools in Visual Studio, relationships are created automatically if the information can be gathered from the source of your data. If you manually add data tables from the DataSet tab of the Toolbox, you might have to create the relationship manually. For information on creating DataRelation objects programmatically, see Adding DataRelations.
Relationships between data tables appear as lines in the Dataset Designer, with a key and infinity glyph depicting the one-to-many aspect of the relationship. By default, the name of the relationship does not appear on the design surface.
Note
Your computer might show different names or locations for some of the Visual Studio user interface elements in this article. You might be using a different edition of Visual Studio or different environment settings. For more information, see Personalize the IDE.
To create a relationship between two data tables
Open your dataset in the Dataset Designer. For more information, see Walkthrough: Creating a Dataset in the Dataset Designer.
Drag a Relation object from the DataSet toolbox onto the child data table in the relationship.
The Relation dialog box opens, populating the Child Table box with the table that you dragged the Relation object onto.
Select the parent table from the Parent Table box. The parent table contains records on the "one" side of a one-to-many relationship.
Verify that the correct child table is displayed in the Child Table box. The child table contains records on the "many" side of a one-to-many relationship.
Type a name for the relationship in the Name box, or leave the default name based on the selected tables. This is the name of the actual DataRelation object in code.
Select the columns that join the tables in the Key Columns and Foreign Key Columns lists.
Select whether to create a relation, constraint, or both.
Select or clear the Nested Relation box. Selecting this option sets the Nested property to
true, and it causes the child rows of the relation to be nested within the parent column when those rows are written as XML data or synchronized with XmlDataDocument. For more information, see Nesting DataRelations.Set the rules to be enforced when you're making changes to records in these tables. For more information, see Rule.
Click OK to create the relationship. A relation line appears on the designer between the two tables.
To display a relation name in the Dataset Designer
Open your dataset in the Dataset Designer. For more information, see Walkthrough: Creating a Dataset in the Dataset Designer.
From the Data menu, select the Show Relation Labels command to display the relation name. Clear that command to hide the relation name.