Using Relational Databases

As discussed in the In Depth section of this chapter, relational databases are powerful databases that connect tables with specific keys, and by using relational concepts, you can perform many SQL operations. You can see how this works in the Relational example on the CD-ROM.

In that example, I've dragged a data adapter onto the main form, which opens the Data Adapter Configuration Wizard. To configure the adapter's SQL statement, I click the Query Builder button, as we did earlier in the DataAccess example in the In Depth section of this chapter, and use the Add Table dialog to add two tables to the Query Builder from the SQL Server pubs database: authors and titleauthor. These two tables share a key, au_id (Author ID), which relates the records of one table to the records of the other table. The Query Builder displays this relation graphically, as you see in Figure 20.27.

Figure 20.27: The Query Builder, showing a relation between two tables.

I select a number of fields from both tables to add to the data adapter, as you see in Figure 20.27, and click OK to close the Query Builder. Because we're dealing with two tables at once with an SQL JOIN operation, Visual Basic can't generate SQL for updating that data, so click the Advanced Options button in the Data Adapter Configuration Wizard and deselect the "Generate Insert, Update, and Delete Statements" checkbox. (If you didn't do so and tried to move on to the next step of the wizard, Visual Basic would inform you of the error, and you could move back, click the Advanced Options button, and remove the offending statements.) This generates the following SQL:

SELECT
    authors.au_id,
    authors.au_lname,
    titleauthor.au_id AS Expr1,
    titleauthor.title_id,
    titleauthor.au_ord,
    titleauthor.royaltyper,
    authors.au_fname
FROM
    authors INNER
JOIN
    titleauthor ON
        authors.au_id = titleauthor.au_id

You can see the result in a datagrid in Figure 20.28. Note how this works-some authors in the authors table have multiple entries in the titleauthor table, so, for example, you can see that Green has two different title_id entries, as does Locksley, and so on. In this way, the authors and titleauthors tables have been joined, using their common key, au_id.

Figure 20.28: Using related tables.

This example uses one SQL query to join two related table, but there's another way of working with related tables-you can create a data relation object to make the relationship explicit, while still working with the two tables independently (without joining them)-see "Using Master/Detail Relationships and Data Relation Objects" in the next chapter.