Working with Relational Databases

Relational databases are powerful databases that connect the data in multiple tables. For example, say that you are using the pubs sample database that we've already used in this chapter, and want to display all the books that the authors in the authors table have written. That's not a simple task, because in the pubs database, the names of the authors are stored in the authors table, but the books that they've written are stored in the titleauthor table (these books are actually stored by book ID, not by title). And the titleauthor table is a separate table.

How do you join the authors names from the authors table with the titles they've written from the titleauthor table into one dataset? You can use a SQL inner join , and we're going to see how this works in our next example, the Ch09_02 example.

To follow along, create an application named Ch09_02, and drag a data adapter onto the main form, opening the Data Adapter Configuration Wizard. To configure the adapter's SQL statement, click the Query Builder button, and use the Add Table dialog box to add both tables from the SQL Server pubs database: authors and titleauthor , as you see in Figure 9.18.

Figure 9.18. A relation between tables in the Query Builder.

These two tables share the author ID field, au_id , which is the key that relates the records of one table to the records of the other. The Query Builder realizes that this is a shared field and displays this relation graphically, as you see in Figure 9.18.

Select the au_lname and au_fname fields from the authors table, and the title_id field from the titleauthor table in the Query Builder as you see in Figure 9.18. Then click OK to close the Query Builder, creating this SQL (remember that you can edit the SQL generated by the Query Builder by hand):

 SELECT   authors.au_lname,   authors.au_fname,   titleauthor.title_id FROM   authors INNER JOIN   titleauthor ON     authors.au_id = titleauthor.au_id 

You can see the SQL JOIN statement here, which is what joins the tables we want. Because we're working with multiple tables, the Data Adapter Configuration Wizard won't be able to generate SQL statements for updating any data because that would involve updating parts of records in multiple tables. To avoid warnings in the Data Adapter Configuration Wizard, click the Advanced Options button now and deselect the Generate Insert, Update, and Delete Statements check box. Then click the Next button and follow through to the end of the process as before to finish configuring the data adapter.

DATA RELATION OBJECTS

In this example, we used SQL to join two related tables, but there's another way to work with related tables in C#. You can create an FCL data relation object to make the relationship explicit. Creating such an object encapsulates the relationship between data tables, while still leaving the two tables independent.

After you've configured the data adapter, generate a dataset as before, and connect it to data grid as we've also done before. You can see the results in Figure 9.19, where we've combined data from two tables that are related to each other. The authors and titleauthor tables have been joined , using their common field, au_id . Because some authors have written multiple books, you'll see multiple entries for them in the dataset. For example, you can see that Marjorie Green has two title_id entries, as do others.

Figure 9.19. Running the Ch09_02 example.