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
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!
