Working with Nested List Controls in ASP.NET

This chapter is from the book

ASP.NET 1.1 Insider Solutions

Learn More Buy

This chapter is from the book

This chapter is from the book 

ASP.NET 1.1 Insider Solutions

Learn More Buy

In This Chapter

Displaying Related Data in Nested DataGrid Controls

A Master/Detail Display with DataList and DataGrid Controls

Summary

ASP.NET introduced many extremely useful server controls that can reduce development time and make it easier to create attractive Web pages with a lot less programming effort. Among these is the DataGrid control, which—for developers building pages that display and manage data—has become almost the de facto solution. However, many developers still have problems using the DataGrid control when stepping beyond the basic mode that it provides for displaying rows of data.

This chapter looks particularly at displaying hierarchical data from related tables or row sets. This is common in many applications, and this chapter investigates four alternative approaches. It also looks at the specific issue of providing a master/detail display where the user can choose to show or hide the related rows.

Displaying Related Data in Nested DataGrid Controls

Developers regularly find that they have to build pages that can display data from related tables in a data source and, at first glance, the DataGrid control doesn't seem to be able to do this. Many third-party grid controls are available for ASP.NET that are designed to provide this feature, but it's quite easy to achieve the same effect with a DataGrid control or a combination of ASP.NET list controls.

The process requires that the list controls be nested so that each row within the grid that displays the parent rows contains a list control bound to the related child rows. There are several oft-used approaches for selecting the correct set of child rows for each parent row. The following are the four most common:

• Declarative nested binding to a DataSet instance—This is the simplest approach, and it requires no code to be written except that required to generate and populate the DataSet instance the first time that the page is opened.

• Filling nested DataGrid controls programmatically from a DataSet instance—This technique allows you to extract all the data you want in one operation, while still maintaining control over the selection of child rows, and access or modify the row contents as required.

• Declarative nested binding to a custom function that returns a row set—This technique combines the previous two approaches, allowing custom handling of the data when creating the row set to be combined with the simple declarative approach to performing the binding.

• Filling nested DataGrid controls from a DataReader instance—This is a useful technique when you need to display only a few rows. It allows you to dynamically select the child rows you want for each parent row, and it gives you full control over the content at the point where the grid is being populated.

Declarative Nested Binding to a DataSet Instance

The simplest way to populate nested DataGrid controls is to use syntax that allows the child rows to be specified using declarative techniques. In other words, you specify the binding properties of the nested grid at design time, and ASP.NET fetches the rows and performs the binding to generate the output at runtime.

The sample page in Figure 4.1 shows nested binding of three DataGrid controls, displaying data extracted from the Northwind sample database that is provided with SQL Server. The outer, or root, DataGrid control displays details from the Customers table, and the grid nested within it displays a list of orders (in the Order History column). However, this nested grid contains within its Details column another DataGrid control, which is bound to data extracted from the Order Details table. The result is a hierarchical display of all three sets of related data rows.

Figure 4.1 Nested DataGrid controls, using declarative data binding.

The page starts the usual Page and Import directives:

<%@Page Language="VB" EnableViewState="False" %>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>

However, in this case you turn off viewstate for the page. You don't intend to perform postbacks, which means that you'll only generate the data once, and you don't need to preserve the values in the grid, so there is no point in storing it in the viewstate.

Declaring the DataGrid Controls

Listing 4.1 shows the declaration of the <form> section of the page and the three DataGrid controls. It also includes a Label control where you will display any data access errors. The declaration of the DataGrid control includes a range of style and formatting attributes, including declarations of the <HeaderStyle>, <ItemStyle>, and <AlternatingItemStyle> elements.

Listing 4.1—The Declaration of the DataGrid Controls

<form runat="server">

 <asp:Label id="lblErr" EnableViewState="False" runat="server" />

 <asp:DataGrid id="dgr1" runat="server"
    Font-Size="10" Font-Name="Tahoma,Arial,Helvetica,sans-serif"
    BorderStyle="None" BorderWidth="1px" BorderColor="#deba84"
    BackColor="#DEBA84" CellPadding="5" CellSpacing="1"
    AutoGenerateColumns="False" >
  <HeaderStyle Font-Bold="True" ForeColor="#ffffff" 
         BackColor="#b50055" />
  <ItemStyle BackColor="#FFF7E7" VerticalAlign="Top" />
  <AlternatingItemStyle backcolor="#ffffc0" />
  <Columns>

   <asp:TemplateColumn HeaderText="Customer Details">
    <ItemTemplate>
     <b><%# Container.DataItem("CompanyName") %></b><br />
     City: <%# Container.DataItem("City") %><br />
     Country: <%# Container.DataItem("Country") %><br />
     CustomerID: "<%# Container.DataItem("CustomerID") %>"
    </ItemTemplate>
   </asp:TemplateColumn>

   <asp:TemplateColumn HeaderText="Order History">
    <ItemTemplate>

     <asp:DataGrid id="dgr2" runat="server"
        BorderStyle="None" BorderWidth="0" Width="100%"
        BackColor="#deba84" CellPadding="5" CellSpacing="2"
        AutoGenerateColumns="False"
        DataSource='<%# CType(Container.DataItem, _
         DataRowView).CreateChildView("CustOrders") %>' >
      <HeaderStyle BackColor="#c0c0c0" />
      <ItemStyle Font-Bold="True" VerticalAlign="Top" />
      <Columns>

       <asp:BoundColumn DataField="OrderID" 
          HeaderText="Number" />

       <asp:TemplateColumn HeaderText="Details">
        <ItemTemplate>
         <asp:Label runat="server"
          Text='<%# DataBinder.Eval(Container.DataItem, _
          "OrderDate", "{0:dddd dd MMM yyyy}") %>' />

         <asp:DataGrid id="dgr3" runat="server"
          BorderStyle="None" BorderWidth="0"
          CellPadding="3" CellSpacing="0" Width="100%"
          AutoGenerateColumns="False"
          DataSource='<%# CType(Container.DataItem, _
          DataRowView).CreateChildView( _
                 "OrdersODetails") %>' >
          <HeaderStyle BackColor="#c0c0c0" />
          <Columns>
           <asp:BoundColumn DataField="ProductID"
              HeaderText="ID" />
           <asp:BoundColumn DataField="ProductName"
              HeaderText="Product" />
           <asp:BoundColumn DataField="Quantity"
              ItemStyle-HorizontalAlign="Right"
              HeaderStyle-HorizontalAlign="Right"
              HeaderText="Qty" />
           <asp:BoundColumn DataField="UnitPrice"
              DataFormatString="${0:f2}"
              ItemStyle-HorizontalAlign="Right"
              HeaderStyle-HorizontalAlign="Right"
              HeaderText="Price"/>
          </Columns>
         </asp:DataGrid>

        </ItemTemplate>
       </asp:TemplateColumn>
      </Columns>
     </asp:DataGrid>

    </ItemTemplate>
   </asp:TemplateColumn>
  </Columns>
 </asp:DataGrid>

</form>

You can't rely on the autogeneration feature for the columns in the grid in this example because you want to include a DataGrid control in one of the columns. So you include the AutoGenerateColumns="False" attribute in the declaration of the main root DataGrid control and include a <Columns> element where you declare the columns you want.

Inside this <Columns> element, you specify a <TemplateColumn> element that displays a range of values extracted from the data rows that will be used to populate this grid. You include the company name, city, and country, as well as the value of the key column named CustomerID. You specify each by using the standard syntax for accessing the DataItem instance (the current row) of the Container object (the binding context that references the set of data rows) and specifying the column name:

<%# Container.DataItem("column-name") %>

The second column in this root grid is another <TemplateColumn> element, but this time it contains a nested DataGrid control (id="dgr2")—so each row in the root DataGrid control will contain an instance of the nested DataGrid control to display order details. This DataGrid control also disables auto-generation of columns and contains a <Columns> element. The important point to note here is that you declare the data source for this nested DataGrid control at design time. Later in this chapter you'll see how the declaration you've used works.

Meanwhile, the nested DataGrid control contains one BoundColumn element to display the value of the order ID (the row key) for each order for this customer and a <TemplateColumn> element that contains another nested DataGrid control (id="dgr3"). An instance of this third DataGrid control will be generated for every order and will be used to display the order lines for this order. In this case, you just use the normal BoundColumn elements to display the product ID, name, quantity, and unit price.

DataSource='<%# CType(Container.DataItem, _
      DataRowView).CreateChildView("CustOrders") %>'

This statement converts the data source row that is providing the data to populate the current row of the root grid into a DataRowView instance, and then it calls its CreateChildView method. The name of a relationship between the current data source row set and the child row set must be provided, and the function returns a set of child rows that are related to the current row in the parent row set.

For this to work, the data must be stored in such a way that the relationship between the parent and child row sets is available, and the obvious way to meet this criterion is to populate tables in an ADO.NET DataSet instance with the rows from the data source. Then you create the relationship(s) between these tables within the DataSet instance.

In this example, you have three DataGrid controls, so there are three sets of data rows in the DataSet instance that you use to populate them: data extracted from the Customers, Orders, and Order Details tables in the Northwind database. The DataSet instance that contains these rows also contains two relationships (DataRelation objects), named CustOrders and OrdersODetails (see Figure 4.2).

Figure 4.2 The structure of the DataSet instance for the nested DataGrid control example.

The first of these relationships is used to create the row set for the data source of the second DataGrid control (id="dgr2") that displays details (such as the delivery address) of each order for the current customer. The second relationship is used to create the row set for the data source of the third DataGrid control (id="dgr3"), which displays the individual lines for each order:

DataSource='<%# CType(Container.DataItem, _
      DataRowView).CreateChildView("OrdersODetails") %>' >

When you subsequently bind the root DataGrid control to its data source, the nested grids will automatically be populated with the matching sets of child rows.

Populating a DataSet Instance and Adding Relationships

The code in the sample page is responsible for creating the DataSet instance and adding the relationships between the tables to it. Listing 4.2 declares a page-level variable to hold the DataSet instance and then calls a separate routine named FillDataSet in the Page_Load event handler to fill it with the data and relationships required. When you have the DataSet instance, you bind it to the root DataGrid, specify which table it should draw its data from, and call the DataBind method to initiate the process of binding all three DataGrid objects.

Listing 4.2—The Page-Level Variable and the Code in the Page_Load Event Handler

' variable to hold reference to DataSet across routines
Dim oDataSet As DataSet

Sub Page_Load()

 'fill the data set with some rows from database
 FillDataSet("c%")

 ' bind the data to the grid for display
 dgr1.DataSource = oDataSet
 dgr1.DataMember = "Customers"
 dgr1.DataBind()

End Sub

Listing 4.3 shows the FillDataSet routine that is used to populate the DataSet instance. This routine receives a String object that contains the full or partial match for the customer ID whose orders you want to list. (In Listing 4.2, it is set to "c%" to extract order details for all customers whose ID starts with c.) Using this ID, you can build the SQL statements you require to extract the appropriate sets of rows from the Customers, Orders, and Order Details tables in the database. You have to join the Products table in the third SQL statement to get the name of the product because the Order Details table only contains a foreign key to the rows in this table—not the product name.

Listing 4.3—The Code to Populate the DataSet Instance

Sub FillDataSet(sCustID As String)

 ' get DataSet with rows from Northwind tables

 Dim sCustSql As String _
  = "SELECT CustomerID, CompanyName, City, Country " _
  & "FROM Customers WHERE CustomerID LIKE '" & sCustID & "'"
 Dim sOrdersSql As String _
  = "SELECT CustomerID, OrderID, OrderDate FROM Orders " _
  & "WHERE CustomerID LIKE '" & sCustID & "'"
 Dim sDetailsSql As String _
  = "SELECT [Order Details].OrderID, Products.ProductID, " _
  & "Products.ProductName, [Order Details].Quantity, " _
  & "[Order Details].UnitPrice " _
  & "FROM [Order Details] JOIN Products " _
  & "ON [Order Details].ProductID = Products.ProductID " _
  & "WHERE [Order Details].OrderID IN " _
  & " (SELECT OrderID FROM Orders " _
  & " WHERE CustomerID LIKE '" & sCustID & "')"

 Dim sConnect As String _
  = ConfigurationSettings.AppSettings("NorthwindOleDbConnectString")
 Dim oConnect As New OleDbConnection(sConnect)
 oDataSet = New DataSet()

 Try

  ' fill DataSet with three tables
  Dim oDA As New OleDbDataAdapter(sCustSQL, oConnect)
  oConnect.Open()
  oDA.Fill(oDataSet, "Customers")
  oDA.SelectCommand.CommandText = sOrdersSql
  oDA.Fill(oDataSet, "Orders")
  oDA.SelectCommand.CommandText = sDetailsSql
  oDA.Fill(oDataSet, "OrderDetails")
  oConnect.Close()

  ' create relations between the tables
  Dim oRel As New DataRelation("CustOrders", _
   oDataSet.Tables("Customers").Columns("CustomerID"), _
   oDataSet.Tables("Orders").Columns("CustomerID"))
  oDataSet.Relations.Add(oRel)
  oRel = New DataRelation("OrdersODetails", _
   oDataSet.Tables("Orders").Columns("OrderID"), _
   oDataSet.Tables("OrderDetails").Columns("OrderID"))
  oDataSet.Relations.Add(oRel)

 Catch oErr As Exception

  ' be sure to close connection if error occurs
  If oConnect.State <> ConnectionState.Closed Then
   oConnect.Close()
  End If

  ' display error message in page
  lblErr.Text = oErr.Message

 End Try

End Sub

Next, the connection string is extracted from web.config, and you can create a Connection instance and a new empty DataSet instance. Then, within the Try...Catch construct, you create a DataAdapter instance, open the connection, and fill the three tables—changing the CommandText property of DataAdapter to the appropriate SQL statement as you go.

Creating and Adding the DataRelation Instances

When the tables are filled, you can create and add the relationships you need between them. You create a new DataRelation object by specifying the name you want to assign to it, the column in the parent table that contains the key to match to the child table, and the column in the child table that contains this value as a foreign key. Figure 4.3 shows the relationships between the Northwind database tables that are used in this example, as well as the primary key and foreign keys in each table.

Figure 4.3 The relationships between the tables used in this example.

In Listing 4.3 you can see that to create the relationship between the Customers and Orders tables in the DataSet instance, you use the following:

Dim oRel As New DataRelation("CustOrders", _
 oDataSet.Tables("Customers").Columns("CustomerID"), _
 oDataSet.Tables("Orders").Columns("CustomerID"))

Then, to add this relationship to the DataSet instance, you use the following:

oDataSet.Relations.Add(oRel)

To create the relationship between the Orders and Order Details tables and add that relationship to the DataSet instance, you use the following:

oRel = New DataRelation("OrdersODetails", _
 oDataSet.Tables("Orders").Columns("OrderID"), _
 oDataSet.Tables("OrderDetails").Columns("OrderID"))
 oDataSet.Relations.Add(oRel)

Other than closing the connection if it's open and displaying a message if an error occurs, this is all the code you need. When the page is opened, the DataSet instance is filled with data, and the three relationships are added. Then the DataBind method causes the three DataGrid controls to be populated. The output you want (refer to Figure 4.1) is then generated automatically.

Filling Nested DataGrid Controls with a DataSet Instance

Instead of using declarative binding, as demonstrated in the previous example, you might want to exert more control over the binding of child rows to their respective DataGrid controls. The example in this section uses the same DataSet instance as the previous example, but in this case, you'll bind the nested DataGrid controls dynamically, using code, instead of defining the bindings declaratively.

This approach allows you to access the data row and examine the values, modify them as required, and even decide whether to bind the nested DataGrid control at runtime. You could, for example, test whether a product was in stock before displaying the details or omit discontinued products when generating sales forecasts.

Figure 4.4 shows the output for this example, and you can see that there are subtle differences from the preceding example. This example omits the details of orders that have not yet been shipped. (In this example, the orders numbered 10782 and 10937 are visible in Figure 4.1.) This example also highlights the names of products that have unit prices greater than $10.00, using bold italic text.

Figure 4.4 A page that demonstrates nested data binding to a DataSet instance.

The Changes to This Example when Declaring the DataGrid Controls

When you declare the DataGrid controls in this example, you no longer include the DataSource attributes for the two nested grids (dgr1 and dgr2), but you do add two more: the DataKeyField and OnItemDataBound attributes.

The DataKeyField attribute specifies the name of the column in the source row set that contains the primary key for each row. You can easily extract this value for any row by referring to the DataKeys(row-index) property of the DataGrid control.

The OnItemDataBound attribute specifies the name of an event handler that the DataGrid control will execute each time it binds to the source data for a row. In this event handler, you can access the ASP.NET server controls in the current row and the row in the source row set that is providing the data for the row.

The opening tag of the root DataGrid control (id="dgr1") looks like this:

<asp:DataGrid id="dgr1" runat="server"
   ...
   AutoGenerateColumns="False"
   DataKeyField="CustomerID"
   OnItemDataBound="BindOrdersGrid">

The opening tag of the first nested DataGrid control (id="dgr2") looks like this:

<asp:DataGrid id="dgr2" runat="server"
   ...
   AutoGenerateColumns="False"
   DataKeyField="OrderID"
   OnItemDataBound="BindOrderItemsGrid">

The Changes to This Example when Populating the DataSet

The only change to the code used to populate the DataSet instance and add the relationships to it occurs because, this time, you want to be able to access the value of the ShippedDate column in the Orders table for each row; this is how you detect whether the order has shipped. All you do is add the ShippedDate column to the SQL statement that extracts the rows from the Orders table:

Dim sOrdersSql As String _
 = "SELECT CustomerID, OrderID, OrderDate, ShippedDate " _
 & "FROM Orders WHERE CustomerID LIKE '" & sCustID & "'"

Handling the ItemDataBound Events

In this example, you've removed the DataSource attributes from the two nested DataGrid controls, which means that they will not display anything when you view the page. All you'll see is the list of customers, generated when the root DataGrid is bound to the Customers table in the DataSet instance by code in the Page_Load event handler. However, both this root DataGrid control and the first of the nested DataGrid controls will execute the custom routines when the ItemDataBound event occurs.

Listing 4.4—The BindOrdersGrid Event Handler

Sub BindOrdersGrid(sender As Object, e As DataGridItemEventArgs)

 ' see what type of row (header, footer, item, etc.) caused the event
 Dim oType As ListItemType = CType(e.Item.ItemType, ListItemType)

 ' only process it if it's an Item or AlternatingItem event
 If oType = ListItemType.Item _
 Or oType = ListItemType.AlternatingItem Then

  ' get a reference to the DataGrid control in this row
  Dim oGrid As DataGrid = CType(e.Item.FindControl _
                 ("dgr2"), DataGrid)

  ' get value of CustomerID for this row from DataKeys collection
  Dim sKey As String = dgr1.DataKeys(e.Item.ItemIndex)

  ' get a DataView containing just the current row in
  ' the Customers table within the DataSet
  Dim oView, oChildView As DataView
  oView = oDataSet.Tables("Customers").DefaultView
  oView.RowFilter = "CustomerID = '" & sKey & "'"
  oChildView = oView(0).CreateChildView( _
         oDataSet.Relations("CustOrders"))

  ' find rows that have not yet shipped and delete them
  ' have to go backwards through row collection to avoid
  ' errors as indexes of rows change when one is deleted
  For iIndex As Integer = (oChildView.Count - 1) To 0 Step -1
   If oChildView(iIndex)("ShippedDate").ToString() = "" Then
    oChildView(iIndex).Delete()
   End If
  Next

  ' bind nested "orders" DataGrid to child DataView
  oGrid.DataSource = oChildView
  oGrid.DataBind()

 End If

End Sub

The next step is to get a reference to the DataGrid control within the current row. The event handler is executed once for each row in the root DataGrid control. It receives a DataGridItemEventArgs instance that contains more details of the event and a reference to the current row in the DataGrid control as a DataGridItem object. This object has a whole range of properties that can be used to set the style of the row, such as the background and foreground colors, borders, font, text alignment, and so on. However, the properties and methods you're usually most interested in when accessing or manipulating the contents of a row are those shown in Table 4.1.

Table 4.1 Commonly Used Properties and Methods of the DataGridItem Object

You can use the FindControl method to locate the DataGrid control we're looking for in the current row. You have to cast the result to the correct type on return because the FindControl method returns the reference as a generic Object type:

Dim oGrid As DataGrid = CType(e.Item.FindControl("dgr2"), DataGrid)

Then you generate the set of child rows that match the current row by creating a filtered DataView instance on the Customers table, which will only contain the row for the current customer. You do this by extracting the ID of the current customer from the DataKeys collection of the DataGrid control, using the ItemIndex property of the DataGridItem instance passed to the routine (refer to Table 4.1).

Then, to limit the rows that are displayed in the DataGrid control, you set the RowFilter property of the default DataView instance of the Customers table, as shown in this section of the code:

Dim sKey As String = dgr1.DataKeys(e.Item.ItemIndex)
Dim oView, oChildView As DataView
oView = oDataSet.Tables("Customers").DefaultView
oView.RowFilter = "CustomerID = '" & sKey & "'"

Now you can use the CreateChildView method of the first (and only) row in the DataView instance to create the set of related child rows from the Orders table. You do this the same way as in the previous declarative binding example, specifying the name of the DataRelation instance that links the two tables in the DataSet instance:

oChildView = oView(0).CreateChildView( _
       oDataSet.Relations("CustOrders"))

At this point, you can perform any actions you want to carry out on the source data or on the DataGrid row and its contents. In this example, you want to hide any rows that have not yet shipped. You can do this by simply deleting them from the child DataView instance. However, because the index of the remaining rows changes when a row is deleted, you have to iterate through the rows in reverse order:

For iIndex As Integer = (oChildView.Count - 1) To 0 Step -1
 If oChildView(iIndex)("ShippedDate").ToString() = "" Then
  oChildView(iIndex).Delete()
 End If
Next

Then, when you're happy with the contents of the DataView instance, you can bind it to the nested DataGrid control to which you're holding a reference in the oGrid variable:

oGrid.DataSource = oChildView
oGrid.DataBind()

This causes the DataGrid control showing the orders for the current customer to generate its contents (a list of orders for this customer) for display. However, remember that you also declared an ItemDataBound event handler for this DataGrid control—and in it you'll perform much the same process you've just seen to populate the third DataGrid control, which contains the list of order lines.

Listing 4.5—The BindOrderItemsGrid Event Handler

Sub BindOrderItemsGrid(sender As Object, e As DataGridItemEventArgs)

 ' see what type of row (header, footer, item, etc.) caused the event
 Dim oType As ListItemType = CType(e.Item.ItemType, ListItemType)

 ' only process it if it's an Item or AlternatingItem event
 If oType = ListItemType.Item _
 Or oType = ListItemType.AlternatingItem Then

  ' get the value of the CustomerID column
  ' argument sender is a reference to the containing DataGrid
  Dim iKey As Integer = sender.DataKeys(e.Item.ItemIndex)

  ' get a reference to the DataGrid control in this row
  Dim oGrid As DataGrid = CType(e.Item.FindControl("dgr3"), DataGrid)

  ' get a DataView containing just the current row in
  ' the Orders table within the DataSet
  Dim oView, oChildView As DataView
  oView = oDataSet.Tables("Orders").DefaultView
  oView.RowFilter = "OrderID = " & iKey
  oChildView = oView(0).CreateChildView( _
         oDataSet.Relations("OrdersODetails"))

  ' find rows where unit price is greater 
  ' than $10.00 and highlight product name 
  For iIndex As Integer = 0 To oChildView.Count - 1
   If oChildView(iIndex)("UnitPrice") > 10 Then
    oChildView(iIndex)("ProductName") = "<b><i>" _
         & oChildView(iIndex)("ProductName") & "</i></b>"
   End If
  Next

  ' bind nested "order details" DataGrid to child DataView
  oGrid.DataSource = oChildView
  oGrid.DataBind()

 End If

End Sub

After checking the type of item that the event was raised for, the next task is to get a reference to the child DataGrid control that you want to populate with the lists of order lines from the OrderDetails table in the DataSet instance. In the BindOrdersGrid routine, you accessed the DataKeys collection of the current DataGrid control (the one that raised the ItemDataBound event) simply by referring to the DataGrid control with its ID. This works because there is only one instance of the root DataGrid control.

However, the DataGrid control for which you're handling the ItemDataBound event this time is one of multiple instances—there is an instance for each order for each customer. Therefore, you can't just use the ID of the grid (dgr2) to reference the DataKeys collection. The actual ID of each grid will be a combination of the parent grid control ID, any intermediate container control IDs, and the ID of this DataGrid control—in other words, something like "dgr1__ctl2_dgr2".

However, remember that event handlers pass a reference to the control that raised the event as the first (sender) parameter. You can use this to get a reference to the DataKeys collection, and from it you can get the OrderID value of the current order. Then you can get a reference to the child grid control in this row (the one that will display the order lines), using the FindControl method of the current DataGridItem instance as before.

The next section of code in Listing 4.5 creates the child DataView instance you want to bind to the DataGrid control in this row, using the same techniques as in Listing 4.4. However, before you bind this row set to the DataGrid control, you "massage" it by checking for any items that have a unit price greater than $10.00. For each one you find, you just add some formatting elements to the text value in the ProductName column of that row in the DataView instance, before binding it to the current DataGrid control to display the results.

Declarative Nested Binding to a Custom Function

The third technique for binding related data to nested list controls is actually a combination of the two techniques just described. ASP.NET supports declarative data binding statements that bind to the result of a function, using the following syntax:

<%# function-name(parameters) %>

You can use this technique to insert the result of a function almost anywhere in an ASP.NET page. You can use it simply to generate output directly. For example, if you have a function that returns the description for a specific paragraph in a document, you can insert the result into the page by using the following:

<p>This paragraph describes <%# GetParaDescription(42) %></p>

Alternatively, you can bind the function result to a property of a server control. For example, you can set the Text property of a Label control by using the same function like this:

<asp:Label id="mylabel" runat="server" 
   Text='<%# GetParaDescription(42) %>' />

Your code simply has to call the DataBind method of the appropriate container control to force the binding to take place. In the two preceding cases, you'd call the DataBind method of the Page object itself.

Of course, this approach to declarative binding is just what you used in the first example in this chapter. You specified the DataSource property of the nested DataGrid controls, using an attribute such as this:

DataSource='<%# CType(Container.DataItem, _
        DataRowView).CreateChildView("OrdersODetails") %>'

CreateChildView is a method of the DataRowView class, and it returns a DataView instance (a row set) that can be used as the source for a DataGrid control. So you shouldn't be surprised to see in the next example that you can use the same approach but specify a custom function that returns a row set and have it used to populate the nested DataGrid controls.

The Custom Functions to Return Row Sets

In the previous example, you handled the ItemDataBound event of two of the DataGrid controls so that you could create and then massage the row sets before using them to populate their respective nested DataGrid controls. In this example, you use the same core code to generate the row sets you need, and you modify their contents, as in the previous example. However, this time the two sections of code (which were in the BindOrdersGrid and BindOrderItemsGrid event handlers) are extracted and converted into functions that return a DataView instance.

Listing 4.6 shows the two functions, named GetOrdersGridRows and GetOrderItemsGridRows. Obviously, this time, because you aren't using the functions to handle events, you don't have access to the DataGridItemEventArgs objects that contain details of the event and that are passed to the ItemDataBound event handler. However, the only information you actually need to be able to create the appropriate row set is the value of the key for the current row in the DataGrid control.

Listing 4.6—The Custom Functions That Return Row Sets

Function GetOrdersGridRows(sRowKey As String) As DataView

 ' get a DataView containing just the current row in
 ' the Customers table within the DataSet
 Dim oView, oChildView As DataView
 oView = oDataSet.Tables("Customers").DefaultView
 oView.RowFilter = "CustomerID = '" & sRowKey & "'"
 oChildView = oView(0).CreateChildView( _
        oDataSet.Relations("CustOrders"))

 For iIndex As Integer = (oChildView.Count - 1) To 0 Step -1
  If oChildView(iIndex)("ShippedDate").ToString() = "" Then
   oChildView(iIndex).Delete()
  End If
 Next

 Return oChildView

End Function


Function GetOrderItemsGridRows(iRowKey As Integer) As DataView

 ' get a DataView containing just the current row in
 ' the Orders table within the DataSet
 Dim oView, oChildView As DataView
 oView = oDataSet.Tables("Orders").DefaultView
 oView.RowFilter = "OrderID = " & iRowKey
 oChildView = oView(0).CreateChildView( _
        oDataSet.Relations("OrdersODetails"))

 For iIndex As Integer = 0 To oChildView.Count - 1
  If oChildView(iIndex)("UnitPrice") > 10 Then
   oChildView(iIndex)("ProductName") = "<b><i>" _
        & oChildView(iIndex)("ProductName") & "</i></b>"
  End If
 Next

 Return oChildView

End Function

Assuming that you can pass this key as a parameter to your functions, the remaining code (which actually generates the DataView instance) is identical to that in Listings 4.4 and 4.5. You reference the single row in the parent table that matches the key supplied as a parameter, and you use the CreateChildView method to generate the child row set. Then you remove any rows for orders that have not shipped or highlight the names of products over $10.00, just as before.

Binding DataGrid Controls to Custom Functions

The two functions described in the preceding section replace the two event handlers that are used in the previous example, so you must remove the OnItemDataBound attributes from the two DataGrid controls. The server-side code to populate the DataGrid control, add the DataRelation instances to it, and initiate the binding of the root DataGrid control in the Page_Load event handler is identical to the code in the previous example.

The only other change to the page is the way you declare the DataSource attributes for the two DataGrid controls. The first of the nested DataGrid controls (id="dgr2"), which displays the list of orders for each customer, contains the following DataSource attribute:

DataSource='<%# GetOrdersGridRows( _
        Container.DataItem("CustomerID")) %>'

The GetOrdersGridRows function takes a parameter that is the ID of the current customer. Normally, in the ItemDataBound event handler, you'd get this value from the DataKeys collection of the root DataGrid control. However, the DataView instance that provides the data for this DataGrid control contains the value of the customer ID in each row. It is included in the SQL statement, and you use it when you generate the value for the Customer Details column. You can therefore refer to it here and use it as the parameter value for the function, in the same way you would to populate a column in the DataGrid control.

The same logic applies to the second nested DataGrid control (id="dgr3"), which displays the list of order lines for each order. In this case, the parameter is the order ID, and again it is in the row set you use to populate the parent DataGrid control. So you can set the DataSource property of the innermost DataGrid control by using the following:

DataSource='<%# GetOrderItemsGridRows( _
        Container.DataItem("OrderID")) %>'

Figure 4.5 shows the result of this example; you can see that it produces identical output to the previous example. This is to be expected because the code you use to generate the row sets is the same. Only the way that you apply it to binding the grid controls differs.

Figure 4.5 A sample page that uses declarative binding to custom functions.

Filling Nested DataGrid Controls from a DataReader Instance

The fourth and final approach to populating nested list controls doesn't use a DataSet instance as the source of the rows. Instead, it uses a DataReader instance to extract the data for the data store. Or, to be more precise, it uses multiple DataReader instances.

It's generally accepted that the DataReader class provides better performance than the DataSet approach when you're extracting data and using it in an ASP.NET page. Figures published by Microsoft while ASP.NET was under development suggested that there were gains of more than 20%, although ultimately the performance gain depends on how you actually end up using the data.

The DataReader Class Versus the DataSet Class

The DataReader class is far lighter weight than the DataSet class. It's really just a "pipe" that connects the results of a query in the database with the consumer in the ASP.NET page (or other type of application). When you're using ASP.NET server-side data binding, the DataReader class is generally the optimal solution, unless you need to cache the data after extracting it or pass it between the tiers of an application.

So how does the DataReader class work when you're performing nested data binding? In some ways, it makes the process more complicated. And rudimentary tests show that it doesn't tend to provide any performance increase unless there are only a few rows in the root row set.

The reason for this is that you can't create a hierarchy of tables and the relationships between them with a DataReader instance. You can only get one or more unrelated row sets from the data store. This means that each time you need a row set to populate a nested list control, you end up generating a DataReader instance, opening the connection, executing the query, and returning the row set.

Okay, so you could reduce the performance hit by reusing the same DataReader instance each time (although you'd have to close it and reopen it) and by holding the database connection open until all the row sets have been extracted. But this isn't likely to provide major performance gains because the real hit is the multiple trips to the database that are required.

Still, this technique might prove useful in certain scenarios, and you might decide to adopt it if you have pages with a shallow hierarchy and few rows in the root row sets. This is where any performance gains are most likely to be felt. Figure 4.6 shows the output from the sample page, and you can see that it is identical to the example shown in Figure 4.1. That example used a DataSet instance as the data source, but the declarations of the DataGrid controls, and the data itself, are the same.

Figure 4.6 A demonstration page that uses a DataReader instance to extract the data rows.

The Changes to This Example when Declaring the DataGrid Controls

The previous examples demonstrate the appearance and disappearance of the OnItemDataBound attributes in the DataGrid controls. Now they're back again. In this example, you handle the ItemDataBound event just as you did when we used a DataSet instance as the source for the DataGrid controls, in the second example in this chapter (refer to Figure 4.4).

So the root DataGrid control contains the attribute OnItemDataBound="BindOrdersGrid", and the nested DataGrid control that displays the list of orders for each customer contains the attribute OnItemDataBound="BindOrderItemsGrid". In fact, the declaration of the three grid controls is identical to what is used in the example of Figure 4.4—where you bound them to row sets extracted from a DataSet instance.

The Changes to the Server-Side Code in This Example

Using a DataReader instance instead of a DataSet instance requires an almost complete change to the server-side code in the page. Listing 4.7 shows the Page_Load event handler, which binds the root DataGrid control to its data source, and the three functions that return DataReader instances. The first of these is used to generate the row set containing a list of customers that is bound to the root DataGrid control in the Page_Load event handler.

Listing 4.7—The Page_Load Event Handler and the Routines to Fetch the Row Sets

Sub Page_Load()

 ' bind the data to the grid for display
 dgr1.DataSource = GetCustomers()
 dgr1.DataBind()

End Sub


Function GetCustomers() As OleDbDataReader

 Dim sSelect As String _
  = "SELECT CustomerID, CompanyName, City, Country " _
  & "FROM Customers WHERE CustomerID LIKE 'c%'"
 Return GetReader(sSelect)

End Function


Function GetOrders(sKey As String) As OleDbDataReader

 Dim sSelect As String _
  = "SELECT OrderID, OrderDate FROM Orders WHERE CustomerID='" & sKey & "'"
 Return GetReader(sSelect)

End Function


Function GetOrderLines(iKey As Integer) As OleDbDataReader

 Dim sSelect As String _
  = "SELECT Products.ProductID, Products.ProductName, " _
  & "[Order Details].Quantity, [Order Details].UnitPrice " _
  & "FROM [Order Details] JOIN Products " _
  & "ON [Order Details].ProductID = Products.ProductID " _
  & "WHERE OrderID=" & iKey.ToString()
 Return GetReader(sSelect)

End Function

The three functions shown in Listing 4.7 simply declare a SQL statement and then call the function named GetReader shown in Listing 4.8 to create the DataReader instance and return it. When creating the row sets for the list of orders or the list of order lines, you need a parameter that specifies the current customer ID or order ID. You can see in Listing 4.7 how these parameters are used to build the SQL statements.

Notice in Listing 4.8 that you specify the value CommandBehavior.CloseConnection as a parameter to the ExecuteReader method when you create the DataReader instance. This ensures that the connection will be closed when the DataReader instance is closed or when it goes out of scope.

Listing 4.8—The Routine to Create a DataReader Instance

Function GetReader(sSQL As String) As OleDbDataReader

 ' get DataReader for rows from Northwind tables
 Dim sConnect As String _
  = ConfigurationSettings.AppSettings("NorthwindOleDbConnectString")
 Dim oConnect As New OleDbConnection(sConnect)

 Try

  oConnect.Open()
  Dim oCommand As New OleDbCommand(sSQL, oConnect)
  Return oCommand.ExecuteReader(CommandBehavior.CloseConnection)

 Catch oErr As Exception

  ' be sure to close connection if error occurs
  If oConnect.State <> ConnectionState.Closed Then
   oConnect.Close()
  End If

  ' display error message in page
  lblErr.Text = oErr.Message

 End Try

End Function

Handling the ItemDataBound Events

As shown in the example in Figure 4.4, the Page_Load event handler initiates the process of displaying the related data by binding the root DataGrid control to the set of customer rows. This raises the ItemDataBound event for each row as it's bound, and in the event handler (BindOrdersGrid), you generate the appropriate set of order rows and bind it to the nested DataGrid control. This in turn causes the ItemDataBound event to be raised for each row in this DataGrid control. In the event handler for this event (BindOrderItemsGrid), you generate the matching set of order detail rows and bind it to the third DataGrid control.

Listing 4.9 shows the two event handlers BindOrdersGrid and BindOrderItemsGrid. As before, you have to check what type of item the event is being raised for, and then you can extract the value of the key from the current row. In the BindOrdersGrid routine, you reference the root DataGrid control, and in the BindOrderItemsGrid routine you use the reference to the DataGrid control that is passed to the event handler as the sender parameter.

Next, you get a reference to the nested DataGrid control in the current row, using the FindControl method of the DataGridItem object that is passed to the event handler. Then you can bind this grid to the result of the appropriate method for generating a DataReader instance.

Listing 4.9—The Event Handlers for the ItemDataBound Events

Sub BindOrdersGrid(sender As Object, e As DataGridItemEventArgs)

 ' see what type of row (header, footer, item, etc.) caused the event
 Dim oType As ListItemType = CType(e.Item.ItemType, ListItemType)

 ' only process it if it's an Item or AlternatingItem event
 If oType = ListItemType.Item _
 Or oType = ListItemType.AlternatingItem Then

  ' get value of CustomerID for this row from DataKeys collection
  Dim sKey As String = dgr1.DataKeys(e.Item.ItemIndex)

  ' get a reference to the DataGrid control in this row
  Dim oGrid As DataGrid = CType(e.Item.FindControl("dgr2"), DataGrid)

  ' bind nested "orders" DataGrid to DataReader
  oGrid.DataSource = GetOrders(sKey)
  oGrid.DataBind()

 End If

End Sub


Sub BindOrderItemsGrid(sender As Object, e As DataGridItemEventArgs)

 ' see what type of row (header, footer, item, etc.) caused the event
 Dim oType As ListItemType = CType(e.Item.ItemType, ListItemType)

 ' only process it if it's an Item or AlternatingItem event
 If oType = ListItemType.Item _
 Or oType = ListItemType.AlternatingItem Then

  ' get the value of the CustomerID column
  ' argument sender is a reference to the containing DataGrid
  Dim iKey As Integer = sender.DataKeys(e.Item.ItemIndex)

  ' get a reference to the DataGrid control in this row
  Dim oGrid As DataGrid = CType(e.Item.FindControl("dgr3"), DataGrid)

  ' bind nested "order details" DataGrid to DataReader
  oGrid.DataSource = GetOrderLines(iKey)
  oGrid.DataBind()

 End If

End Sub

As you can see, using the DataReader class is not that different from using the DataSet class as far as implementation is concerned. However, remember that you must consider the ramifications of the increased number of trips to the database that the DataReader approach requires.