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:

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.

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.

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.

Figure 18

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 orderDetailsForProductSqlDataSource to get back the appropriate set of order details.

The orderDetailsForProductSqlDataSource's <SelectParameters> section defines the values for the parameters in the SelectCommand (namely, @ProductID). The <asp:ControlParameter> tag indicates that the ProductID parameter is of type Int32 and its value is the value of the productSelector control's SelectedValue property. (The <SelectParameters> section was added automatically based on the values specified during the SqlDataSource's configuration wizard.)

Displaying Master/Detail Data from an Object

Displaying master/detail data from a one-to-many relationship with a data access layer (DAL) is identical to doing the same with data coming directly from a database save one step: the configuration of the second data source control (the one that retrieves the proper subset of order details). When using a DAL instead of going straight to the database you'll need to create a method in your DAL for accessing just a subset of data. Included in this article's download are OrderDetail and OrderDetailDAL classes, which illustrate how to display master/detail data using a DAL. The OrderDetail class represents information inherent in an order detail, while the OrderDetailDAL class provides a method to accessing just those order details that belong to a specific product.

OrderDetail Class (Visual Basic)

Imports Microsoft.VisualBasic
Public Class OrderDetail
#Region "Private Member Variables"
Private _orderID As Integer
Private _productID As Integer
Private _quantity As Integer
Private _unitPrice As Decimal
#End Region
#Region "Constructors"
Public Sub New()
End Sub
Public Sub New(ByVal orderID As Integer, _
ByVal productID As Integer, ByVal quantity As Integer, _
ByVal unitPrice As Decimal)
Me._orderID = orderID
Me._productID = productID
Me._quantity = quantity
Me._unitPrice = unitPrice
End Sub
#End Region
#Region "Public Properties"
Public Property OrderID() As Integer
Get
Return _orderID
End Get
Set(ByVal value As Integer)
If value < 0 Then
Throw New ArgumentException("OrderID must be " & _
"greater than or equal to zero.")
Else
_orderID = value
End If
End Set
End Property
Public Property ProductID() As Integer
Get
Return _productID
End Get
Set(ByVal value As Integer)
If value < 0 Then
Throw New ArgumentException("ProductID must be " & _
"greater than or equal to zero.")
Else
_productID = value
End If
End Set
End Property
Public Property Quantity() As Integer
Get
Return _quantity
End Get
Set(ByVal value As Integer)
If value < 0 Then
Throw New ArgumentException("Quantity must be " & _
"greater than or equal to zero.")
Else
_quantity = value
End If
End Set
End Property
Public Property UnitPrice() As Decimal
Get
Return _unitPrice
End Get
Set(ByVal value As Decimal)
If value < 0 Then
Throw New ArgumentException("UnitPrice must be " & _
"greater than or equal to zero.")
Else
_unitPrice = value
End If
End Set
End Property
#End Region
End Class

The OrderDetail class contains four properties: OrderID, ProductID, Quantity, and UnitPrice. The OrderDetailDAL class's sole method, GetOrderDetailsByProductID(productID), takes in a product ID and returns a List of OrderDetail instances that have the specified, passed-in ProductID value. This is accomplished by running an appropriate SQL statement against the Northwind database and building a List of OrderDetails based on the results returned from the query.

When configuring the second ObjectDataSource—the one that retrieves just the order details for the selected product—select for the SELECT method the GetOrderDetailsByProductID(productID) method and click Next. When selecting a method with input parameters, the next step of the ObjectDataSource wizard will prompt you to specify those parameter values (see Figure 20). Just as we did with the SqlDataSource example, select the value for the method's productID input parameter as the DropDownList control (productSelector).

Figure 20

That's the only difference required between displaying master/detail data using a SqlDataSource and an ObjectDataSource. All other steps are identical, as is the output. (Refer back to Figures 18 and 19 for screenshots of the master/detail output.) As with the SqlDataSource example, the ObjectDataSource example requires absolutely no code in the ASP.NET page. Also, as before, take note on how the selected DropDownList value is used in the orderDetailsForProductObjectDataSource to retrieve only the correct order details for the chosen product.