Displaying Hierarchical data with the ASP.NET Data Grid

Date: Tuesday, September 17, 2002
By: Akash Bhargava

I had been looking for ways to show hierarchical data in a data grid, but in vain. I never found a way that would solve my purpose! My problem was simple. I wanted to display the child records in an indented manner using the data grid; the indentation would emphasize the hierarchy. This article assumes the reader has elementary knowledge of ADO.NET and SQL 2000. The sample uses the Northwind Database and is written in VB.NET.

Preparing our hierarchical data
So can we show order and order details in the same data grid? Yes! I will walk you through one of the simplest ways on how to display the entire data using a data grid.

The first thing we do here is preparing the hierarchical data that we want to display. The following is the code behind file named HDataGrid.aspx.vb.

• Step 1: In the Page_load event open a connection and connect to the northwind database.
• Step 2: Create a data adapter and retrieve the orders and the order details.
• Step 3: Create and fill the dataset.
• Step 4: Give Meaning full names to the tables.
• Step 5: Establish a Parent child relationship between the two tables based on the common field OrderID.
• Step 6: Add the relationship to the Dataset
• Step 7: Bind the data grid to the default view of the Orders Table.

Find below the code behind file DataGrid.aspx.vb, which contains the preparation of the data and the binding to the ASP.NET datagrid.

Imports System.Data.SqlClient

Public Class HDataGrid
Inherits System.Web.UI.Page
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

  ‘ Step 1 Open a connection
Dim OrdersConnection As New SqlConnection("server=(local);database=northwind;uid=sa;pwd=;")

‘ Step 2 Retrieve the orders and the order details
Dim OrderAdapter As New SqlDataAdapter("Select * from Orders; select OrderID ,Products.ProductName,[order Details].Unitprice,[order Details].Quantity,[order Details].discount from [order Details],Products where [Order Details].ProductId=Products.ProductID", OrdersConnection)

‘Step 3 Create and fill the dataset
Dim OrderDataSet As New DataSet()

OrderAdapter.Fill(OrderDataSet)

  ‘Step 4 Give Meaning full name to the tables
OrderDataSet.Tables(0).TableName = "Orders"
OrderDataSet.Tables(1).TableName = "Order Details"

‘ Step 5 Establish a Parent child relationship between the two tables based on the common field OrderID.
Dim Parent As DataColumn = OrderDataSet.Tables("Orders").Columns("OrderID")
Dim Child As DataColumn = OrderDataSet.Tables("Order Details").Columns("OrderID")

Dim OrderRelation As DataRelation = New DataRelation("OrderRelation", Parent, Child, False)
  
   ‘Step 6 Add the relationship to the Dataset  
OrderDataSet.Relations.Add(OrderRelation)
    
   ‘Step 7 Bind the data grid to the default view of the Orders Table
DataGrid1.DataSource = OrderDataSet.Tables("Orders").DefaultView
DataBind()


End Sub


Private Sub DataGrid1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemDataBound
e.Item.Cells(0).BackColor = System.Drawing.Color.Ivory

End Sub
End Class

In the HdataGrid.aspx we make a Template column for the Datagrid1 and embed another data grid into this item template. The Parent data grid will be used to show the master record i.e. the orders and the child data gird will be used to show the details i.e. Order details.
Look for the way we assign the data source to the child grid.

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

This is the crux of the entire exercise. Based on the current parent row the DataRowView .CreateChildView which takes the name of the relationship (That we established earlier)as a parameter fetches the child row and binds it to the child data grid. This is exactly how it is done.

Find below the ASPX page, HdataGrid.aspx.

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="HDataGrid.aspx.vb" Inherits="WebSample.HDataGrid"%>
<%@ Import namespace="System.Data"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>Data Grid</title>
  <meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
  <meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
  <meta name="vs_defaultClientScript" content="JavaScript">
  <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body>
<form id="FrmDataGrid" method="post" runat="server">
  <P align="center">
  <asp:DataGrid id="DataGrid1" BorderColor="Red" runat="server" ShowHeader="False" Width="570px" CellPadding="0" CellSpacing="0" AutoGenerateColumns="False">
  <Columns>
  <asp:TemplateColumn>
    <ItemTemplate>
    <TABLE cellSpacing="0" cellPadding="0" width="100%" border="0">
    <TR>
    <TD bgColor="#999966"><B>Order No:
        <%# DataBinder.Eval(Container.DataItem, "OrderID") %>  </B>
      </TD>
  </TR>
    <TR>
    <TD align="right">
<asp:DataGrid id=DataGrid2 runat="server" AutoGenerateColumns="False" BorderColor="#33FF33" DataKeyField="OrderID" DataSource='<%# CType(Container.DataItem,DataRowView).CreateChildView("OrderRelation") %>'>
<HeaderStyle Font-Bold="True" ForeColor="#CC0066" BackColor="#CCCC99"></HeaderStyle>
    <Columns>
                          <asp:TemplateColumn>
    <ItemTemplate>
<table border="0" cellpadding="0" cellspacing="0" width="100%">
  <tr>
        <td width="50px"> </td>
      </tr>
  </table>
  </ItemTemplate>
  </asp:TemplateColumn>
  <asp:BoundColumn Visible="False" DataField="OrderID" ReadOnly="True"></asp:BoundColumn>
  <asp:TemplateColumn HeaderText="Product Name">
  <HeaderStyle Width="300px"></HeaderStyle>
                            <ItemTemplate>
    <%# DataBinder.Eval(Container.DataItem, "ProductName") %>
                            </ItemTemplate>
  </asp:TemplateColumn>
  <asp:BoundColumn DataField="UnitPrice" HeaderText="Unit Price"></asp:BoundColumn>
  <asp:BoundColumn DataField="Quantity" HeaderText="Quantity"></asp:BoundColumn>
  <asp:BoundColumn DataField="Discount" HeaderText="Discount"></asp:BoundColumn>
  </Columns>
  </asp:DataGrid></TD>
      </TR>
      </TABLE>
    </ItemTemplate>
    </asp:TemplateColumn>
    </Columns>
  </asp:DataGrid></P>
  </form>
  </body>
</HTML>

And now the icing on the cake - a screenshot of the final result:



Conclusion
The same concept can be applied to Data List and Data Repeater controls also. Try this for yourself.
Hope you enjoyed reading this article. Feel free to provide your comments and feedback below.

Note: Don’t forget to check and change the connection string in the code sample.

---

© 2001 Wimdows.net

---

Original Article: http://www.wimdows.net/articles/article.aspx?aid=19