O'Reilly logo

ADO.NET Cookbook by Bill Hamilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required