Multilevel Nested Master/Detail Data Display Using GridView

• Download source - 4.43 Kb

Introduction

This article presents how the GridView control can be nested to show master/detail relationships for multilevel hierarchal data. In this article, I have implemented it for three levels, and it can be implemented for n levels quite easily, by maintaining the information about the edit index of the parent GridView controls.

This article provides a simple, easy, and clean solution for displaying data with multilevel master/detail relationships. This article also shows how the GridView can be used effectively by using its template fields, and how controls inside template fields are accessed and bound. More importantly, you will realize the power of GridView's edit mode, and learn how it can be used to do things other than editing; evading from its default usage!!

Background

I happened to work on an HRM system of a company where I had to display details of employees' log events. This was a three level master detail relationship. At the first level, I had to show all the employees of the company (in a ParentGridView). When clicked at any employee, at the second level, I had to show the days in a month when he/she was present (in a ChildGridView), and on the third level, when clicked on a particular day, the corresponding log events of that day (login or log out) had to be displayed (in a GrandChildGridView). I implemented this multilevel master detail relationship by handling the OnRowEditing event of a GridView just to get the new edit index of the parent GridView and save it in the session for the child GridViews.

Using the code

In this particular demo version, I have used Northwind’s Pubs database. The tables Publishers, Titles, and Roysched have a three level master detail hierarchical relationship: each publisher has published multiple titles, and each title has multiple royalty schedules. There is a parent GridView named ParentGridView showing the Publishers table, a child GridView named ChildGridView to display the corresponding records of the Titles table, and a grand child GridView named GrandChildGridView to show the corresponding royalty schedule of a particular title. All parent GridViews need to be templated to show the child one in edit mode. The SelectCommand for the ParentGridView shows that it will have three template fields, pub_id, pub_name, and city, yet there will be a fourth template field that will be used to show the child GridView.

SelectCommand="SELECT [pub_id], pub_name], [city] FROM [publishers]";

I have removed the formatting code and explained the implementation of all three GridViews separately for the sake of clarity. I will show where a child GridView should be nested by writing its name with a star just to increase readability and understanding.

Parent GridView

The parent GridView is templated for all columns selected in the SelectCommand. As you can see, in the code, there is one more template field named ‘View’ which is the only part where the magic lies! In its IetmTemplate, there is a button with the Text, ‘+’, and CommandName, ‘Edit’. This plus sign button, when clicked, lets the GridView to enter edit mode, and fires the OnRowEditing event which is handled. What is to be shown in the edit mode is implemented in the EditItemTemplate. As shown in the code, it has a button with CommandName 'Cancel' and Text ‘-’ (to collapse the GridView by just running the Cancel command, which resets the edit index to -1 and the ParentGridView comes out of editing mode; obviously, the ChildGridView shown in edit mode will no longer be visible, which gives the notion of collapsibility). Next to the Cancel ‘-' button is a ChildGridView.

<asp:GridView ID="ParentGridView" runat="server"
  DataSourceID="ParentSqlDataSource"
  AutoGenerateColumns="False"
  DataKeyNames="pub_id"
  OnRowEditing= "ParentGridView_OnRowEditing" 
  <Columns>
     <asp:TemplateField HeaderText="PublisherID">
        <ItemTemplate>
           <asp:Label id="pubid_lbl" Runat="Server" 
           Text='<%# Eval("pub_id") %>'/>
        </ItemTemplate>
                
        </asp:TemplateField>
     <asp:TemplateField HeaderText="Name">
        <ItemTemplate>
           <asp:Label id="name_lbl" Runat="Server" 
           Text='<%# Eval("pub_name") %>'/>
        </ItemTemplate>
     </asp:TemplateField>
              
    <asp:TemplateField HeaderText="City">
        <ItemTemplate>
            <asp:Label id="city_lbl" Runat="Server" 
            Text='<%# Eval("city") %>'/>
        </ItemTemplate>
    </asp:TemplateField>
                
    <asp:TemplateField HeaderText="View">
        <ItemTemplate>
            <asp:Button ID="ViewChild_Button" 
                  runat="server" Text="+"   CommandName="Edit" />
        </ItemTemplate>
       <EditItemTemplate>
            <asp:Button ID="CancelChild_Button" 
                  runat="server" Text="-" CommandName="Cancel" />
            *ChildGridView…will come here
     </EditItemTemplate>
   </asp:TemplateField>
 </Columns>
</asp:GridView>

The event handling code for OnRowEditing is quite self-explanatory, yet it's is important to know that the OnRowEditing event does not have any code for editing. Instead, it sets the the ParentGridView’s edit index to that of the current row and binds it. It saves the index and pub_id in the session, because later, we’ll need both of them: pub_id to get the corresponding tuples of Titles against it, and ParentGridViewIndex is needed to find the row containing the ChildGridView whose row has been selected.

protected void ParentGridView_OnRowEditing(object sender, 
                              GridViewEditEventArgs e)
{
    int parent_index = e.NewEditIndex;
    
    //to set the edit index of the Parent 

    //grid with that of the current row

    ParentGridView.EditIndex = parent_index;
    ParentGridView.DataBind();
    //find the pubid_lbl containing pub_id in that 

    //particular row by using findcontrol method

    GridViewRow row = ParentGridView.Rows[parent_index];
    Label pub_id_lbl = (Label)row.FindControl("pubid_lbl");
   
    //save pub_id and edit_index in session for childgridview's use

    Session["PubID"] = Convert.ToInt32(pub_id_lbl.Text);
    Session["ParentGridViewIndex"] = parent_index;
}

Child GridView

Let's take a look at the SelectCommand of the DataSource for the ChildGridView. Its WHERE clause needs pub_id, which we have already stored in Session[“Pub_ID”].

<SelectCommand="SELECT [title_id],[title], [type],[price] 
                FROM [titles] WHERE ([pub_id] = ?) ORDER BY [price]">
  <SelectParameters>
     <asp:SessionParameter Name="PubID" SessionField="PubID" Type="Int32" />
  </SelectParameters>

The GrandChildView is templated in the same way as ParentGridView. It also has a template field named ‘View’ to show its own child grid (GrandChildGridView) with the same buttons, and the same commands on the same positions. It also implements its OnRowEditing, which is a little different than that of its parent. Here, the child grid view is not directly accessible as it is nested in the parent one. To set its edit index with new edit index, first we need to find it from the particular row of ParentGridView. The row of ParentGridView containing the ChildGridView is found using ParentGridViewIndex from the session variable.

int parent_index =(int)Session["ParentGridViewIndex"];
GridViewRow parent_row = ParentGridView.Rows[parent_index];

We now have the row of ParentGirdView containing the ChildGridView. Now, we simply use the FindControl method to find the ChildGridView.

GridView ChildGridiView = 
     (GridView)Parent_row.FindControl("ChildGridView");

The title_id of the selected title is stored in the session, and that will be used by its own child gird, i.e., GrandChildGridView. The complete code for the event is as follows:

protected void ChildGridView_OnRowEditing(object sender, 
                             GridViewEditEventArgs e)
{
    //set the edit index of the child 

    //gridview with that of the current row

    int parent_index =(int)Session["ParentGridViewIndex"];
    GridViewRow parent_row = ParentGridView.Rows[parent_index];
    GridView ChildGridiView = 
        (GridView)parent_row.FindControl("ChildGridView");

    int child_index = e.NewEditIndex;
    ChildGridiView.EditIndex = child_index;
    ChildGridiView.DataBind();
    //find the titleid_lbl in that particular 

    //row by using findcontrol method

    GridViewRow child_row = ChildGridiView.Rows[child_index];
    Label titleid_lbl = (Label)child_row.FindControl("titleid_lbl");
    // save the title_id in session for grandchildgridview's use

    Session["TitleID"] = titleid_lbl.Text;
}

Here is the aspx source of ChildGridView. The template field named View shows the GrandChildGridView with a star that will be substituted at this place.

<asp:GridView ID="ChildGridView" 
       runat="server" AllowPaging="true" PageSize="4"
       AutoGenerateColumns="false" 
       DataSourceID="ChildSqlDataSource"
       OnRowEditing= "ChildGridView_OnRowEditing" >
        <Columns>
          <asp:TemplateField HeaderText="TitleID">
             <ItemTemplate>
                 <asp:Label id="titleid_lbl" Runat="Server" 
                 Text='<%# Eval("title_id") %>'/>
             </ItemTemplate>
             </asp:TemplateField>
             <asp:TemplateField HeaderText="Title">
             <ItemTemplate>
                <asp:Label id="title_lbl" Runat="Server" 
             Text='<%# Eval("title") %>'/>
          </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Type">
          <ItemTemplate>
           <asp:Label id="type_lbl" Runat="Server" 
           Text='<%# Eval("type") %>'/>
          </ItemTemplate>
       </asp:TemplateField>
       <asp:TemplateField HeaderText="Price">
          <ItemTemplate>
            <asp:Label id="price_lbl" Runat="Server" 
            Text='<%# Eval("price") %>'/>
          </ItemTemplate>
       </asp:TemplateField>
       <asp:TemplateField HeaderText="View">
         <ItemTemplate>
           <asp:Button ID="ViewGrandChild_Button" 
                 runat="server" Text="+"   CommandName="Edit"/>
        </ItemTemplate>
        <EditItemTemplate>
           <asp:Button ID="CancelGrandChild_Button" 
                 runat="server" Text="-" CommandName="Cancel" />
           *GrandChildGridView will come here
        </EditItemTemplate>
     </asp:TemplateField>
   </Columns>
</asp:GridView>

Grand Child GridView

The WHERE clause of GrandChildDataSource’s SelectCommand needs the value of title_id from its immediate parent table that was stored in the session when the particular title was selected. It is now being used to show all the rows of the roysched table against it in the GrandChildGridView.

<SelectCommand="select [lorange],[hirange],roysched.royalty 
                from [roysched], [titles] where 
                titles.title_id=roysched.title_id 
                and (roysched.title_id=?)">
 <SelectParameters>
    <asp:SessionParameter Name="title_id" 
         SessionField="TitleID" Type="String"/>
 </SelectParameters>

There is no need to use GrandChildGridView with template fields as we don't need any further child relationship with it. Otherwise, we'll have to implement it using template fields.

<asp:GridView ID="GrandChildGridView" runat="server" 
      AllowPaging="true" PageSize="4" 
      DataSourceID="GrandChildSqlDataSource">
</asp:GridView>

Points of Interest

It is important to know that I have used the GridView's Edit mode for displaying the child GridView. No editing is done in the Edit mode, yet simultaneously, it can be done if desired. The OnRowEditing event does not contain any code for editing, rather it does what is needed; the edit index and the primary key value stored in the session will be used in the OnRowEditing event of the child GridViews for searching the particular row of the parent GridView and finding the child GridView from it, and later will be used in the SelectCommand of the child GridView to get all the child rows against that particular parent ID. The Cancel command already does what we want; it cancels the editing, and the parent GridView comes out of the Edit mode hiding its child GridView.

If any parent GridView is collapsed in the hierarchy, its entire children will automatically collapse.

More importantly, it can be implemented for any number of levels by simply keeping the track of primary keys and edit indexes of all parent GridViews of a child GridView. Every child GridView knows its own edit index whereas it needs to know the edit index of all its parent GridViews, which can be provided by storing the edit index of each parent GridView in the session and using them when needed to find a particular row containing the child GridView. Lastly, always place a SqlDataSource control besides a GridView otherwise a child GridView will no longer be able to access it.