This week I gave a quick one hour overview of ADO.Net to the developers I am currently mentoring. When I covered relationships within datasets, basically a primary / foreign key constraint I was asked where that would be useful. Aside from enforcing data integrity on inserts and deletes you can use relationships to make nesting asp.net repeaters easy.

If you look at my spam pages you can see there is an obvious data hierarchy, each month has a collection of days which show a daily spam count. Rather than send a request to the SQL server to give me the data on months then as I go through each month send another request to the SQL server to give me the days I decided get everything back in one query;

select * from SpammerStatisticsByYearMonth
select * from SpammerStatisticsByYearMonthDay

This stored procedure returns two datasets which have the common column yyyyMM which can be used to form a relationship. So I pull it back using the Microsoft Data Access block then set the relationship between the columns;

DataSet spamStatistics =
  SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings["database.spam"].ToString(),
  "getSpammerStatistics");

spamStatistics.Relations.Add(
  new DataRelation("yearMonth",spamStatistics.Tables[0].Columns["yyyyMM"],
  spamStatistics.Tables[1].Columns["yyyyMM"]));

Now I needed to setup the repeaters.

<asp:repeater id="rptMonthSummary" runat="server">
  <itemtemplate>
    <h1>
      <%# DataBinder.Eval(Container.DataItem, "receivedYear")%>/
      <%# DataBinder.Eval(Container.DataItem, "receivedMonth")>%
    </h1>
    <p><%DataBinder.Eval(Container.DataItem, "spamCount")>% rejected mails</p>
    <!-- daily breakdown will go here -->
  </itemtemplate>
</asp:repeater>

This repeater builds the outer frame; the data bound control that shows the year and month along with the number of spams in that month. A simple data bind in the Page_Load event and away it goes (with my actual code caching the dataset and binding to that if it exists, to save round trips to the server);

rptMonthSummary.DataSource = spamStatistics
rptMonthSummary.DataBind()

But this doesn't help display the actual days, it just produces output like

2004 / 10

4564 rejected mails

2004 / 9

3769 rejected mails

So we need to add a new dataset to our repeater code (replacing that nice comment that hinted this was going to happen);

<asp:repeater id="rptMonthSummary" runat="server">
  <itemtemplate>
    <h1>
      <%# DataBinder.Eval(Container.DataItem, "receivedYear")%>
      <%# DataBinder.Eval(Container.DataItem, "receivedMonth")>%
    </h1>
    <p><%DataBinder.Eval(Container.DataItem, "spamCount")>% rejected mails</p>
    <!-- Daily Breakdown Repeater -->
    <p>
      <asp:repeater id="rptDailySummary" runat="server">
        <itemtemplate>
          <a href ='dailyBreakDown.aspx?date=<%#DataBinder.Eval(Container.DataItem, "yyyyMMDD") %>'>
            <%# DataBinder.Eval(Container.DataItem, "receivedDay")%>
          </a>
        </itemtemplate>
      </asp:repeater>
    </p>
  </itemtemplate>
</asp:repeater>

However running this does not produce any daily results, we have to hook into the repeater events. As data bound controls bind each row they fire an OnItemDataBound events.

binding to the item data bound event Binding to the event is easy in Visual Studio.Net, by bringing up the properties for the outer repeater click on the lightning icon to get to the events and enter a name for the method the event will bind to. You can also setup the binding in your code behind file by adding the handler in code

this.RepeaterName.ItemDataBound += new System.Web.UI.WebControls.RepeaterItemEventHandler(this.RepeaterName_ItemDataBound);

Or you can use the set the binding event using an attribute in your repeater declaration on the aspx page,

<asp:repeater id="rptMonthSummary" runat="server" OnItemDataBound="RepeaterName_ItemDataBound">

If you use this method your method must be public, not private, otherwise the derived class that ASP.NET makes from your page, can't invoke that method.

So now as each row from the dataset is bound to the repeater control two events are fired, ItemCreated and the one we've bound to, ItemDataBound. This is where the fun begins. ItemDataBound fires for every defined template in a repeater, including header and footer templates, so we need to filter these binding events when we react to each data binding. So I build the event handler like so

private void rptMonthSummary_ItemDataBound(object sender, System.Web.UI.WebControls.RepeaterItemEventArgs e)
{
  ListItemType lt = e.Item.ItemType;
  if(lt == ListItemType.Item || lt == ListItemType.AlternatingItem)
  {
    DataRowView dv = e.Item.DataItem as DataRowView;
    if(dv != null)
    {
      Repeater nestedRepeater = e.Item.FindControl("rptDailySummary") as Repeater;
      if (nestedRepeater != null)
      {
        nestedRepeater.DataSource = dv.CreateChildView("yearMonth");
        nestedRepeater.DataBind();
      }
    }
  }
}

So as each event is fired our method gets called. We get the ListItemType from the event and check if the ListItem is a normal item or an alternate item so we can filter out binding to header and footer templates. Then we extract the data being bound to the row, checking it is not null. Note that the item is not a DataRow, but is return as a DataRowView, a view on the DataRow exposed as a Windows Control. So once we have the data view of the row being bound we look for the nested repeater by name, using e.Item.FindControl("controlName"). To get the related data we create a view based on the relationship we defined after we loaded the dataset. I then set that that view as the source for the nested repeater

nestedRepeater.DataSource = dv.CreateChildView("yearMonth");
nestedRepeater.DataBind();

and as if by magic all the related data from the second table binds to the repeater and we have our list of days.