Displaying One-To-Many Relationships with Nested Repeaters

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">
  <ItemTemplate>
  <p><strong><%# Eval("Author") %></strong><br />
  <asp:Repeater ID="ChildRep" runat="server">
    <ItemTemplate>
    <div>
      <%# Eval("Title") %><br />
    </div>
    </ItemTemplate>
  </asp:Repeater>
  </p>
  </ItemTemplate>
</asp:Repeater>    

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.

 
[C#]       
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();
  ad.Fill(ds);
[VB]
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()
  ad.Fill(ds)

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:

[C#]
  ds.Relations.Add(new DataRelation("AuthorID", ds.Tables[0].Columns["au_id"],
      ds.Tables[1].Columns["au_id"]));
      
[VB]
  ds.Relations.Add(New DataRelation("AuthorID", ds.Tables(0).Columns("au_id"), 
      ds.Tables(1).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:

[C#]
  MasterRep.DataSource = ds.Tables[0];
  MasterRep.DataBind();
}
[VB]
  MasterRep.DataSource = ds.Tables(0)
  MasterRep.DataBind()
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:

[C#]
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");
    ChildRep.DataBind();
  }
}
[VB]
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")
    ChildRep.DataBind()
  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: 48489

4 Comments

- Brightsy

Hi,
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
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and I end up deleting quite a lot. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Requests to fix your code (post a question to forums.asp.net instead, please)
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Recent Comments

KrASh 5/2/2015 5:19 AM
In response to Accessing Your Model's Data from a Controller
Hello, I'm following the tutorial but getting a problem here. I built the application like you said...

Afzaal Ahmad Zeeshan 5/1/2015 7:50 PM
In response to Object reference not set to an instance of an object and INamingContainer
Hello Mike, Get article to share views over how to ignore null exception in Web there is a typo...

Anh Huynh 4/27/2015 3:51 PM
In response to Create PDFs in ASP.NET - getting started with iTextSharp
I can't get a WRITE access to the online server so I change the method from FileStream to and it for...

Rogerio 4/24/2015 11:16 PM
In response to Adding Search
Hi, great post. Congratulations. @mb, you could make your parameters optional and just at of your...

chandradev 4/24/2015 11:53 AM
In response to 7 C# 6.0 Features That Every ASP.NET Developer Should Know About
very nice article. Thanks for sharing with us....

Lokesh 4/24/2015 9:06 AM
In response to 7 C# 6.0 Features That Every ASP.NET Developer Should Know About
Excellent Article..!! Thanks Mike :)...

kawish 4/24/2015 7:04 AM
In response to Using The Themes Helper In Razor Web Pages
Nice:)...

Mahdi 4/23/2015 9:49 PM
In response to Adding a New Row In The Razor WebGrid
Thank u very very. its useful for me...

Jerrie Pelser 4/23/2015 4:24 PM
In response to Entity Framework 6 Recipe: Storing And Managing Time
Nice article Mike! I think there is an error in the House property getter of MovieFormModel. Your...

mike 4/22/2015 4:45 PM
In response to Parameter Queries in ASP.NET with MS Access
If the insert statement got failed how w can i get the error message of as string?...