The Master Detail Code

Now that we are finished with the front-end of our page let's start on the codebehind(back-end).
The following are things we need to create in the codebehind

• An event handler for Page_Init event.
• An event handler for Page_Load event.
• An event handler for the 'Categories' repeater's ItemDataBound event.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace MasterDetail
{
    /// <summary>
    /// Summary description for MasterDetail.
    /// </summary>
    public class MasterDetail : Page
    {
        public Repeater Categories;
        public Repeater Products;
        private void Page_Init(object sender, EventArgs e)
        {
            Categories.ItemDataBound += new RepeaterItemEventHandler(Categories_OnItemDataBound);
        }

Nothing magic here, just setting up the base and defining the Page_Init event handler. We don't have to subscribe to the ItemDataBound event in the codebehind, we could have done it in the .aspx file by adding the event handler to the 'Categories' repeater's OnItemDataBound property.

The next thing on the agenda now is to add an event handler for Page_Init event. This method will contain the meat of the page, as that is where we fill our dataset, databind our 'Categories' repeater control and add a relationship between our two tables:

private void Page_Load(object sender, EventArgs e)
{
    SqlConnection connection = new SqlConnection("Server=localhost; User Id=sa; Password=; Initial Catalog=Northwind");
    SqlDataAdapter da = new SqlDataAdapter();
    DataSet ds = new DataSet();           
    // Fill dataset
    da.SelectCommand = new SqlCommand("SELECT CategoryID, CategoryName FROM Categories", connection);
    da.Fill(ds, "Categories");
    da.SelectCommand = new SqlCommand("SELECT CategoryID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock FROM Products", connection);
    da.Fill(ds, "Products");
    // We need to define a relationship between the two tables in the dataset
    DataRelation relation = new DataRelation("categoryId", ds.Tables["Categories"].Columns["CategoryID"], ds.Tables["Products"].Columns["CategoryID"]);
    ds.Relations.Add(relation);
    Categories.DataSource = ds.Tables["Categories"].DefaultView;
    Categories.DataBind();
}

Briefly all we do here is fill up our dataset with the two tables, and add a relationship between the two tables. The way you define a relationship between two tables is by creating an instance of the DataRelation class, and then you add it to the dataset's Relations collection by using the collection's Add method. Instead of doing like I did above you could have done the following instead:

ds.Relations.Add( new DataRelation("categoryId", ds.Tables["Categories"].Columns["CategoryID"], ds.Tables["Products"].Columns["CategoryID"]) );

Although I prefer the first version as it looks nicer ;) All that is left now is to add the event handler for Categories_ItemDataBound event:

        private void Categories_OnItemDataBound(object sender, RepeaterItemEventArgs e)
        {
            if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
            {
                ((Repeater) e.Item.FindControl("Products")).DataSource = ((DataRowView) e.Item.DataItem).CreateChildView("categoryId");
                ((Repeater) e.Item.FindControl("Products")).DataBind();
            }
        }
    }
}

In the method we check to see if the item that is databound is actually either an Item or an AlternatingItem . We have to do this because the objects we want to toy around with are only contained inside the ItemTemplate (in this case we could actually remove the AlternatingItem check, but I added it to make a point). Through the usage of the FindControl method and type casting we can dynamically alter the 'Products' repeater control's datasource for each category iteration.

Well that's it, compile it and have fun!