Adding a Relationship to Our Program

In any except the most trivial database program, you need to examine multiple tables in a parent/child format. The .NET data grid comes into its own in this situation. You can hand code the relationships between tables or have it done for you. To illustrate just how easy it is to show a parent/child relationship between tables, we will expand the program we built at the start of this chapter.

The DataSet object model contains a DataRelations collection that will hold any table relationships we define and add to this collection. The DataSet also has a DataTables collection that contains all the tables currently in memory. Each of the DataTables has a DataColumn, DataRow, and Constraints collection. The DataSet object model is shown in Figure 11-10.

Figure 11-10

The DataSet object model.

The Data Sets and XML Program

In the Northwind database, the Categories table has a primary key on the CategoryID field. The Products table has a CategoryID field as its foreign key. This example will link the child Products table to the parent Categories table. Notice in Figure 11-11 that each category record has a plus sign beside it, indicating that each record has one or more child records associated with it.

Figure 11-11

The plus sign indicates that child records are associated with the parent record.

By expanding a record, we can see a relationship named CategoriesProducts that was created for us. (The .NET convention for naming relationships is ParentTableNameChildTableName.) Clicking the CategoriesProducts link, shown in Figure 11-12, displays all of the child records associated with that category.

Figure 11-12

Expanding a record shows a link to the child records.


	When the child records are displayed, note that the data grid actually adds two icons. Clicking the white box with the up and down arrows on the right side of the data grid's caption bar will display or hide the Categories bar. When the Categories bar is displayed, each of the fields of the parent record is displayed. If there are more fields than can be displayed, as in our example, black arrows permit horizontal scrolling. Clicking the white box again will hide this row. To return to the parent Categories table, click the white left-pointing arrow next to the box on the right side of the data grid. This is a compelling approach to displaying complex relationships of data.

Creating the Parent/Child Relationship

The first operation we want to perform is to add a second SqlDataAdapter to the form. We already have a connection to the Northwind database, a data adapter with which to grab the information we want from the Categories table, and a data set in which to place the records. Essentially we are going to use the second data adapter to get the records from the Products table and place them in the existing data set. Recall from Chapter 10 that a data set can contain multiple tables and that the relationships are built within the data set.

Add a second data adapter to your program.
Accept the defaults the wizard presents for the connection and the Use SQL Statements option. On the Generate The SQL Statements screen, add the following SQL statement. We are simply retrieving each of the fields with the exception of the Discontinued field from the Products table. Also, by using the WHERE clause, only products that are not discontinued will be retrieved.
```
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel FROM Products WHERE (Discontinued = 0)
```
Click Next, and the wizard informs you that SqlDataAdapter2 has been configured successfully. Click Finish.
We now want to add the records retrieved from the Products table to our existing data set. Right-click SqlDataAdapter2, and select Generate Dataset. Use the existing dataset, SqlXMLExample, and select the Products table. Be sure to clear the Add This Dataset To The Designer check box because we are using the existing DataSet1, as shown in Figure 11-13. Click OK to generate commands to add the Products table to the existing data set.

Figure 11-13
Be sure to clear the Add This Dataset To The Designer check box before you click OK.

Now right-click the form, and select Generate Dataset. We want to have both tables, Categories from SqlDataAdapter1 and Products from SqlDataAdapter2, placed in DataSet1. Leave the default options unchanged, and click OK to generate the data set.
The schema Dataset1.xsd has been added to the Solution Explorer. Open the Solution Explorer, and double-click Dataset1.xsd to display the graphical representation of the schema. Note that both of the tables, Categories and Products, are now resident in the single dataset in the XML Designer, as you can see in Figure 11-14.

Figure 11-14
The Categories and Products tables are now resident in the single data set.

Adding a Relationship to Our Tables

Let's take a moment to consider how relationships are built in .NET versus traditional database theory. This concept is one of those that you need to get down right away.

If we wanted to retrieve all the orders placed by each of our employees, we would write an inner join in SQL that looks like this.

SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName, Employees.Title, Orders.* FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID;

This query would return a recordset that merges the column (EmployeeID) that we need to work with into a single result. Our INNER JOIN statement ends up returning rows with a certain quantity of duplicated data. For example, for each of the orders placed by Nancy Davolio, we get the entire Employee table fields duplicated. We certainly don't need to repeat her name, title, and so forth for each order she sold, but there it is in the returned tabular structure from the result set, as you can see in Figure 11-15.

Figure 11-15

Employee information is repeated in this inner join.

Now you need to create a relationship between the tables. Select both tables, right-click one of the tables and choose Add | New Relation. Confirm that the values shown in Table 11-4 are set in the Edit Relation dialog box, shown in Figure 11-16. Click OK when you're finished.

Table 11-4 Edit Relation Values
Setting	Value
Name	CategoriesProducts (You will need to know this name later so, if you change it, be sure you make a note of the new name.)
Parent Element	Categories
Child Element	Products
Key	Constraint1
Key Fields	CategoryID
Foreign Key Fields	CategoryID
Create Foreign Key Constraint Only	Not selected

Figure 11-16

The Edit Relation dialog box.

A relation icon is displayed between the two tables in the XML Designer, as shown in Figure 11-17. If you need to change relationship settings, you can right-click the relationship line and choose Edit Relation.

Figure 11-17

The XML Designer shows the new relationship.

At this point, you have set up everything you need in order to get information out of the database and into a data set. The only step left for us to take is to execute the Fill method of SqlDataAdapter2 and pass in the Products table to the DataSet object. Add the following line to the Retrieve button's Click event handler.

Private Sub btnRetrieve_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btmRetrieve.Click If bDataAdded = True Then Exit Sub Try SqlDataAdapter1.Fill(DataSet11, "Categories") SqlDataAdapter2.Fill(DataSet11, "Products") With dgDataGrid .CaptionText = "Examining XML" .DataSource = DataSet11 .AllowSorting = True .AlternatingBackColor = System.Drawing.Color.Bisque .SetDataBinding(DataSet11, "Categories") End With '-- Update the XML tab txtXML.Text = DataSet11.GetXml '-- Update the XML Schema tab txtXMLSchema.Text = DataSet11.GetXmlSchema bDataAdded = True Catch ex As Exception Console.WriteLine(ex.ToString()) End Try End Sub

That's all there is to it. A little wizardry and a single line of code gives us our parent/child relationship. And because we bind the single data set to the data grid, it knows how to display both of the tables. Before you leave this project, click the Write XML button to write a file that contains both the Categories and Products tables, along with the relationship between the two. Now take a moment to examine the new SqlXML.xml written to disk. We will shortly be using this file to populate a data grid in a new program.

Examining DataSet Properties

It's easy to see the contents of our data set. Right-click DataSet11 in the Forms Designer, and select Dataset Properties. Note that our data set now contains both the Categories and Products tables. Expand all of the nodes to display the fields. The Categories table contains the CategoriesProducts relationship, which contains the Products table as a child. The Constraint1 in each table represents their respective primary keys. The Dataset Properties dialog box is shown in Figure 11-18.

Figure 11-18

The Dataset Properties dialog box.