ADO.NET Cookbook

10.11. Creating DataSet Relationships from SQL Server Relationships

Problem

You need to create relationships between DataTable objects within your DataSet at runtime based on the relationships that are defined in your SQL Server database.

Solution

Use INFORMATION_SCHEMA views and system tables to create relationships automatically at runtime.

The schema of table TBL1011a used in this solution is shown in Table 10-5.

Table 10-5. TBL1011a schema

Column name	Data type	Length	Allow nulls?
a	int	4	No
b	int	4	No
c	int	4	No

The schema of table TBL1011b used in this solution is shown in Table 10-6.

Table 10-6. TBL1011b schema

Column name	Data type	Length	Allow nulls?
d	int	4	No
e	int	4	No
a2	int	4	No
b2	int	4	No

The sample code creates a DataSet containing the Orders table and Order Details table from the Northwind sample database. The tables TBL1011a and TBL1011b—related through a multicolumn key—are also added to the DataSet. Next, the result set of a query of the INFORMATION_SCHEMA views are examined to determine the relationships specified in the data source between the tables in the DataSet. DataRelation objects are created in the DataSet for the identified relationships.

The C# code is shown in Example 10-11.

Example 10-11. File: AutoDataRelationForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Collections; using System.Text; using System.Data; using System.Data.SqlClient; // . . . DataSet ds = new DataSet( ...