GridView Examples for ASP.NET 2.0: Drilling Down into Detailed Data
Click here to return to the TOC.
In the Filtering the Data Shown in a GridView section we saw how to allow the user to display a list of products in a DropDownList and, for the selected product, list the product's order details in a GridView. That demo worked well for viewing order details for a specific product, but imagine if you wanted to put more of an emphasis on displaying product information, with viewing the order details for the products being a secondary concern. In such a situation you might want to show a GridView of the products with a way to "select" a particular GridView row. Doing so would then display the order details for the selected product in another GridView.
This common type of report is often called a drill-down report, since it shows data at one level—the list of products—with the ability to drill down into information specific to a particular product—the list of associated order details. Another common type of drill-down report is used when displaying data that has a lot of properties. For example, the Northwind database has a Customers table with numerous fields. When showing information about customers in a GridView, it might be impractical to list all of the fields of the Customers table in a GridView. Rather, it might make more sense to list only the most germane fields along with a "Details" button in each row that, when clicked, would display the particular customer's complete information.
Both of these types of drill-down reports are easily accomplished with the GridView and require no programming. In the next two sections we'll see just how easy building drill-down reports can be.
Drilling Down From One to Many
One of the many one-to-many relationships in the Northwind database can be found between the Orders or Order Details tables. The Orders table contains a record for each order placed. An order consists of one to many line items, each line item being stored as a record in the Order Details table. To illustrate a one-to-many drill-down report, let's create an ASP.NET page that lists all available orders in one GridView, where each row in this GridView has a "View Order Details" button that, when clicked, displays the particular order's line items in another GridView.
To accomplish this, start by adding a SqlDataSource to the page that retrieves all of the records from the Orders table. Next, add a GridView to the page and bind it to this SqlDataSource. In order to select an item from the GridView, check the Enable Selection link in the GridView's Smart Tag. This will add a CommandField to the GridView with a Select button.
Note The CommandField is another type of GridView column type, like the BoundField and ImageField that we examined earlier. The CommandField is used to display common command buttons, such as buttons for selection, deletion, and updating. We'll see more examples of the CommandField in the demos involving deletion and editing of the GridView's underlying data.
Next, we need a SqlDataSource that returns the right subset of order details for the selected order. To accomplish this, add another SqlDataSource to the page and configure it to select the appropriate columns from the Order Details table. Next, click the WHERE button and add a WHERE condition on the OrderID field, creating a parameter tied to the Orders GridView's SelectedValue (see Figure 29).
.gif "ms972814.gridview_fg29(en-us,MSDN.10).gif")
Figure 29
Finally, add another GridView to the page, binding it to the order details SqlDataSource. Once you have completed these two steps your ASP.NET page's declarative syntax should look similar to the following:
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div style="width:50%;float:left;padding-right:10px;">
<h2>Select an Order from the Left...</h2>
<asp:SqlDataSource ID="ordersDataSource" Runat="server"
SelectCommand="SELECT dbo.Orders.OrderID,
dbo.Customers.CompanyName, dbo.Orders.OrderDate FROM
dbo.Orders INNER JOIN dbo.Customers ON dbo.Orders.CustomerID
= dbo.Customers.CustomerID"
ConnectionString=
"<%$ ConnectionStrings:NWConnectionString %>">
</asp:SqlDataSource>
<asp:GridView ID="orderGridView" Runat="server"
DataSourceID="ordersDataSource" DataKeyNames="OrderID"
AutoGenerateColumns="False" AllowPaging="True"
BorderWidth="1px" BackColor="#DEBA84"
CellPadding="3" CellSpacing="2" BorderStyle="None"
BorderColor="#DEBA84"
OnPageIndexChanged="orderGridView_PageIndexChanged">
<FooterStyle ForeColor="#8C4510"
BackColor="#F7DFB5"></FooterStyle>
<PagerStyle ForeColor="#8C4510"
HorizontalAlign="Center"></PagerStyle>
<HeaderStyle ForeColor="White" Font-Bold="True"
BackColor="#A55129"></HeaderStyle>
<Columns>
<asp:CommandField ShowSelectButton="True"
SelectText="View Order Details"></asp:CommandField>
<asp:BoundField HeaderText="Company"
DataField="CompanyName"
SortExpression="CompanyName"></asp:BoundField>
<asp:BoundField HeaderText="Order Date"
DataField="OrderDate" SortExpression="OrderDate"
DataFormatString="{0:d}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
</Columns>
<SelectedRowStyle ForeColor="White" Font-Bold="True"
BackColor="#738A9C"></SelectedRowStyle>
<RowStyle ForeColor="#8C4510" BackColor="#FFF7E7"></RowStyle>
</asp:GridView>
</div>
<div>
<h2>... and View the Order Details on the Right</h2>
<asp:SqlDataSource ID="orderDetailsDataSource" Runat="server"
SelectCommand="SELECT dbo.[Order Details].OrderID,
dbo.Products.ProductName, dbo.[Order Details].UnitPrice,
dbo.[Order Details].Quantity, dbo.[Order Details].Discount
FROM dbo.[Order Details] INNER JOIN dbo.Products
ON dbo.[Order Details].ProductID = dbo.Products.ProductID
WHERE dbo.[Order Details].OrderID = @OrderID"
ConnectionString="<%$ ConnectionStrings:NWConnectionString %>">
<SelectParameters>
<asp:ControlParameter ControlID="orderGridView"
Name="OrderID" Type="Int32"
PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="detailsGridView" Runat="server"
DataSourceID="orderDetailsDataSource"
AutoGenerateColumns="False" BorderWidth="1px"
BackColor="#DEBA84" CellPadding="3"
CellSpacing="2" BorderStyle="None" BorderColor="#DEBA84">
<FooterStyle ForeColor="#8C4510"
BackColor="#F7DFB5"></FooterStyle>
<PagerStyle ForeColor="#8C4510"
HorizontalAlign="Center"></PagerStyle>
<HeaderStyle ForeColor="White" Font-Bold="True"
BackColor="#A55129"></HeaderStyle>
<Columns>
<asp:BoundField HeaderText="Product"
DataField="ProductName"
SortExpression="ProductName"></asp:BoundField>
<asp:BoundField HeaderText="Unit Price"
DataField="UnitPrice" SortExpression="UnitPrice"
DataFormatString="{0:c}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Quantity"
DataField="Quantity" SortExpression="Quantity">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Discount"
DataField="Discount" SortExpression="Discount"
DataFormatString="{0:P}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
</Columns>
<SelectedRowStyle ForeColor="White" Font-Bold="True"
BackColor="#738A9C"></SelectedRowStyle>
<RowStyle ForeColor="#8C4510" BackColor="#FFF7E7"></RowStyle>
</asp:GridView>
</div>
</form>
</body>
</html>
When examining the markup be sure to note the following:
- When adding a Select button to a GridView, a CommandField is added. By default, the text for the Select button will be "Select," but this can be changed through the SelectText property. You can even use an image for the Select button, if you'd rather, by providing the path to the image in the SelectImageUrl property.
- The orderGridView GridView's DataKeyNames property is set to OrderID. When a particular record in the GridView is selected, the GridView's SelectedValue property will be set to that row's OrderID value.
- The orderDetailsDataSource SqlDataSource contains a parameter in its WHERE clause (@OrderID) whose value is specified in the <SelectParameters> section. The <asp:ControlParameter> indicates that the parameter is of type Int32 and its value should be set to the orderGridView **GridView'**s SelectedValue property.
Figures 30 and 31 show the drill-down report in action. Note that on the first page load, only the GridView of orders is displayed (see Figure 30). When an order's "View Order Details" link is clicked, a postback occurs and the order details GridView is bound to the SqlDataSource that returns all Order Details records associated with the selected order (see Figure 31).
.gif "Click here for larger image.")
Figure 30 (Click on the graphic for a larger image)
.gif "Click here for larger image.")
Figure 31 (Click on the graphic for a larger image)
One minor issue with the demo is that the orders GridView's SelectedIndex does not change when the GridView is paged. What this implies is that if you are on page 1 and click on the second order's "View Order Details," you will see that order's details to the right, as expected. However, if you then click to visit page 2, the second order on page 2 will be selected and its details will be shown to the right. I wanted to have the SelectedIndex reset when stepping through the pages. To accomplish this simply create an event handler for the GridView's PageIndexChanged event. The PageIndexChanged event fires whenever the user requests a new page of data. In this event handler I reset the GridView's SelectedIndex to -1, as shown in the following code snippet:
The PageIndexChanged Event Handler (Visual Basic)
Sub orderGridView_PageIndexChanged(ByVal sender As Object, _
ByVal e As System.EventArgs)
orderGridView.SelectedIndex = -1
End Sub
The PageIndexChanged Event Handler (C#)
void orderGridView_PageIndexChanged(object sender, EventArgs e)
{
orderGridView.SelectedIndex = -1;
}
Drilling Down From Summary to Detail
The other flavor of drill-down reports is expanding from a concise view to a detailed view. For example, the Customers table in the Northwind database contains a plethora of fields and displaying all fields in a GridView would result in a wide, unwieldy GridView that was difficult to read. Rather than displaying all customer-related fields, it might make more sense to just display the pertinent fields—CompanyName and ContactName—along with a "View Customer Details" link in each row. If the end user wants to see all of the fields of a particular customer, they can click that customer's details link.
Creating such a summary to a detail drill-down report is fairly simple in ASP.NET 2.0 thanks to the DetailsView Web control, which is designed for displaying information one record at a time (as opposed to displaying numerous records at once, like the GridView). As we'll see shortly, displaying a particular customer's full details using a DetailsView is as simple as adding and configuring a GridView.
To get started we need to get just the CustomerID, CompanyName, and ContactName fields from the Customers table and display them in a GridView. As with our previous drill-down report example, this is accomplished by adding and configuring a SqlDataSource and then binding a GridView to it. Next, add another SqlDataSource that retrieves all of the Customers table's fields.
At this point you could add a WHERE clause and bind the parameter to the customer's GridView, just like we did in the previous drill-down example, but for this exercise let's try an alternative approach for accessing the correct customer for the customer DetailsView. After configuring the second SqlDataSource without adding a WHERE clause, from the Design view go to the SqlDataSource's Properties and add the following into the SqlDataSource's FilterExpression property: CustomerID='{0}'. Next, open the Parameter Collection Editor dialog box by clicking on the FilterParameters property. This dialog box should look familiar, as it closely mimics the dialog box shown in Figure 29 when we configured the WHERE clause parameter for the earlier drill-down report example. Set up a parameter based on the customer's GridView.
The last step is to add a DetailsView to the page. Add a DetailsView by simply dragging and dropping the control from the Toolbox and then, from its Smart Tag, associate it with the data source control we just configured.
Once you have configured this page your page's declarative syntax should look similar to the following markup shown. As with our previous examples, no source code was needed to create this page. When examining this markup be sure to take note of the following things:
- The customerGridView GridView's DataKeyNames property is set to CustomerID. As we discussed with the previous drill-down report example, this indicates that when a GridView row is selected, the GridView's SelectedValue property will be set to the value of that row's CustomerID.
- The customerDetailsDataSource SqlDataSource's SelectCommand does not include a WHERE clause. Instead, it contains a FilterExpression—CustomerID='{0}'—along with a <FilterParameters> section that indicates the filter parameter ({0}) has the value of the customerGridView GridView's SelectedValue property.
<%@ 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 style="width:50%;float:left;padding-right:10px;">
<h2>Select a Customer from the Left...</h2>
<asp:SqlDataSource ID="customerDataSource" Runat="server"
SelectCommand="SELECT [CustomerID], [CompanyName],
[ContactName] FROM [Customers] ORDER BY [CompanyName]"
ConnectionString="<%$ ConnectionStrings:NWConnectionString %>">
</asp:SqlDataSource>
<asp:GridView ID="customerGridView" Runat="server"
DataSourceID="customerDataSource" DataKeyNames="CustomerID"
AutoGenerateColumns="False" BorderWidth="1px"
BackColor="White" CellPadding="4"
BorderStyle="None" BorderColor="#CC9966">
<FooterStyle ForeColor="#330099"
BackColor="#FFFFCC"></FooterStyle>
<PagerStyle ForeColor="#330099" HorizontalAlign="Center"
BackColor="#FFFFCC"></PagerStyle>
<HeaderStyle ForeColor="#FFFFCC" Font-Bold="True"
BackColor="#990000"></HeaderStyle>
<Columns>
<asp:CommandField ShowSelectButton="True"
SelectText="View Customer Details"></asp:CommandField>
<asp:BoundField HeaderText="CompanyName"
DataField="CompanyName"
SortExpression="CompanyName"></asp:BoundField>
<asp:BoundField HeaderText="ContactName"
DataField="ContactName"
SortExpression="ContactName"></asp:BoundField>
</Columns>
<SelectedRowStyle ForeColor="#663399" Font-Bold="True"
BackColor="#FFCC66"></SelectedRowStyle>
<RowStyle ForeColor="#330099" BackColor="White"></RowStyle>
</asp:GridView>
</div>
<div>
<h2>... and See Their Detailed Information on the Right</h2>
<asp:SqlDataSource ID="customerDetailsDataSource"
Runat="server"
SelectCommand="SELECT [CompanyName], [ContactName],
[ContactTitle], [Address], [City], [Region], [PostalCode],
[Country], [Phone], [Fax], [CustomerID] FROM [Customers]"
ConnectionString="<%$ ConnectionStrings:NWConnectionString %>"
FilterExpression="CustomerID='{0}'">
<FilterParameters>
<asp:ControlParameter Name="CustomerID" DefaultValue="-1"
Type="String" ControlID="customerGridView"
PropertyName="SelectedValue"></asp:ControlParameter>
</FilterParameters>
</asp:SqlDataSource>
<asp:DetailsView ID="customerDetailsView" Runat="server"
DataSourceID="customerDetailsDataSource"
BorderWidth="1px" BackColor="White" CellPadding="4"
BorderStyle="None" BorderColor="#CC9966"
AutoGenerateRows="False">
<FooterStyle ForeColor="#330099"
BackColor="#FFFFCC"></FooterStyle>
<RowStyle ForeColor="#330099" BackColor="White"></RowStyle>
<PagerStyle ForeColor="#330099" HorizontalAlign="Center"
BackColor="#FFFFCC"></PagerStyle>
<Fields>
<asp:BoundField HeaderText="CompanyName"
DataField="CompanyName"
SortExpression="CompanyName"></asp:BoundField>
<asp:BoundField HeaderText="ContactName"
DataField="ContactName"
SortExpression="ContactName"></asp:BoundField>
<asp:BoundField HeaderText="ContactTitle"
DataField="ContactTitle"
SortExpression="ContactTitle"></asp:BoundField>
<asp:BoundField HeaderText="Address"
DataField="Address"
SortExpression="Address"></asp:BoundField>
<asp:BoundField HeaderText="City" DataField="City"
SortExpression="City"></asp:BoundField>
<asp:BoundField HeaderText="Region" DataField="Region"
SortExpression="Region"></asp:BoundField>
<asp:BoundField HeaderText="PostalCode"
DataField="PostalCode"
SortExpression="PostalCode"></asp:BoundField>
<asp:BoundField HeaderText="Country" DataField="Country"
SortExpression="Country"></asp:BoundField>
<asp:BoundField HeaderText="Phone" DataField="Phone"
SortExpression="Phone"></asp:BoundField>
<asp:BoundField HeaderText="Fax" DataField="Fax"
SortExpression="Fax"></asp:BoundField>
</Fields>
<FieldHeaderStyle ForeColor="#FFFFCC" Font-Bold="True"
BackColor="#990000"></FieldHeaderStyle>
<HeaderStyle ForeColor="#FFFFCC" Font-Bold="True"
BackColor="#990000"></HeaderStyle>
<EditRowStyle ForeColor="#663399" Font-Bold="True"
BackColor="#FFCC66"></EditRowStyle>
</asp:DetailsView>
</div>
</form>
</body>
</html>
Figures 32 and 33 show the drill-down report in action.
.gif "Click here for larger image.")
Figure 32 (Click on the graphic for a larger image)
.gif "Click here for larger image.")
Figure 33 (Click on the graphic for a larger image)