Creating a Master-Detail Page

Learning Resources
 

Creating a Master-Detail Page

In most data models there are numerous one-to-many relationships. For example, in a data model that contains information about a company's workforce, there might be a Locations table and an Employees table, the Locations table cataloging the various offices of the company and the Employees listing the company's employees. The business rules may be such that each employee is assigned to precisely one location, thereby establishing a one-to-many relationship between locations and employees.

With such relationships, oftentimes we want to allow the user to select a particular record from the "one" entity and then display its corresponding "many" records. For example, rather than showing all employees on an ASP.NET page, it might be better to show a list of locations instead. When the user selects a location, the location's corresponding employees are then displayed. Filtering the output on a one-to-many relationship is surprisingly easy with ASP.NET 2.0 and, like with our earlier examples, can be done with absolutely no source code.

Displaying Master/Detail Data from a Database

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:

  1. A DropDownList that lists the items from the "one" entity.
  2. 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.

Aa479344.gridview_fg16(en-us,MSDN.10).gif

Figure 16

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.

Aa479344.gridview_fg17(en-us,MSDN.10).gif

Figure 17

Next add a GridView to the page and associate it with the SqlDataSource just created. At this point you're done! As Figure 18 and 19 shows, when viewing the page through a browser the user is shown the list of products in a DropDownList and the selected product's order details information in a GridView below.

Aa479344.gridview_fg18(en-us,MSDN.10).gif

Figure 18

Aa479344.gridview_fg19(en-us,MSDN.10).gif

Figure 19

The ASP.NET page's declarative syntax is shown below. Note that there is no source code required for this page—just a couple of SqlDataSource controls, a DropDownList, and a GridView. When reviewing the markup, be sure to note the following:

  • The DropDownList's DataValueField property is set to ProductID, meaning when a DropDownList item is selected, the DropDownList's SelectedValue property equals the ProductID for that item. It's this ProductID value that is used in the WHERE clause in the orderDetailsForProduct SqlDataSource to get back the appropriate set of order details.
  • The orderDetailsForProduct SqlDataSource's section defines the values for the parameters in the SelectCommand (namely, @ProductID). The tag indicates that the ProductID parameter is of type Int32 and its value is the value of the productSelector control's SelectedValue property. (The section was added automatically based on the values specified during the SqlDataSource's configuration wizard.)
 
 
<%@ Page Language="C#" %>




    Untitled Page


    

    

        
You are Viewing Order Detail Information for Orders 
         that Have Included Shipments of the Selected Product

        
        
        
         

 

--MSDN
 For Support