Using Master/Detail Relationships and Data Relation Objects

In the topic "Using Relational Databases" in the previous chapter, I showed how to use a single SQL statement to join the data from two related tables (the authors and titleauthor tables from the pubs database, which are related using the au_id field). This joined the data from the two tables, and you can see the results in Chapter 20, Figure 20.28.

However, you don't have to merge the data from two tables into one dataset to work with related tables; you can keep the two tables separate, and use a data relation object to relate them. For example, you might want to set up a master/detail relationship, also called a parent/child relationship, between two tables, as you see in the ParentChildData example that appears in Figure 21.13. This example uses the publishers and titles tables in the pubs database, and relates them through the pub_id key. (The publishers table lists publishers, and the titles table lists published books.) The code displays the publishers.pub_name field in the combo box you see in Figure 21.13 (this is the "master" part), and when the user selects a publisher, the program displays all that publisher's books in the datagrid below (this is the "detail" part).

Figure 21.13: The ParentChildData example.

Although the combo box is bound to the publishers.pub_name field, the data grid is actually bound to a data relation object—publisherstitle—that we'll create. Let's see how this works.

To follow along in this example, create a new Windows forms project and drag a SQL data adapter, SqlDataAdapter1, to the main form in this project. In the Data Adapter Configuration Wizard, connect this data adapter to all fields in the pubs database's publishers table (SQL: "SELECT * FROM publishers"). Then create a second SQL data adapter, SqlDataAdapter2, to the titles table (SQL: "SELECT * FROM titles"). Then create a dataset using the Data|Generate Dataset menu item using both tables, as you see in Figure 21.14. This creates the DataSet1 class, and an object of that class, DataSet11.

Figure 21.14: Creating DataSet1.

The new dataset is just a data container, like any dataset, and that means it doesn't know anything about the relationship between the tables. In fact, both tables share a common field, pub_id, the publisher ID, which is a key into both tables. We'll add a data relation object to make this relationship explicit. (Because each publisher in the master table has only one ID, but multiple titles in the titles table can have the same publisher ID, this is called a one-to-many relation.)

We'll create a data relation object named publisherstitles relating the two tables we're using. To create this object, find the file DataSet1.xsd, the XML schema for DataSet1, in the Solution Explorer and double-click it to open it in the Visual Basic IDE. You'll see the two tables—publishers and titles—in an XML designer.

When you open the XML schema for DataSet1, the toolbox displays and opens an XML Schema tab. Drag a relation object from the toolbox onto the child table, the titles table. Doing so opens the Edit Relation dialog you see in Figure 21.15. This dialog creates the data relation object we'll need, and Visual Basic has already given it the default name publisherstitles that we'll use. Note that the parent element is already given as the publishers table and the child element is given as the titles table in Figure 21.15.

Figure 21.15: Creating a data relation object.

Clicking the OK button closes the Edit Relation dialog, and you can now see a data relation object relating the two tables we're using, as you see in Figure 21.16. Now we've added a relation between the two tables in the dataset we've created.

Figure 21.16: A data relation object at work.

We'll also need code to load the two tables into the dataset from the two data adapters, so add this code to the Form1_Load event:

Private Sub Form1_Load(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load
    SqlDataAdapter1.Fill(DataSet11)
    SqlDataAdapter2.Fill(DataSet11)
End Sub

Now create a combo box and set its DataSource property to DataSet11, and its DisplayMember property to publishers.pub_name to display the names of publishers, as you see in Figure 21.13. Next, create a data grid and set its DataSource property to DataSet11, and its DataMember property—not to publishers.titles as you might expect—but to the data relation object that we created, publishers.publisherstitles. That's all it takes. Now we've connected the two controls using the data relation object we created, and you see the results in Figure 21.13. When the user selects a publisher in the combo box, the program responds by activating the one-to-many relationship and displaying all the publisher's titles in the data grid.