GridView Examples for ASP.NET 2.0: Displaying Master/Detail Data in a GridView

  • Article

 

Click here to return to the TOC.

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 <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.)
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" 
  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <h2>You are Viewing Order Detail Information for Orders 
         that Have Included Shipments of the Selected Product</h2>
        <asp:SqlDataSource ID="productListingDataSource" 
         Runat="server" ConnectionString=
         "<%$ ConnectionStrings:NWConnectionString %>"
         SelectCommand="SELECT [ProductID], 
          [ProductName] FROM [Products]">
        </asp:SqlDataSource>
        <asp:DropDownList ID="productSelector" Runat="server" 
           DataSourceID="productListingDataSource"
           DataTextField="ProductName" DataValueField="ProductID" 
           AutoPostBack="True">
        </asp:DropDownList>&nbsp;
        <asp:SqlDataSource ID="orderDetailsForProduct" Runat="server" 
         SelectCommand="SELECT [OrderID], [ProductID], [UnitPrice], 
         [Quantity] FROM [Order Details] WHERE ([ProductID] = 
         @ProductID)"
            ConnectionString=
              "<%$ ConnectionStrings:NWConnectionString%>"
              DataSourceMode="DataReader">
            <SelectParameters>
                <asp:ControlParameter Name="ProductID" Type="Int32" 
                  ControlID="productSelector" 
                  PropertyName="SelectedValue"></asp:ControlParameter>
            </SelectParameters>
        </asp:SqlDataSource><asp:GridView ID="orderDetailsGridView" 
          Runat="server" DataSourceID="orderDetailsForProduct" 
          AutoGenerateColumns="False" DataKeyNames="OrderID" 
          BorderWidth="1px" BackColor="LightGoldenrodYellow" 
          GridLines="None" CellPadding="2" BorderColor="Tan" 
          ForeColor="Black">
            <FooterStyle BackColor="Tan"></FooterStyle>
            <PagerStyle ForeColor="DarkSlateBlue" 
              HorizontalAlign="Center" BackColor="PaleGoldenrod">
            </PagerStyle>
            <HeaderStyle Font-Bold="True" 
              BackColor="Tan"></HeaderStyle>
            <AlternatingRowStyle 
              BackColor="PaleGoldenrod"></AlternatingRowStyle>
            <Columns>
                <asp:BoundField ReadOnly="True" HeaderText="Order ID" 
                  InsertVisible="False" DataField="OrderID"
                    SortExpression="OrderID">
                    <ItemStyle HorizontalAlign="Center"></ItemStyle>
                </asp:BoundField>
                <asp:BoundField HeaderText="Quantity" 
                  DataField="Quantity" SortExpression="Quantity" 
                  DataFormatString="{0:d}">
                    <ItemStyle HorizontalAlign="Right"></ItemStyle>
                </asp:BoundField>
                <asp:BoundField HeaderText="Unit Price" 
                   DataField="UnitPrice" SortExpression="UnitPrice" 
                   DataFormatString="{0:c}">
                    <ItemStyle HorizontalAlign="Right"></ItemStyle>
                </asp:BoundField>
            </Columns>
            <SelectedRowStyle ForeColor="GhostWhite" 
                BackColor="DarkSlateBlue"></SelectedRowStyle>
        </asp:GridView>    
    </div>
    </form>
</body>
</html>

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

OrderDetailDAL Class (Visual Basic)

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports Microsoft.VisualBasic
Public Class OrderDetailDAL
    Public Shared Function GetOrderDetailsByProductID(ByVal productID _
      As Integer) As List(Of OrderDetail)
        ' returns a list of OrderDetail instances for 
        ' a particular product based on the
        ' data in the Northwind Products table
        Dim sql As String = "SELECT OrderID, UnitPrice, Quantity " & _
         "FROM [Order Details] WHERE ProductID = @ProductID"
        Using myConnection As New _
          SqlConnection( _
          ConfigurationManager.ConnectionStrings( _
          "NWConnectionString").ConnectionString)
            Dim myCommand As New SqlCommand(sql, myConnection)
            myCommand.Parameters.Add(New _
              SqlParameter("@ProductID", productID))
            myConnection.Open()
            Dim reader As SqlDataReader = 
              myCommand.ExecuteReader(CommandBehavior.CloseConnection)
            Dim results As New List(Of OrderDetail)()
            While reader.Read()
                Dim detail As New OrderDetail()
                detail.OrderID = Convert.ToInt32(reader("OrderID"))
                detail.ProductID = productID
                detail.Quantity = Convert.ToInt32(reader("Quantity"))
                detail.UnitPrice = 
                  Convert.ToDecimal(reader("UnitPrice"))
                results.Add(detail)
            End While
            reader.Close()
            myConnection.Close()
            Return results
        End Using
    End Function
End Class

OrderDetail Class (C#)

using System;
/// <summary>
/// Summary description for OrderDetail
/// </summary>
public class OrderDetail
{
#region Private Member Variables
    private int _orderID;
    private int _productID;
    private int _quantity;
    private decimal _unitPrice;
#endregion
#region Constructors
    public OrderDetail() {}
    public OrderDetail(int orderID, int productID, int quantity, 
      decimal unitPrice)
    {
        this._orderID = orderID;
        this._productID = productID;
        this._quantity = quantity;
        this._unitPrice = unitPrice;
    }
#endregion
#region Public Properties
    public int OrderID
    {
        get
        {
            return _orderID;
        }
        set
        {
            if (value < 0)
                throw new ArgumentException(@"OrderID must be greater 
                  than or equal to zero.");
            else
                _orderID = value;
        }
    }
    public int ProductID
    {
        get
        {
            return _productID;
        }
        set
        {
            if (value < 0)
                throw new ArgumentException(@"ProductID must be greater 
                  than or equal to zero.");
            else
                _productID = value;
        }
    }
    public int Quantity
    {
        get
        {
            return _quantity;
        }
        set
        {
            if (value < 0)
                throw new ArgumentException(@"Quantity must be greater 
                   than or equal to zero.");
            else
                _quantity = value;
        }
    }
    public decimal UnitPrice
    {
        get
        {
            return _unitPrice;
        }
        set
        {
            _unitPrice = value;
        }
    }
    #endregion
}

OrderDetailDAL Class (C#)

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System;
/// <summary>
/// Summary description for OrderDetailDAL
/// </summary>
public class OrderDetailDAL
{
    public static 
      List<OrderDetail> GetOrderDetailsByProductID(int productID)
    {
        // returns a list of OrderDetail instances for 
        // a particular product based on the
        // data in the Northwind Products table
        string sql = @"SELECT OrderID, UnitPrice, Quantity 
         FROM [Order Details] WHERE ProductID = @ProductID";
        using (SqlConnection myConnection = new 
         SqlConnection(
         ConfigurationManager.ConnectionStrings["NWConnectionString"]
         .ConnectionString))
        {
            SqlCommand myCommand = new SqlCommand(sql, myConnection);
            myCommand.Parameters.Add(new SqlParameter("@ProductID", 
              productID));
            myConnection.Open();
            SqlDataReader reader = 
              myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            List<OrderDetail> results = new List<OrderDetail>();
            while (reader.Read())
            {
                OrderDetail detail = new OrderDetail();
                detail.OrderID = Convert.ToInt32(reader["OrderID"]);
                detail.ProductID = productID;
                detail.Quantity = Convert.ToInt32(reader["Quantity"]);
                detail.UnitPrice = 
                    Convert.ToDecimal(reader["UnitPrice"]);
                results.Add(detail);
            }
            reader.Close();
            myConnection.Close();
            return results;
        }
    }
}

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).

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

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 orderDetailsForProduct ObjectDataSource to retrieve only the correct order details for the chosen product.

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" 
  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <h2>You are Viewing Order Detail Information 
          for Orders that Have Included Shipments of the Selected 
          Product</h2>
        <asp:ObjectDataSource ID="productListingDataSource" 
          Runat="server" TypeName="ProductDAL"
            SelectMethod="GetProducts">
        </asp:ObjectDataSource>
        <asp:DropDownList ID="productSelector" Runat="server" 
          AutoPostBack="True" DataSourceID="productListingDataSource"
            DataTextField="ProductName" DataValueField="ProductID">
        </asp:DropDownList>
        <asp:ObjectDataSource ID="orderDetailsForProduct" 
          Runat="server" TypeName="OrderDetailDAL"
            SelectMethod="GetOrderDetailsByProductID">
            <SelectParameters>
                <asp:ControlParameter Name="productID" Type="Int32" 
                   ControlID="productSelector" 
                   PropertyName="SelectedValue"></asp:ControlParameter>
            </SelectParameters>
        </asp:ObjectDataSource>
        <asp:GridView ID="productGridView" Runat="server" 
          DataSourceID="orderDetailsForProduct" 
          AutoGenerateColumns="False" DataKeyNames="OrderID">
            <Columns>
                <asp:BoundField ReadOnly="True" HeaderText="Order ID" 
                   InsertVisible="False" DataField="OrderID"
                    SortExpression="OrderID">
                    <ItemStyle HorizontalAlign="Center"></ItemStyle>
                </asp:BoundField>
                <asp:BoundField HeaderText="Quantity" 
                  DataField="Quantity" SortExpression="Quantity" 
                  DataFormatString="{0:d}">
                    <ItemStyle HorizontalAlign="Right"></ItemStyle>
                </asp:BoundField>
                <asp:BoundField HeaderText="Unit Price" 
                  DataField="UnitPrice" SortExpression="UnitPrice" 
                  DataFormatString="{0:c}">
                    <ItemStyle HorizontalAlign="Right"></ItemStyle>
                </asp:BoundField>
            </Columns>
        </asp:GridView>    
    
    </div>
    </form>
</body>
</html>

Next Section: Paging and Sorting the GridView's Data

© Microsoft Corporation. All rights reserved.