Tyrone Hopes - how to do nested repeaters

web work

I work as a web programmer in Henley. I write here about some useful web programming techniques using ASP.NET.

How to build Nested Releaters in VB.NET

The humble repeater has been around for some time; it's primitive and involves late databinding but it is extremely versatile. The great advantage of using repeaters, and other similar dinosaurs is that they allow html to be added easily. The alternative - if we were doing all the work in the code - is to embed markup within the code itself. This would cause a bit of grief if simple markup changes were required.

But are repeaters capable of dealing with relational data? Yes, but it's not easy to find the correct means by which this is possible. Here's one way of doing it, keeping all the databinding in the code-behind page (where I consider it belongs), and using the .aspx page for markup:

The data is from the Northwind Database, free with Microsoft SQL Server Express.

There are two tables in this database which have a close hierarchical relationship, and are perfect to show in this demo - they are Orders and Order Details. What we are going to do is show the Order Details that belong to each Order.

Here is the html markup in the page:

 
<asp:Repeater ID="rptOrders" runat="server">
    <HeaderTemplate>
        <table>
            <tr><th>OrderID</th><th>CustomerID</th><th>Total Value (Freight)</th></tr>
    </HeaderTemplate>
        <ItemTemplate>
            <tr>
                <td><%#DataBinder.Eval(Container.DataItem, "OrderID")%></td>
                
                <td><%#DataBinder.Eval(Container.DataItem, "CustomerID")%></td>
                
                <td><%#DataBinder.Eval(Container.DataItem, "Freight", "{0:c}")%></td>
            </tr>
            <!-- now the nested repeater showing order details -->
            <asp:Repeater ID="rptOrderDetails" runat="server">
                <HeaderTemplate>
                    <tr><td colspan="99"><table>
                </HeaderTemplate>
                <ItemTemplate>
                    <tr><td>ProductID:</td><td><%#DataBinder.Eval(Container.DataItem, "ProductID") %></td></tr>
                </ItemTemplate>
                <FooterTemplate>
                    </table></td></tr>
                </FooterTemplate>
            </asp:Repeater>
        </ItemTemplate>
    <FooterTemplate>
        </table>
    </FooterTemplate>
</asp:Repeater>

A couple of notes about the above: notice that the "freight" data item utilises the formatting option. In this case freight is a numeric which we want to convert to currency. Secondly, the HeaderTemplate, ItemTemplate and FooterTemplate of the repeaters are all used to build tables in the html that is produced. Although frowned upon by XHTML extremists it is okay to still use them for data formatting.

All other work is done in the code-behind. The following shows what is required:

Imports required

Imports System.Data
Imports System.Data.SqlClient

assign some variables first

Dim con As SqlConnection
Dim dsOrders As DataSet
Dim daOrders As SqlDataAdapter
Dim daOrderDetails As SqlDataAdapter
Dim OrderRelation As DataRelation

in page_load fill up the dataset

If Not (IsPostBack) Then
    'extract the tables from the database
    con = New SqlConnection()
    con.ConnectionString = "Data Source=(local); Initial Catalog=Northwind; Integrated Security=True;"
    dsOrders = New DataSet
    '
    daOrders = New SqlDataAdapter
    daOrders.SelectCommand = con.CreateCommand()
    daOrders.SelectCommand.CommandType = CommandType.Text
    daOrders.SelectCommand.CommandText = "SELECT * FROM Orders ORDER BY OrderID"
    daOrders.Fill(dsOrders, "Orders")
    '
    daOrderDetails = New SqlDataAdapter
    daOrderDetails.SelectCommand = con.CreateCommand()
    daOrderDetails.SelectCommand.CommandType = CommandType.Text
    daOrderDetails.SelectCommand.CommandText = "SELECT * FROM [Order Details] ORDER BY OrderID"
    daOrderDetails.Fill(dsOrders, "Order_Details")
    '
    'we now have the two tables.
    'create a relationship between the two - the common column is orderID
    '
    OrderRelation = New DataRelation("OrderID_Releation", _
      dsOrders.Tables("Orders").Columns("OrderID"), _
      dsOrders.Tables("Order_Details").Columns("OrderID"))
    OrderRelation.Nested = True
    '
    'bind the data to the parent repeater
    '
    Me.rptOrders.DataSource = dsOrders.Tables("Orders")
    Me.rptOrders.DataBind()
    '
End If

and now the secret weapon: the ItemDataBound event to build the data for the nested repeater:

Protected Sub rptOrders_ItemDataBound(ByVal sender As Object, _
  ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles rptOrders.ItemDataBound
    Dim dv As DataRowView = TryCast(e.Item.DataItem, DataRowView)
    If dv IsNot Nothing Then
        Dim rptOrderDetails As Repeater = TryCast(e.Item.FindControl("rptOrderDetails"), Repeater)
        If rptOrderDetails IsNot Nothing Then
            rptOrderDetails.DataSource = dv.CreateChildView(OrderRelation)
            rptOrderDetails.DataBind()
        End If
    End If
End Sub

notes

It is important to note the following:

the inner nested repeater is not databound in page load. It is 'late-bound' using the ItemDataBound event.
the nesting will not work without reference made in the ItemDataBound event to the relationship set up in the page load area.

so how does it work?

All the data objects are set up in page load, so that the dataset has two tables, Orders, and OrderDetails (I lose the space that is used in the database). There is a relationship between the two. The outer repeater is bound once-only in page load.

The rendering to html starts. There's a trigger when the databinding to the outer nesting occurs. This runs the ItemDataBound sub, which casts the dataitem to a datarowview, which is a list of data items. A reference is obtained in the code to the nested repeater - which allows the subselection of data to occur in the crucial line, rptOrderDetails.DataSource = dv.CreateChildView(OrderRelation).

how about multiple nestings?

Yep, this is possible. It's not a case of simply adding a new ItemDataBound subroutine for the 'n'th layer. Look at the nested repeater control: it is only referenced from within the ItemDataBound event via a 'findcontrol' method. This means we haven't got a direct programmatic reference to it outside the ItemDataBound sub. This means we need to reference a new ItemDataBound event via an additional 'AddHandler' line.

In order to try this out I have modified the routine referred to above by adding a new outer repeater called "rptCustomer". This represents the outer Customer table, so we have the hierarchy:

Customer (key CustomerID)
Orders (key OrderID, foreign key CustomerID)
Order Details (key OrderID, ProductID - OrderID is also a foreign key)

So it is simply a case of creating a new data adapter to include the Customers table, adding a relationship, adding the outer repeater, and changing the binding to this instead. Then in the Orders ItemDataBound event, we modify it by adding the following:

Protected Sub rptCustomers_ItemDataBound(ByVal sender As Object, _
  ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles rptCustomers.ItemDataBound
    Dim dv As DataRowView = TryCast(e.Item.DataItem, DataRowView)
    If dv IsNot Nothing Then
        Dim rptOrders As Repeater = TryCast(e.Item.FindControl("rptOrders"), Repeater)
        If rptOrders IsNot Nothing Then
            'add an event
            AddHandler rptOrders.ItemDataBound, AddressOf rptOrders_ItemDataBound
            rptOrders.DataSource = dv.CreateChildView(CustomerRelation)
            rptOrders.DataBind()
        End If
    End If
End Sub

And then we add the new event for the handler mentioned above:

Protected Sub rptOrders_ItemDataBound(ByVal sender As Object, _
   ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs)
    Dim dv As DataRowView = TryCast(e.Item.DataItem, DataRowView)
    If dv IsNot Nothing Then
        Dim rptOrderDetails As Repeater = TryCast(e.Item.FindControl("rptOrderDetails"), Repeater)
        If rptOrderDetails IsNot Nothing Then
            rptOrderDetails.DataSource = dv.CreateChildView(OrderRelation)
            rptOrderDetails.DataBind()
        End If
    End If
End Sub

Don't forget to build a new parent-child relationship in the dataset code. This can be like the following:

CustomerRelation = New DataRelation("CustomerID_Relation", _
     dsOrders.Tables("Customers").Columns("CustomerID"), _
     dsOrders.Tables("Orders").Columns("CustomerID"))
CustomerRelation.Nested = True

Some final notes on the above:

there is no 'handles' clause in the outer ItemDataBound sub: the wiring between the event and its code is taken care of by the 'AddHandler' line
the handler is the key to making this a multi-nested routine. If more levels of nesting are required, just add more handlers for the next nesting level. If two repeaters are dealt with at the same level, then you would have two AddHandler lines in the ItemDataBound event handler, both referring to further ItemDataBound event handlers.