As Web developers our motto has always been "why should the WinForms guys have all the cool stuff!" It has been both our frustration and delightful challenge to try and give our users as rich a UI experience as that of a thick client application. In the process we have had to come up with an amazing array of hacks and ingenious work-arounds to make it so. When ASP.NET previewed in beta we were like kids in a candy store rushing to try all the new sugar coated goodies that came with the .NET Framework. For the most part we breathed a collective sigh of contentment imagining all the great things we could do with these new tools and all the work-around code we would no longer need to write. As time wore on however, and Beta 1 led to Beta 2, the lustre came off some parts of our shiny new toy. For me one of those times was when I discovered that the ASP.NET DataGrid control didn't do hierarchical datasets. Rather than accepting the fact as a limitation of ASP.NET, I decided to resort to my old ways and test the limits of the DataGrid server control. What follows is one approach to displaying hierarchical data in an ASP.NET DataGrid.

The Concept
While there are a number of examples of showing hierarchical data using the Master/Detail record concept, what I was looking for was the ability to display collapsible child rows under those of their parents in the same grid. Something similar to the way the Winforms DataGrid works when you feed it a hierarchical dataset. Unfortunately the ASP.NET DataGrid will only accept one of the tables within a hierachical dataset in its .DataBind method. In order to work around this we need to create a single DataTable object that contains all the rows we want to show when all the nodes of the hierarchy are expanded. Along the way we will leave ourselves some clues in the DataTable rows that tell us which ones are parent nodes and which are children. Once we have created this specially formatted DataTable we can then bind it to the DataGrid, apply a few formatting functions, and presto; hierarchical data served up in an ASP.NET DataGrid control. 

Getting The Data
The first thing we are going to need to get this job done is some hierarchical data. For the purposes of this sample I chose to use the Northwind DB that comes with SQL Server as it is something that most developers have access to (no pun intended). Rather than just a simple parent child relationship I thought we would really push the envelope and try three levels. The tables we are going to work with are the Customers, Orders, and Order Details tables. As usual when accessing data we start by creating a connection object. Seeing as we are connecting to a SQL Server database we can use the SQLClient Namespace and its objects to gain a little performance boost. I have declared a module level variable to contain my Connection object and then call the Open method in the Page's Load Handler.

Private Sub PageLoad(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load    If Not Page.IsPostBack Then       If moConn.State <> ConnectionState.Open Then           moConn.ConnectionString = (AppSettings("connectionString"))          moConn.Open()       End If       LoadResults()    End If End Sub

The only piece of this code worth remarking on is the line where we set moConn.ConnectionString. All we are doing here is storing the connection string in the Web.Config file and accessing it through the System.Configuration.ConfigurationSettings.AppSettings object. I find this a handy trick so that the assembly doesn't need to be recompiled when the project is redeployed on another machine and the connection string changes. Once we have an open connection we call the LoadResults Sub and the real work begins.

Private Sub LoadResults;    Dim oRow As DataRow    Dim oTable As DataTable = New DataTable("RESULTS")    Dim sSQL As String    Dim dOrderTotal As Decimal    Dim oDA As SqlClient.SqlDataAdapter    Dim oCustomerRow As DataRow    Dim oOrderRow As DataRow    Dim oDetailRow As DataRow    Dim oDS As New DataSet()    'set up columns in the results table    oTable.Columns.Add("CompanyName", GetType(System.String))    oTable.Columns.Add("OrderDate", GetType(System.DateTime))    oTable.Columns.Add("ProductName", GetType(System.String))    oTable.Columns.Add("Quantity", GetType(System.Int32))    oTable.Columns.Add("UnitPrice", GetType(System.Decimal))    oTable.Columns.Add("Total", GetType(System.Decimal))    'send a batch of selects as the data adapters SelectCommand    sSQL = "SELECT * FROM CUSTOMERS WHERE CompanyName < 'C' & _ "    "SELECT * FROM ORDERS WHERE CustomerID < 'C'" & _    "SELECT [ORDER DETAILS].OrderId, [ORDER DETAILS].Quantity, [ORDER DETAILS].UnitPrice, " & _    "ProductName FROM [ORDER DETAILS], PRODUCTS WHERE [ORDER DETAILS].ProductId = PRODUCTS.ProductId"    oDA = New SqlClient.SqlDataAdapter(sSQL, moConn)    'map the tables that are returned from the DB to the ones we will create in the dataset    oDA.TableMappings.Add("Customers", "Customers")    oDA.TableMappings.Add("Customers1", "Orders")    oDA.TableMappings.Add("Customers2", "Details")    oDA.Fill(oDS, "Customers")    'set up the relationships    oDS.Relations.Add("Customer_Order", oDS.Tables("Customers").Columns("CustomerID"), oDS.Tables("Orders").Columns("CustomerID"))    oDS.Relations.Add("Order_Detail", oDS.Tables("Orders").Columns("OrderId"), oDS.Tables("Details").Columns("OrderId"), False)    'loop through the tables getting child rows as necessary    For Each oCustomerRow In oDS.Tables("Customers").Rows       dOrderTotal = 0       oRow = oTable.NewRow()       oRow("CompanyName") = oCustomerRow("CompanyName")       oTable.Rows.Add(oRow)       For Each oOrderRow In oCustomerRow.GetChildRows("Customer_Order")          oRow = oTable.NewRow()          oRow("OrderDate") = oOrderRow("OrderDate") oTable.Rows.Add(oRow)          For Each oDetailRow In oOrderRow.GetChildRows("Order_Detail")             oRow = oTable.NewRow()             oRow("ProductName") = oDetailRow("ProductName")             oRow("Quantity") = oDetailRow("Quantity")             oRow("UnitPrice") = oDetailRow("UnitPrice")             'calculate on the fly             oRow("Total") = (oDetailRow("UnitPrice") * oDetailRow("Quantity"))             oTable.Rows.Add(oRow)             'add to running total             dOrderTotal += oRow("Total")          Next       Next       oRow = oTable.NewRow()       oRow("ProductName") = "Customer Total"       oRow("Total") = dOrderTotal       oTable.Rows.Add(oRow)    Next    dgValues.DataSource = oTable dgValues.DataBind()    SetHierarchical(False ) End Sub

Let's walk through this Sub. At the top we declare a number of variables including a DataTable to hold our specially formatted results as well as a DataSet and DataAdapter for pulling the hierarchical data out of the DB. We then set up the columns that we want to include in our "RESULTS" DataTable that we will eventually bind to the DataGrid. Once this is done we can begin the process of pulling the grandparent, parent, and child records from the database. This is done by assigning a batch of SQL select commands as the SelectCommand property of the DataAdapter. We send one SQL select for each table that we want created in the DataSet. In this case we will issue three, one for each level in our hierarchy. Note that the third SQL select is selecting from two different tables and includes a join between them. This is to include some data from a fourth table that is what we want to include as part of the Order Detail information. Because this data is in a table that has a one to one relationship with the Order Details table we can just lump it together with the other order details information in the DataSet. We then add three TableMappings to the DataAdapter to assign names to the DataTables that will be created in our DataSet. We will call these tables Customers, Orders and Details to represent the three levels in our hierarchy. Once we call the Fill method of the DataAdapter our three tables in the DataSet will be filled with the data that we selected with our three SQL statements. Another minor point is that I have added WHERE clauses in the SQL to limit the amount of data we return from the DB.

Now we have data in our DataSet but how does it know about which tables are parents and which are children? Just like we do in building a relational database we need to create relationships between the table objects. In ADO.NET we do this using the System.Data.DataRelation object. In the next two line of code we add two DataRelations to the DataSets relations collection. The first one is called Customer_Order and makes the CustomerID of the Customers table the parent column while the CustomerID associated with the Orders table is the child column. The second relationship is similar except that it joins the Orders and Details tables in the dataset using their respective OrderID columns. The one other difference is that I have set the CreateConstraints flag to false in the second instance. By default a DataRelation is created with constraints meaning that if a there are keys in the child table that don't exist in the parent table, an exception is thrown. In our case we didn't limit the selection of rows in the Details table but we did in the Orders table, so to avoid the exception we need to set the the CreateConstraints flag to False.

We are now ready to start reading the data out of the DataSet and into our RESULTS table. To do this we use a set of nested loops which iterate through the rows in each table in the DataSet. We create a new row in the RESULTS for each row in the Customers Table and then enter the Orders loop. The interesting thing about this loop is how we get the child rows that pertain to the current Customer row. The method of the DataRow that does this is called, oddly enough, GetChildRows. We pass it the name of the DataRelation that joins these two tables and the DataSet is smart enough to know which rows in the order table belong to the current customer. The same thing happens when we need to loop through the detail rows for each order but this time we pass the Order_Detail DataRelation to GetChildRows. At the end of the Customer loop we add another row that shows a total of dollars spent by that customer that we have been storing in the running total variable dCustomerTotal. You'll notice that for each row in all of the three tables in our DataSet we add another row to our RESULTS table. The difference from one row to the next however is which columns we populate. These are the clues that we will use when we apply our formatting functions to the DataGrid which will give it the functionality that we are looking for.

Format The DataGrid
At the end of the LoadResults Sub we call SetHierarchical, passing it a value of False. This is the Sub where we apply the formatting that will give our DataGrid the look an feel of that lusted after WinForms one. Let's look at the code.

Private Sub SetHierarchical(ByVal bExpanded As Boolean)    Dim iCount As Int32    For iCount = 0 To dgValues.Items.Count - 1       'set the bg colour of the Customer and Order rows and the plus minus cells       If dgValues.Items(iCount).Cells(1).Text <> " " Then          dgValues.Items(iCount).BackColor = System.Drawing.Color.Wheat          dgValues.Items(iCount).Cells(0).BackColor = System.Drawing.Color.Tan       End If       If dgValues.Items(iCount).Cells(3).Text <> " " Then          dgValues.Items(iCount).BackColor = System.Drawing.Color.AntiqueWhite          dgValues.Items(iCount).Cells(2).BackColor = System.Drawing.Color.Tan       End If       'set the bg colour of the total rows        If dgValues.Items(iCount).Cells(4).Text = "Customer Total" Then  _        dgValues.Items(iCount).BackColor = System.Drawing.Color.AntiqueWhite        If bExpanded Then           'hide + on Test all rows where there is not an expandable node           If dgValues.Items(iCount).Cells(3).Text = " " Then              dgValues.Items(iCount).Cells(2).Controls(0).Visible = False           Else              'set the minus sign              CType(dgValues.Items(iCount).Cells(2).Controls(0), LinkButton).Text = "-"              CType(dgValues.Items(iCount).Cells(2).Controls(0), LinkButton).CssClass = "PlusMinus"           End If           'hide + on all Collection rows where there is not an expandable node           If dgValues.Items(iCount).Cells(1).Text = " " Then              dgValues.Items(iCount).Cells(0).Controls(0).Visible = False           Else              'set the minus sign                CType(dgValues.Items(iCount).Cells(0).Controls(0), LinkButton).Text = "-"           End If         Else           'hide + on all Order rows where there is not an expandable node           If dgValues.Items(iCount).Cells(3).Text = " " Then              dgValues.Items(iCount).Cells(2).Controls(0).Visible = False           Else              'set the plus sign              CType(dgValues.Items(iCount).Cells(2).Controls(0), LinkButton).Text = "+"              CType(dgValues.Items(iCount).Cells(2).Controls(0), LinkButton).CssClass = "PlusMinus"           End If           'hide + on all Customer rows where there is not an expandable node           If dgValues.Items(iCount).Cells(1).Text = " " Then              dgValues.Items(iCount).Cells(0).Controls(0).Visible = False           Else              'set the plus sign              CType(dgValues.Items(iCount).Cells(0).Controls(0), LinkButton).Text = "+"              CType(dgValues.Items(iCount).Cells(0).Controls(0), LinkButton).CssClass = "PlusMinus"           End If          'hide all child nodes and rows of the root nodes          If dgValues.Items(iCount).Cells(1).Text = " " Then dgValues.Items(iCount).Visible = < FONT color = #0000ff > False        End If    Next End Sub

Just because you have called the DataBind method of the DataGrid object doesn't mean that you have to live with the results format-wise. After calling DataBind you can access all the rows of data and change them one by one. It does add a little performance penalty of course but that's a small price to pay for some cool additional functionality. Another trick we have used in this DataGrid is that not all of the columns are databound. Two additional columns have been added to display the plus and minus signs that the user will click to expand and collapse the nodes on our grid. In the SetHierarchical sub we loop through all the rows in the DataGrid and format them based on the clues we added in the rows in RESULTS table. For example, we test to see that there is data in the second cell of each row. If there is then we know that it is a customer row (remember we left the CompanyName column blank in all others when filling the results table). Similarly if the fourth row is filled then it is an Order row. The bExpanded variable is boolean that tells us whether to initialize the grid with all nodes expanded or collapsed. We have passed in False so we will hit the Else part of this If statement. In this section of the code we hide the plus signs on all non expandable rows and most importantly we hide the rows themselves for all rows but those at the top or Customer level. Remember when we set the Visible property of a DataGridRow to False it will not be rendered. This way when the grid is displayed we are only rendering the rows that the user will see while the ViewState will continue to hold all the rows from our RESULTS table that we bound to for later use.

Expand And Collapse
At this point running the code should display the grid in the collapsed position. All that remains is to add the code behind the events that are triggered when the user clicks the plus or minus signs. On closer inspection of the two non bound rows that were added to the grid we discover that they are actually LinkButton columns with the Command name changed to a custom value. The DataGrid object was built with extensibility in mind so it allows you to change the Command name of any button column. Now when the user clicks the button instead of firing one of the prewired command events like the CancelCommand, it fires the more generic ItemCommand. This then is the event handler where we will put our expand and collapse code.

Private Sub dgValuesItemCommand(ByVal source As Object, _                                                      ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs)                                                      Handles dgValues.ItemCommand    Dim iCount As Int32 = e.Item.ItemIndex + 1    If e.CommandName = "ExpandOrder" Then       If CType(e.Item.Cells(2).Controls(0), LinkButton).Text = "-" Then          'Hide all child rows in the node careful not to hide the Customer row          Do While dgValues.Items(iCount).Cells(3).Text = " " And dgValues.Items(iCount).Cells(1).Text = " "             dgValues.Items(iCount).Visible = False             iCount += 1             If iCount > = dgValues.Items.Count Then Exit Do          Loop          'change the minus to a plus          CType(e.Item.Cells(2).Controls(0), LinkButton).Text = "+"       Else          'Show all child rows in the node          Do While dgValues.Items(iCount).Cells(3).Text = " "             dgValues.Items(iCount).Visible = True             iCount += 1             If iCount > = dgValues.Items.Count Then Exit Do          Loop          'change the plus to a minus          CType(e.Item.Cells(2).Controls(0), LinkButton).Text = "-"       End If    ElseIf e.CommandName = "ExpandCustomer" Then       If CType(e.Item.Cells(0).Controls(0), LinkButton).Text = "-" Then          'Hide all child rows in the node          Do While dgValues.Items(iCount).Cells(1).Text = " "             dgValues.Items(iCount).Visible = False             'if this is an order row set the plus sign as we are collapsing it too             If dgValues.Items(iCount).Cells(3).Text <> " " Then                CType(dgValues.Items(iCount).Cells(2).Controls(0), LinkButton).Text = "+"             End If             iCount += 1             If iCount > = dgValues.Items.Count Then Exit Do          Loop          'hide the total row          dgValues.Items(iCount - 1).Visible = False          'change the minus to a plus          CType(e.Item.Cells(0).Controls(0), LinkButton).Text = "+"       Else          'Show all Order rows in the node          Do While dgValues.Items(iCount).Cells(1).Text = " "             If dgValues.Items(iCount).Cells(3).Text <> " " Then dgValues.Items(iCount).Visible = True             iCount += 1             If iCount > = dgValues.Items.Count Then Exit Do          Loop          'show the total row          dgValues.Items(iCount - 1).Visible = True          'change the plus to a minus          CType(e.Item.Cells(0).Controls(0), LinkButton).Text = "-"        End If    End If End Sub

As you can see, we test e.CommandName for the string that we set in fig 2 above. For the Customer rows it is ExpandCustomer and for the Order rows it is ExpandOrder. Basically we do the same thing in both sections. If the node has a plus sign then we expand it otherwise, we collapse. Again we use the clues that we left in our data to decipher what type of row it is and take action based on that. There are a few tricky things that need to be done like checking for the last row in the grid and dealing with the total rows but most of this code is pretty simple plumbing.

Sky's The Limit...
Hopefully this gives you a good idea about how to interact with hierarchical data in the ASP.NET DataGrid. Don't stop here though. If you can use something like this then download the code and start customizing! If you think you might reuse an object like this then try creating a server control that inherits DataGrid and build the plumbing code right in. Adding the ability to edit in line would be another great feature. One of the great things about .NET is that there are many ways to approach any problem. Experiment, enjoy, and remember when working with .NET, the sky's the limit!