ADO.NET 3.5 Cookbook, 2nd Edition

11.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

Two approaches are shown—one that uses catalog views and one that uses information schema views—to create relationships automatically at runtime between the tables ParentTable and ChildTable. The T-SQL DDL that creates these tables is shown in Example 11-11.

Example 11-11. Create tables ParentTable and ChildTable

USE AdoDotNet35Cookbook
GO
-- Create the parent table
CREATE TABLE ParentTable(
    a int NOT NULL,
    b int NOT NULL,
    c int NOT NULL,
    d int NOT NULL,
  CONSTRAINT PK_ParentTable PRIMARY KEY CLUSTERED 
  ( a ASC, b ASC )
)
GO
-- Create the child table
CREATE TABLE ChildTable(
    z int NOT NULL PRIMARY KEY,
    a int NOT NULL,
    b int NOT NULL,
)
GO
-- Create the relation between ParentTable and ChildTable
ALTER TABLE ChildTable 
WITH CHECK ADD CONSTRAINT FK_ChildTable_ParentTable FOREIGN KEY(a, b)
REFERENCES ParentTable (a, b)

The first solution uses a query of the catalog views to get the relationships between the tables in the DataSet. DataRelation objects are created in the DataSet for the identified relationships.

The C# code in Program.cs in the project CreateRelationsFromSqlServerCV is shown in Example 11-12.

Example 11-12. File: Program.cs for CreateRelationsFromSqlServerCV solution

using System; using System.Data; using System.Data.SqlClient; using ...