Self-referencing hierarchy

Self-referencing hierarchy refers to the database setup where ID -> ParentID relation is defined in the same table. A typical example for this is the Employee table in the Northwind database, where the table Employee has ID (primary key) and ReportsTo (which points to the ID in the same table).

The end result will look like this:

The key here is to set the HierarchySettings.HierarchyMode of the parent grid to Parent and to hook its client-side event SubGridRowExpanded. This can be achieved in the following way:

<trirand:JQGrid runat="server" ID="JQGrid1" DataSourceID="SqlDataSource1" Width="650px" Height="300px">
   <Columns>
      <trirand:JQGridColumn DataField="EmployeeID" PrimaryKey="True" />
      <trirand:JQGridColumn DataField="LastName" />
      <trirand:JQGridColumn DataField="FirstName" />
      <trirand:JQGridColumn DataField="Title" />
   </Columns>        
   <HierarchySettings HierarchyMode="Parent" />
   <ClientSideEvents SubGridRowExpanded="showSubGrid" />
</trirand:JQGrid>
    
<asp:SqlDataSource runat="server" ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:SQL2008_449777_fhsConnectionString %>"        
   SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [Title] FROM [Employees]">
</asp:SqlDataSource>

The client-side event designated by ClientSideEvents.SubGridRowExpanded should call a special autogenerated function which will expand the grid. The name of the autogenerated function is fixed and is always "showSubGird_GridID", where "GridID" is the ID of the grid. Example:

<script type="text/javascript">    
   function showSubGrid(subgrid_id, row_id) 
   {
      // the "showSubGrid_JQGrid2" function is autogenerated and available globally on the page by the second child grid. 
      // Calling it will place the child grid below the parent expanded row and will call the OnDataRequesting event 
      // of the child grid, with ID equal to the ID of the parent expanded row
      showSubGrid_JQGrid2(subgrid_id, row_id);
   }   
</script>

Setting up the the second grid is similar to that, the difference being that the HierarchySettings.HierarchyMode property should be set to ParentAndChild. In most cases you would also typically need to hook the OnDataRequesting event - this even will fire just prior to binding the grid with data and can be used to pass the parent row key to the child grid before requesting the data - this is usually required to change the SQL query to only fetch records that are children of the parent row. Similar to the parent grid, you would also need to hook the client-side event SubGridRowExpanded, in order to show the next nested self-referenced levels.

<trirand:JQGrid runat="server" ID="JQGrid2" DataSourceID="SqlDataSource2"
   Width="550px" Height="100%"
   HierarchyMode="ParentAndChild" 
   OnDataRequesting="JQGrid2_DataRequesting">
   <Columns>
      <trirand:JQGridColumn DataField="EmployeeID" PrimaryKey="True" />
      <trirand:JQGridColumn DataField="LastName" />
      <trirand:JQGridColumn DataField="FirstName" />
      <trirand:JQGridColumn DataField="Title" />            
   </Columns>
   <HierarchySettings HierarchyMode="ParentAndChild" />        
   <ClientSideEvents SubGridRowExpanded="showSubGrid" />
</trirand:JQGrid>
    
<asp:SqlDataSource runat="server" ID="SqlDataSource2" ConnectionString="<%$ ConnectionStrings:SQL2008_449777_fhsConnectionString %>"        
   SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [Title], [ReportsTo] FROM [Employees] WHERE ([ReportsTo] = @ReportsTo)">
   <SelectParameters>
      <asp:Parameter Name="ReportsTo" Type="Int32" />
   </SelectParameters>
</asp:SqlDataSource