Fetching data for the master
Let's first examine how to display master/detail data from a one-to-many relationship when such data is coming directly from a database. On our ASP.NET page we'll need two user interface elements:
- A DropDownList that lists the items from the "one" entity.
- A GridView that will display the records from the "many" entity that are associated with the selected "one" entity.
The Northwind database has a number of one-to-many relationships. For this demonstration, let's use the one-to-many relationship between the Products table and the Order Details table. (This relationship exists and is evident since the Order Details table has the foreign key field ProductID, indicating that one product can exist in an arbitrary number of Order Detail records.)
To start, we need to grab the list of products from the Products table and display these in a DropDownList. To get the products data, add a SqlDataSource to the page, configuring it to select the ProductID and ProductName fields from the Products table, ordered by ProductName. Next, add a DropDownList to the page and set its DataSource to the products SqlDataSource by clicking on the Choose Data Source option from the DropDownList's Smart Tag. Doing so will display the Data Source Configuration Wizard shown in Figure 16. From this wizard, select the data source control to use and then select ProductName as the field to display and ProductID as the value field.
At this point we have completed step 1. We now need to have a GridView that displays just those order details for the selected product. To accomplish this, add another SqlDataSource to the page. For this SqlDataSource select the Order Details table from the drop-down list of tables and views and return the OrderID, UnitPrice, and Quantity fields. We don't want to return all order detail records, just those for the selected product, so click on the WHERE button. This will display the Add WHERE Clause dialog box (see Figure 17). From this dialog box you can add any number of WHERE clause conditions.
To get back the right subset of order details, select the ProductID field from the Column drop-down list, = from the Operator drop-down list, and Control from the Source drop-down list. Once you select Control as the Source you will be prompted to specify the control in the Control ID drop-down list on the right. Choose the DropDownList added a moment ago. Finally, to add this WHERE clause to the SqlDataSource, be sure to click the Add button.