Sunday, 26 June 2011

Hierarchical GridView in ASP.NET

Today I am going to post on how to show GridViews on Hierarchical style. In some time, we required to show the records in Master – Details structure where the master information will show initially and the detail information will show on demand for a particular master detail.

In the below implementation, I am going the take the following requirement to implement.

  1. In a page by default list of Orders will show with + icon on each row.
  2. By clicking + icons on a row, the Order Detail grid will show about the particular master detail just below the row.
  3. Once the detail information is visible, the + icon must be changed to – icon to make the detail invisible.
  4. On click of – icon, the detail grid will be invisible.

In this example I am using NorthWind sql server database for example purpose. So to test this example code, please make sure to have NorthWind database setup done with your database server.

The implementation follows:

As I have two type of GridView which will show entirely different columns, I have to fetch two records set results. So I am creating a store procedure which will return two select query result.
CREATE PROC [OrderDetails]
AS
SELECT Orders.OrderID,
  Orders.CustomerID,
  Orders.ShippedDate,
  Orders.ShipName,
  Orders.ShipCountry,
  Orders.ShipCity
FROM Orders
SELECT OrderDetails.OrderID,
  OrderDetails.ProductID,
  Products.ProductName,
  OrderDetails.UnitPrice,
  OrderDetails.Quantity,
  OrderDetails.Discount,
  ((OrderDetails.UnitPrice * OrderDetails.Quantity) - OrderDetails.Discount) Amount
FROM [Order Details] OrderDetails
JOIN Products ON Products.ProductID = OrderDetails.ProductID

When I get the result to the DataSet, I will get records two tables in separate Table index. So the DataSet.Table[0] provides Order information and DatSet.Table[1] will provide Order Details information.

Below is the GridView Script which contains one more GridView inside of a column.
<asp:GridView id="GridViewHierachical" runat="server" AutoGenerateColumns="False"
    Width="100%" CellPadding="0" CellSpacing="2" GridLines="Vertical" DataKeyNames="OrderId" AllowPaging="true"
    BackColor="White" BorderWidth="1px" BorderStyle="Solid" BorderColor="Black" PageSize="20" style="border-right:none;"
    OnPageIndexChanging="GridViewHierachical_PageIndexChanging"
    OnRowDataBound="GridViewHierachical_RowDataBound">
    <Columns>
        <asp:TemplateField HeaderText="Order ID">
            <ItemStyle Width="10%" />
            <ItemTemplate>
                <a href="javascript:ExpandCollapse('div<%# Eval("OrderID") %>');" style="padding-left:2px;">
                    <img id="imgdiv<%# Eval("OrderID") %>" alt=""  width="9px" border="0" src="images/plus.gif"/>
                </a>                    
                <asp:Label ID="lblOrderID" Text='<%# Eval("OrderID") %>' Visible="true" runat="server"></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="CustomerID" HeaderText="Customer ID">
            <ItemStyle Width="10%" />
        </asp:BoundField>
        <asp:BoundField DataField="ShippedDate" HeaderText="Shipped Date"  DataFormatString="{0:MMMM d, yyyy}">
            <ItemStyle Width="20%" />
        </asp:BoundField>
        <asp:BoundField DataField="ShipName" HeaderText="Ship Name">
            <ItemStyle Width="20%" />
        </asp:BoundField>
        <asp:BoundField DataField="ShipCountry" HeaderText="Ship Country">
            <ItemStyle Width="15%" />
        </asp:BoundField>
        <asp:BoundField DataField="ShipCity" HeaderText="Ship City">
            <ItemStyle Width="15%" />
        </asp:BoundField>
        <asp:TemplateField>
            <HeaderStyle CssClass="InvisibleCol" />
            <ItemStyle CssClass="InvisibleCol" />                                                        
            <ItemTemplate>
            </td></tr>
                <tr>
                    <td colspan="6">
                        <div id="div<%# Eval("OrderID") %>" style="display:none;position:relative;left:10px;overflow:auto;width:99%;">
                            <asp:GridView id="GridViewDetails" runat="server" 
                                AllowSorting="false" AutoGenerateColumns="False" Width="100%"
                                CellSpacing="1" CellPadding="0" GridLines="Both" DataKeyNames="OrderId"
                                BackColor="White" BorderWidth="2px" BorderStyle="Ridge"
                                BorderColor="White" AllowPaging="false" ForeColor="#000066">
                                <Columns>
                                    <asp:BoundField DataField="OrderId" HeaderText="Order ID">
                                        <ItemStyle Width="10%" />
                                    </asp:BoundField>
                                    <asp:BoundField DataField="ProductName" HeaderText="Product Name">
                                        <ItemStyle Width="30%" />
                                    </asp:BoundField>
                                    <asp:BoundField DataField="UnitPrice" HeaderText="Unit Price" DataFormatString="{0:N}" ItemStyle-HorizontalAlign="Right">
                                        <ItemStyle Width="15%" />
                                    </asp:BoundField>
                                    <asp:BoundField DataField="Quantity" HeaderText="Quantity" DataFormatString="{0:N}" ItemStyle-HorizontalAlign="Right">
                                        <ItemStyle Width="15%" />
                                    </asp:BoundField>
                                    <asp:BoundField DataField="Discount" HeaderText="Discount" DataFormatString="{0:N}" ItemStyle-HorizontalAlign="Right">
                                        <ItemStyle Width="15%" />
                                    </asp:BoundField>
                                    <asp:BoundField DataField="Amount" HeaderText="Amount" DataFormatString="{0:N}" ItemStyle-HorizontalAlign="Right">
                                        <ItemStyle Width="15%" />
                                    </asp:BoundField>
                                </Columns>
                                <RowStyle ForeColor="#000066" Height="20px"></RowStyle>
                                <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White"></SelectedRowStyle>
                                <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left"></PagerStyle>
                                <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White"></HeaderStyle>
                                <AlternatingRowStyle BorderStyle="Solid" BorderWidth="0px"></AlternatingRowStyle>
                            </asp:GridView>
                        </div>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
    <FooterStyle BackColor="#CCCCCC" ForeColor="Black"></FooterStyle>
    <RowStyle BackColor="#EEEEEE" ForeColor="Black" Height="22px"></RowStyle>
    <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" Height="22px"></HeaderStyle>
    <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White"></SelectedRowStyle>
    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Right"></PagerStyle>
    <AlternatingRowStyle BorderStyle="Solid" BorderWidth="0px" BackColor="#DCDCDC"></AlternatingRowStyle>
</asp:GridView>

If you note correctly the script, the TemplateField of the last column contains script to complete the column and complete the current row by using </td></tr>. So once the Ship City column completed and when last column rendering, the column will end by our script and start a new row. In the new row, the Child GridView will be rendered.

Below is C# code.
private DataSet dsOrderDetails = null;
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGrid();
    }
}
private void BindGrid()
{
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
    {
        SqlCommand command = new SqlCommand("OrderDetails", connection);
        command.CommandType = CommandType.StoredProcedure;

        SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
        connection.Open();

        dsOrderDetails = new DataSet();
        dataAdapter.Fill(dsOrderDetails);

        GridViewHierachical.DataSource = dsOrderDetails.Tables[0];
        GridViewHierachical.DataBind();
    }
}

protected void GridViewHierachical_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridViewHierachical.PageIndex = e.NewPageIndex;
    BindGrid();
}

protected void GridViewHierachical_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        // Additional check for the row is not header or footer
        if (e.Row.DataItem == null) return;

        // Getting the GridViewDetails child grid
        GridView GridViewDetails = (GridView)e.Row.FindControl("GridViewDetails");

        // Getting the Order ID
        Label lblOrderID = (Label)e.Row.FindControl("lblOrderID");

        // Filtering the records in the second table records. The second table in the dataset contains list of order details for all orders.
        dsOrderDetails.Tables[1].DefaultView.RowFilter = "OrderID=" + lblOrderID.Text; // Normal SQL Where condition only
        GridViewDetails.DataSource = dsOrderDetails.Tables[1].DefaultView;
        GridViewDetails.DataBind();
    }
}
Below is VB code:
Private dsOrderDetails As DataSet = Nothing
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            BindGrid()
        End If
    End Sub
    Private Sub BindGrid()
        Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("SQLConnection").ConnectionString)
            Dim command As New SqlCommand("OrderDetails", connection)
            command.CommandType = CommandType.StoredProcedure

            Dim dataAdapter As New SqlDataAdapter(command)
            connection.Open()

            dsOrderDetails = New DataSet()
            dataAdapter.Fill(dsOrderDetails)

            GridViewHierachical.DataSource = dsOrderDetails.Tables(0)
            GridViewHierachical.DataBind()
        End Using
    End Sub

    Protected Sub GridViewHierachical_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
        GridViewHierachical.PageIndex = e.NewPageIndex
        BindGrid()
    End Sub

    Protected Sub GridViewHierachical_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
        If e.Row.RowType = DataControlRowType.DataRow Then
            ' Additional check for the row is not header or footer
            If e.Row.DataItem Is Nothing Then
                Return
            End If

            ' Getting the GridViewDetails child grid
            Dim GridViewDetails As GridView = DirectCast(e.Row.FindControl("GridViewDetails"), GridView)

            ' Getting the Order ID
            Dim lblOrderID As Label = DirectCast(e.Row.FindControl("lblOrderID"), Label)

            ' Filtering the records in the second table records. The second table in the dataset contains list of order details for all orders.
            dsOrderDetails.Tables(1).DefaultView.RowFilter = "OrderID=" + lblOrderID.Text
            ' Normal SQL Where condition only
            GridViewDetails.DataSource = dsOrderDetails.Tables(1).DefaultView
            GridViewDetails.DataBind()
        End If
    End Sub
In the RowDataBound event, we are getting the Child GridView (GridViewDetails) and assigning the records to the GridView programmatically and calling DataBind Method. Before calling DataBind, the records are filtered by RowFilter of dataset table and assigning to the GridView. I also declared dsOrderDetails variable in Page level scope as I required the second table resultsets in the RowDataBound event.

I have a Javascript, which is used to Expand and Collapse the Child grid by clicking the +/- icons on the row.
function ExpandCollapse(obj) {
    var div = document.getElementById(obj);
    var img = document.getElementById('img' + obj);

    if (div) {
        if (div.style.display == "none") {
            div.style.display = "block";
            img.src = "images/minus.gif";

        }
        else {
            div.style.display = "none";
            img.src = "images/plus.gif";
        }
    }
}

I also used a CSS class for hiding the last TemplateField column which hold the Child Grid.
.InvisibleCol
{
    display:none;
}

This code has been tested with IE 6.0/9.0, Firefox 3.6, Opera 11.01

Below is the output of the screen,
Initial Page (with +, - buttons)

Page with Hierarchical structure

download the working example of the source code in C# here and in VB here

[Update]
I have blogged another post on the same topic using AJAX and JQuery concepts. Below are the links

Hierarchical GridView in ASP.NET with AJAX, Javascript implementation
Hierarchical GridView in ASP.NET with AJAX, JQuery implementation - Part 1
Hierarchical GridView in ASP.NET with AJAX, JQuery implementation - Part 2


22 Responses to “Hierarchical GridView in ASP.NET”

  • Anonymous says:
    22 February 2012 at 02:17

    I tried downloading the code but I could not..
    Please can you advice on how to get a copy of the code

  • Thiru says:
    22 February 2012 at 11:11

    Hi,
    I verified the download urls, it works fine. If you still face any issues, please let me know your mail id. So I can send you the source code on mail.

  • Anonymous says:
    22 February 2012 at 12:15

    Hi, Thank you for the speedy response. My email address is seethal1@gmail.com.. I would reaaly appreciate if you could also email the ajax implementation of the Hierarchical GridView.

  • Thiru says:
    22 February 2012 at 12:48

    Hi. The source code has been sent. Pls check your mail id.

  • hira says:
    4 May 2012 at 17:55

    hi... can u give me a code on my id ?

  • hira says:
    4 May 2012 at 18:32

    kindly email the ajax implementation of the Hierarchical GridView it gives an error.

  • Thiru says:
    4 May 2012 at 19:17

    I could not find your mail id. Pls send a test mail to pm.thirumalai@gmail.com. I will reply back code. And what is the error you are getting?

  • hira says:
    5 May 2012 at 18:57

    compiled correctly but at run tyme it gives error .. so kindly mail me da code at hiraahmed@live.com

  • Learning Geeks says:
    24 May 2012 at 12:17

    Hi Thiru,
    From where can i get the same Northwind.mdf which you have used in this project? Also i want to use file based DB in this project to test, so i just want to add .mdf to App_Data. Please let know download url for right version of northwind. I tried downloading one but it has different sp names etc.

  • Thiru says:
    24 May 2012 at 13:21

    Hi Learning Geeks,

    Please follow the steps for having mdf file in the App_Data folder.

    1. Right click the App_Data folder and select Add -> Add New Item.
    2. Select SQL Server Database, provide the Name and Press Add.
    3. There will be a database in the App_Data folder. Right click and select Detach.
    4. Open the SQL Server Management Studio and connect a server (may be local sql express server).
    5. Right click the Database and select Attach. Press Add.
    6. Select the mdf file created and select OK. It will add the database in Management Studio.
    7. Download the Northwind script file from the following url http://www.dotnettwitter.com/2011/05/northwind-database-scripts-for-sql.html and run it in the attached database (remove USE Northwind - if any)
    8. Run the OrderDetails View script provided in this post.
    9. Detach the database (drop connection if any).
    10. Modify the Web.Config connection string as below
    <add name="SQLConnection" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=C:\Thiru\Blog\HierarchicalGrid\HierarchicalGrid\App_Data\Northwind.mdf; Integrated Security=True; Connect Timeout=30; User Instance=True" />
    (Note - Change the mdf file name and path accordingly)
    11. Run the project and verify.
    :)

  • Shawn says:
    1 June 2012 at 21:41

    Any suggestions for adding a 'Delete' button to the detail gridview. Everything I've tried leads to not having access to the dynamic gridview.

    Help?

    Thanks!!!

  • Thiru says:
    1 June 2012 at 22:42

    Hi. Shawn, You can add Delete button as normally in this example. Add Delete button in the Child grid with the event handler defined for that. It should work.

    If you still face issue, send your detail requirement to pm.thirumalai@gmail.com.

  • Brian Smith says:
    30 November 2012 at 21:14

    I really trying here, but I'm stumped on converting this to be able to use an Access Database. Can you give any help as to how to replace the stored procedure with something that will work with access?

  • Thiru says:
    30 November 2012 at 22:49

    Hi Brain, You need to use OleDbDataAdapter for fetching records from MS Access. Just google it "how to bind records from MS Access to GridView".
    For Ex:
    conn.Open();
    OleDbDataAdapter da = new OleDbDataAdapter("select * from tablename", conn);
    DataSet ds = new DataSet();
    da.Fill(ds,"tablename");
    GridView1.DataSource = ds.Tables[0];
    GridView1.DataBind();
    conn.Close();

  • Brian Smith says:
    30 November 2012 at 23:20

    wow, that was a quick response. I have the VB syntax worked out to function with Access. Where I'm having the issue is getting the required response in the select command. The stored procedure you have in your example returns 2 select statement. I'm not sure how to do this with an Access DB.

  • Thiru says:
    1 December 2012 at 09:13

    That could be possible by selecting two different dataset and adding one table to another. For Ex:
    OleDbDataAdapter da = new OleDbDataAdapter("select * from tablename", conn);
    DataSet ds = new DataSet();
    da.Fill(ds,"tablename");

    OleDbDataAdapter da1 = new OleDbDataAdapter("select * from tablename", conn);
    DataSet ds1 = new DataSet();
    da.Fill(ds1,"tablename");

    da.Tables.Add(da.Tables[1]);

    I did not tried yet, this type of code. But it should be possible. Pls Google it.

  • Anonymous says:
    2 April 2013 at 21:26

    Thank you soo much for the article and the code, it really helped a lot.

  • Anonymous says:
    16 June 2013 at 17:08

    Its good article

  • Vi Lê says:
    21 November 2014 at 12:44

    I try to use AllowPage="true" for GridHierarchy but not active, help me please !. Thaks

  • Siva Reddy says:
    18 December 2015 at 16:20

    Thanks its working fine...
    now my requirement is i need a grill down in another grill down (Nested Grill down) pls helpme.

  • Anonymous says:
    21 March 2016 at 12:39

    Thanks it is working fine...
    but how i can retrieve the data from the parent grid(when clicking +).

  • Anonymous says:
    18 August 2016 at 23:54

    hi any chance you know why this example is having this error?
    The RegisterRequiresViewStateEncryption() method needs to be called before or during Page_PreRender.

Post a Comment