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!

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


- 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

Phil 29/11/2015 18:48
In response to iTextSharp - Working with images
Hi Mike, great article as ever. Any chance you can do an example of adding a swf to a pdf with I it...

makhlas 27/11/2015 17:05
In response to How To Send Email In ASP.NET MVC
hello, thanks for sharing these tutos , but when i apply the send button, i'm gettin this error...

raphael 27/11/2015 13:59
In response to ASP.NET 5: Managing Client-side Dependencies with NPM, Bower and Gulp
i fixed it. i'm not sure *what* it is that VS does, but you get the same result if you do a "npm on...

Raphael 27/11/2015 13:02
In response to ASP.NET 5: Managing Client-side Dependencies with NPM, Bower and Gulp
There seems to be a major issue with the dependency manager in VS2015. I haven't yet figured out but...

Joaquín Bresan 26/11/2015 17:58
In response to Getting the identity of the most recently added record
Great contribution friend. Thank you so much for sharing....

JR 26/11/2015 02:49
In response to ASP.NET MVC 5 with EF 6 - Working With Files
Hello, Where is the location of the database? Thanks...

sumalatha 24/11/2015 22:09
In response to ASP.NET 5 Middleware, Or Where Has My HttpModule Gone?
Great article. I have gone through hundreds of articles, finally this gives the comprehensive of...

TimPat10 24/11/2015 17:35
In response to A Better Way To Export Gridviews To Excel
Great article. I've noticed that date fields have to be re-formatted when the spreadsheet is pulled...

Rajasekar 24/11/2015 12:27
In response to Import Data From Excel to Access with ASP.NET
While use this code i'm facing on error: "Unrecognized Database format C:\mydabase.accdb" can any...

Parmod 24/11/2015 07:28
In response to ASP.NET 5 Project Basics
For a new learner (Fresher) in ASP.NET there is a issue Fresher have to learn two types of , old...