Creating a Data Relation in Code

This chapter's final example will relate the data in two tables of the pubs example database publishers and titles together using the shared pub_id field to create a master/detail data application. This example will display publishers' names in a list box as you see in Figure 10.6 (the "master" part). When the users select a publisher, the code will display that publisher's books in a data grid (the "detail" part). To do this, we'll create a DataRelation object in code.

Figure 10.6. The ch10_05 example.

Datasets just hold your data in tablesthey don't know anything about the relations between those tables. To set up those relations, you use DataRelation objects, which relate tables together using DataColumn objects. You can find the significant public properties of DataRelation objects in Table 10.29.

Table 10.29. Significant Public Properties of DataRelation Objects

PROPERTY	PURPOSE
DataSet	Returns the dataset the relation is contained in.
RelationName	Returns or sets the name of the relation.

To create the ch10_05 example, add a new SQL data adapter, sqlDataAdapter1 , to a Windows form that will return the pubs database's publishers table, and another data adapter, sqlDataAdapter2 , that will return the titles table. Then use the Data, Generate Dataset item to generate a new dataset, dataSet11 , containing both tables. When the main form loads, we can fill the dataset from the data adapters like this:

 private void Form1_Load(object sender, System.EventArgs e) {  dataSet11.Clear();   sqlDataAdapter1.Fill(dataSet11);   sqlDataAdapter2.Fill(dataSet11);  .     .     . 

Next, add a list box to the form for the master data, and a data grid for the detail data. Bind the list box to the pub_name field in the publishers table in dataSet11 . To create our data relation between the publishers and titles tables, we'll need two DataColumn objects. The DataColumn objects will hold the shared pub_id column from both of those tables. Here's how we create those objects in code:

 private void Form1_Load(object sender, System.EventArgs e) {   dataSet11.Clear();   sqlDataAdapter1.Fill(dataSet11);   sqlDataAdapter2.Fill(dataSet11);  DataColumn publishersColumn;   DataColumn titlesColumn;   publishersColumn = dataSet11.Tables["publishers"].Columns["pub_id"];   titlesColumn = dataSet11.Tables["titles"].Columns["pub_id"];  .     .     . 

Now we can relate the master and detail part of this application together using these DataColumn objects to create a data relation. We'll create a new data relation by passing its namewe'll call it publisherstitles and the two column objects to the DataRelation constructor. We can install this new data relation, which shows how to relate the publishers table to the titles table, in the dataset using the Add method of the dataset's Relations collection. To display the detail data, all you have to do is bind the new data relation to a data grid like this:

 private void Form1_Load(object sender, System.EventArgs e) {   dataSet11.Clear();   sqlDataAdapter1.Fill(dataSet11);   sqlDataAdapter2.Fill(dataSet11);     .     .     .  DataRelation publisherstitles;   publisherstitles = new DataRelation("publisherstitles", publishersColumn,   titlesColumn);   dataSet11.Relations.Add(publisherstitles);   dataGrid1.SetDataBinding(dataSet11, "publishers.publisherstitles");  } 

You can see the results in Figure 10.6, where the new data relation object has related the publisher data in the list box to the title data in the data grid. All the user needs to do to see a publisher's titles is click the name of a publisher, which makes that publisher's titles appear in the data grid.