Displaying One-To-Many Relationships with Nested Repeaters

4.28 (29 votes)

This item could also have been entitled "Displaying Master-Detail Data" or "Displaying Categorised Data", but the principal is the same: you have what are essentially headings or categories, and a group of entries that belong to each heading that you want to display. Typical examples would include the Models of cars by Manufacturer, or Employees by Department. Here's how to do it quite simply using nested Repeaters.

An ideal example to illustrate this is to retrieve titles by authors from the Pubs database. What we want to achieve is to have each author displayed once, with all their titles grouped by author.

And here is the aspx content that produces it:

<asp:Repeater ID="MasterRep" runat="server" OnItemDataBound="MasterRep_ItemDataBound">
  <p><strong><%# Eval("Author") %></strong><br />
  <asp:Repeater ID="ChildRep" runat="server">
      <%# Eval("Title") %><br />

Simply, there are two Repeaters - MasterRep and ChildRep, which contain a databinding expression each, and the MasterRep also contains an ItemDataBound event.

There are essentially two sets of data here - the Authors and the Titles.  They both need to be extracted separately, and then a relationship formed between the two sets based on the Author's primary key. Fortunately, the ADO.NET DataSet object allows us to define relationships on the fly between the contents of multiple DataTables, but this means that we cannot use a SqlDataSource or AccessDataSource control for extracting the data, as the DataSets returned by these controls only contain the most recently populated set of data. So we need to do a bit of ADO.NET coding. Here are the 2 SELECT statements:

  SELECT au_id, au_lname + ' ' + au_fname AS Author FROM authors 
  (au_id IN (SELECT au_id FROM titleauthor))
  SELECT titleauthor.au_id, titles.title FROM titles 
  INNER JOIN titleauthor ON titles.title_id = titleauthor.title_id

I combine the 2 into one stored procedure, which I call GetTitlesByAuthor. However, with Sql Server, both statements can be run from one Command if they are separated by a semicolon (;) as a batch. MS Access doesn't support batch statements, so each SELECT needs to be run separately against the same DataSet object.

string cn = @"Data Source=.\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI;";
using (SqlConnection conn = new SqlConnection(cn))
  SqlCommand cmd = new SqlCommand("GetTitlesByAuthor", conn);
  cmd.CommandType = CommandType.StoredProcedure;

  SqlDataAdapter ad = new SqlDataAdapter(cmd);
  DataSet ds = new DataSet();
Dim cn As String = "Data Source=.\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI;"
Using conn As New SqlConnection(cn)
  Dim cmd As New SqlCommand("GetTitlesByAuthor", conn)
  cmd.CommandType = CommandType.StoredProcedure

  Dim ad As New SqlDataAdapter(cmd)
  Dim ds As New DataSet()

Now comes the simple task of relating the 2 sets of data that are held in separate tables within the DataSet ds together.  This is achieved by adding a new DataRelation object to the Relations collection of the DataSet:

  ds.Relations.Add(new DataRelation("AuthorID", ds.Tables[0].Columns["au_id"],
  ds.Relations.Add(New DataRelation("AuthorID", ds.Tables(0).Columns("au_id"), 

The above instruction creates a DataRelation called "AuthorID" between the au_id field of the first DataTable and the au_id of the second. So the next step is to bind the MasterRep Repeater to the first table, as this is the one that contains the Authors' names:

  MasterRep.DataSource = ds.Tables[0];
  MasterRep.DataSource = ds.Tables(0)
End Using

All of the above goes into the Page_Load event, ensuring that the page hasn't been posted back (which saves rebinding the data if it has).

Now onto the ItemDataBound event of the MasterRep Repeater, as this is where use is made of the DataRelation that was created earlier:

protected void MasterRep_ItemDataBound(object sender, RepeaterItemEventArgs e)
  if (e.Item.ItemType == ListItemType.Item ||
  e.Item.ItemType == ListItemType.AlternatingItem)
    DataRowView drv = e.Item.DataItem as DataRowView;
    Repeater ChildRep = e.Item.FindControl("ChildRep") as Repeater;
    ChildRep.DataSource = drv.CreateChildView("AuthorID");
Protected Sub MasterRep_ItemDataBound(ByVal sender As Object, ByVal e As RepeaterItemEventArgs)
  If e.Item.ItemType = ListItemType.Item OrElse 
                           wrap e.Item.ItemType = ListItemType.AlternatingItem Then
    Dim drv As DataRowView = TryCast(e.Item.DataItem, DataRowView)
    Dim ChildRep As Repeater = TryCast(e.Item.FindControl("ChildRep"), Repeater)
    ChildRep.DataSource = drv.CreateChildView("AuthorID")
  End If
End Sub

The CreateChildView method of the DataRowView object returns a DataView from the "child" DataTable, as defined in the DataRelation (it's the second table that was passed into the method). Since this is filtered on the current MasterRep row's au_id, it will return all titles that share that au_id. This is then bound to the ChildRep, before the loop moves on to the next row.

And with that, it's done!

You might also like...

Date Posted:
Last Updated:
Posted by:
Total Views to date: 54423


- Brightsy

Iam a newbie, I have already done relating the two queries and displaying the records, now i want to link items in a cloumn, to open related details in new window. how can i do it.

The column to be linked, is an Msg_ID Column, so on clickling on any ID Number, the related record should open in a new window.

I couldn't find how to bind to link in ItemDataBOund method.

Plz help me immediately.

Its Urgent

Thank you.

- Mike

- Sohrab

hi.this was very usefull for me.after spending 6 hours I could find best answer for my question.
thanx alot.

- Black

it's working. thank for the code

Recent Comments

Gfw 03/02/2017 09:48
In response to Free SSL Certificates On IIS With LetsEncrypt
I have used WinSimple for about the last 9 months - works great. One thing that you want to make of...

Ted Driver 02/02/2017 13:24
In response to Free SSL Certificates On IIS With LetsEncrypt
This looks great is you have command line access to your web server - what about those of us on Is...

Carl T. 06/11/2016 05:43
In response to Server.MapPath Equivalent in ASP.NET Core
Very succinct and easy to follow. Worked perfectly the first time for me. Thanks!!...

Manoj Kulkarni 04/11/2016 05:47
In response to Entity Framework Core DbContext Updated
Great post....

Sivu 19/10/2016 08:21
In response to Entity Framework Core TrackGraph For Disconnected Data
Oh that's very very very nice ! Thanks for the write up Mike, much appreciated for the taking the to...

Mark 12/10/2016 16:42
In response to ASP.NET Web Pages vNext or Razor Pages
Although "Web Pages" was removed from the roadmap, has it just been renamed to "Razor Pages"?...

Satyabrata 12/10/2016 09:20
In response to Entity Framework Core TrackGraph For Disconnected Data
Nice article. Please write more articles featuring ASP.Net web pages. Thank you...

Julian 26/09/2016 14:27
In response to Loading ASP.NET Core MVC Views From A Database Or Other Location
Fantastic, many thanks Mike! Had got half way down this road before finding your article - saved...

Abolfazl Roshanzamir 14/09/2016 05:36
In response to Loading ASP.NET Core MVC Views From A Database Or Other Location
Nice article. Thanke you so much ....

cyrus 02/09/2016 15:12
In response to ASP.NET Web Pages vNext or Razor Pages
I've got some news. As Damian stated in this link: https://github.com/aspnet/Mvc/issues/5208 “We...