• Grouping in ASP.NET GridView Control

    Posted on April 15, 2012 by in ASP.NET, C#, Dotnet


    When displaying data, we sometimes would like to group data for better user experience.When displaying a long list of sorted data where there are only a handful of different values in the sorted column, an end user might find it hard to discern where, exactly, the difference boundaries occur. For example, there are 81 products in the database, but only nine different category choices. To help highlight the boundaries between sorted groups, many Web sites employ a user interface that adds a separator between such groups. In this example, that is what exactly we are going to do.

    I am not going to cover any basics in this article. If you are new to using grid view or if you would like know more about grid view, I would recommend you to reach following tutorials:
    Using GridView in ASP.NET & C# —PART 1
    Using GridView in ASP.NET & C# —PART 2

    Overview:
    I am going to use Adventure Works as datasource. Every product in Production.Product table belongs to a product sub category. We fetch handful of products and the sub categories they belong to from the database. These products are sorted by ProductSubCategoryID. On the web page, we will add a group header row at the starting of every subcategory.

    Database Connection
    Added following entry under connectionStrings element in web.config.

    <add name="Sql" connectionString="Data Source=(local);
    Initial Catalog=AdventureWorks; User=testuser; Password=testuser;"
    providerName="System.Data.SqlClient"/>
    

     
    Page Design
    I have created a ASP.NET web application project and added a new web page “NestedProductsView.aspx”. I have added the Gridview control to the web page and applied some simple formatting to make it look nice. We are interested in ProdutID, ProductName, ProductNumber and LisPrice attributes of the product. I have added four BoundColumns to the GridView to render these attributes. I have additional formatting to format the Price column as a currency column. Finally, I have added a hidden field to the grid rendered in a TemplateField. The hidden field is mapped to the subcategory name of the data source. We use this hidden field in the code-behind class to figure out the start/end position of the group header row.

    <asp:gridview id="gvProducts"
      autogeneratecolumns="False"
      emptydatatext="No data available."
      GridLines="None"
      runat="server" DataKeyNames="ProductID"
      CssClass="GridStyle">
      <AlternatingRowStyle CssClass="AlternatingRowStyle" />
      <HeaderStyle CssClass="ColumnHeaderStyle" />
    <Columns>
        <asp:BoundField DataField="ProductID" HeaderText="Product ID">
            <ItemStyle Width="200px"/>
        </asp:BoundField>
        <asp:BoundField DataField="Name" HeaderText="Product Name”>
            <HeaderStyle HorizontalAlign="Left"/>
            <ItemStyle Width="200px" HorizontalAlign="Left"/>
        </asp:BoundField>
        <asp:BoundField DataField="ProductNumber" HeaderText="Product Number" />
        <asp:BoundField HeaderText="Price"
                DataField="ListPrice"
                DataFormatString="{0:c}">
            <ItemStyle HorizontalAlign="Right"></ItemStyle>
        </asp:BoundField>
       <asp:TemplateField>
            <ItemTemplate>
                <asp:HiddenField ID="hfSubCategory" runat="server"
                                 Value='<%#Eval("SubCategoryName")%>' />
            </ItemTemplate>
       </asp:TemplateField>
    </Columns>
    </asp:gridview>
    

     
    Source Code:
    First and foremost thing to do is loading the grid with list of products that are ordered by subcategory. I did that in the Page_Load event of the page and saved products list in the ViewState for any page refreshes.

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
            BindData();
    }
    private void BindData()
    {
        //Bind the grid view
        gvProducts.DataSource = RetrieveProducts();
        gvProducts.DataBind();
    }
    private DataSet RetrieveProducts()
    {
        if (ViewState["Products"] != null)
            return (DataSet)ViewState["Products"];
    
        //fetch the connection string from web.config
        string connString = ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
    
        //SQL statement to fetch sorted entries from products            
        string sql = @"Select top 20 P.*,PS.ProductSubCategoryID,PS.Name as 
                    SubCategoryName from Production.Product P
                    inner join Production.ProductSubCategory PS 
                    on P.ProductSubCategoryID = PS.ProductSubCategoryID 
                    order by PS.ProductSubCategoryID desc";
    
        DataSet dsProducts = new DataSet();
        //Open SQL Connection
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            //Initialize command object
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //Fill the result set
                adapter.Fill(dsProducts);
    
            }
        }
        return dsProducts;
    }
    

     
    When the GridView is bound to a data source, it creates a GridViewRow for each record returned by the data source. Therefore, we can inject the needed separator rows by adding “separator records” to the data source before binding it to the GridView. Because we only want to add the separator rows to the GridView‘s control hierarchy after its control hierarchy has been created and created for the last time on that page visit, we want to perform this addition at the end of the page lifecycle, but before the actual GridView control hierarchy has been rendered into HTML. The latest possible point at which we can accomplish this is the Page class’s Render event, which we can override in our code-behind class.

    We get the reference to the GridView’s Table object. We start by iterating through all the rows in the grid view. For each row, we get the reference to the hidden field control. We store the value of the field in the currSubCategory field. I have created another string variable lastSubCategory, which holds the last row’s sub category value. Comparing these two variables would tell you whether to insert the seperator row. This is accomplished by determining the index of the GridViewRow in the Table object’s Rows collection, creating new GridViewRow and TableCell instances, and then adding the TableCell and GridViewRow to the control hierarchy.

    Note that the separator row’s lone TableCell is formatted so that it spans the entire width of the GridView, is formatted using the GroupHeaderRowStyle CSS class, and has its Text property such that it shows both the group name (such as “SubCategory”) and the group’s value (such as “Tires and Tubes”). Finally, lastSubCategory is updated to the value of currSubCategory.

    protected override void Render(HtmlTextWriter writer)
    {
        string lastSubCategory = String.Empty;
        Table gridTable = (Table)gvProducts.Controls[0];
        foreach (GridViewRow gvr in gvProducts.Rows)
        {
            HiddenField hfSubCategory = gvr.FindControl("hfSubCategory") as
                                        HiddenField;
            string currSubCategory = hfSubCategory.Value;
            if (lastSubCategory.CompareTo(currSubCategory) != 0)
            {
                int rowIndex = gridTable.Rows.GetRowIndex(gvr);
                // Add new group header row
                GridViewRow headerRow = new GridViewRow(rowIndex, rowIndex,
                    DataControlRowType.DataRow, DataControlRowState.Normal);
                TableCell headerCell = new TableCell();
                headerCell.ColumnSpan = gvProducts.Columns.Count;
                headerCell.Text = string.Format("{0}:{1}", "SubCategory",
                                                currSubCategory);
                headerCell.CssClass = "GroupHeaderRowStyle";
                // Add header Cell to header Row, and header Row to gridTable
                headerRow.Cells.Add(headerCell);
                gridTable.Controls.AddAt(rowIndex, headerRow);
                // Update lastValue
                lastSubCategory = currSubCategory;
            }
        }
        base.Render(writer);
    }
    

     
    I have used the following CSS style sheet for formatting the grid and its rows.

    body {
    	margin: 0;
    	background-color: #FFFFFF;
    	color: #000000;
    	font-family: Verdana, Arial, Helvetica, sans-serif;
    }
    .GridStyle
    {
        font-size: 90%;
    }
    .ColumnHeaderStyle
    {
        background-color: #000000;
        color: White;
        font-weight: bold;
    }
    .AlternatingRowStyle
    {
        background-color: #66CCFF;
    }
    .RowStyle
    {
        background-color: #66CCFF;
    }
    .GroupHeaderRowStyle
    {
        background-color: Blue;
        text-align: left;
        font-weight: bold;
        color: White;
    }
    

     
    Page Output:

    Be Sociable, Share!
      Post Tagged with , ,

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    12 Responsesso far.

    1. Daniel says:

      Hi

      I would like to use this technique but instead of listing the rows below the category. I want to list them in the right hand side cell of the category (header) cell. In this case I guess I would need to change the rowspan in each of the product info cells, based on the total number products were under a give category.

      • Venugopal says:

        you could achieve this by changing the design of grid view and modifying rendering logic

        add new template field column at the top of the grid view columns.
        <Columns>    
            <asp:TemplateField>
                <ItemStyle Width="230px"/>
            </asp:TemplateField>
            <asp:BoundField DataField="ProductID" HeaderText="Product ID">
                <ItemStyle Width="200px" HorizontalAlign="Center"/>
            </asp:BoundField>
            .....
            .....          
        </Columns>
        
        protected override void Render(HtmlTextWriter writer) 
        {    
            string lastSubCategory = String.Empty;
            Table gridTable = (Table)gvProducts.Controls[0]; 
            foreach (GridViewRow gvr in gvProducts.Rows)         
            {
                HiddenField hfSubCategory = gvr.FindControl("hfSubCategory") as HiddenField;
                string currSubCategory = hfSubCategory.Value;
                if (lastSubCategory.CompareTo(currSubCategory) != 0)             
                {                                     
                    int rowIndex = gridTable.Rows.GetRowIndex(gvr);                 
                    // Add new group header row                
                    GridViewRow headerRow = new GridViewRow(rowIndex, rowIndex,                     
                        DataControlRowType.DataRow, DataControlRowState.Normal);
                    gvr.Cells[0].Text = string.Format("{0}:{1}", "SubCategory", currSubCategory);                            
                    // Update lastValue                 
                    lastSubCategory = currSubCategory;             
                }         
            }
            base.Render(writer); 
        }
        
        • john says:

          Specified argument was out of the range of valid values on Table grdTable = (Table)grdInwartTrans.Controls[0];

          need your comments

    2. Nishat says:

      Index out of range on => Table gridTable = (Table)gridview1.Controls[0];

    3. Kiki Giwa says:

      Hi,
      One of the most beautiful and simple way to group that in a gridview.
      I have just tested it; and it worked perfectly.

      I will very much appreciate if you could extend this solution to calculate product price per group.

      Thank you.

    4. john says:

      out of the range of valid values on Table grdTable = (Table)grdInwartTrans.Controls[0];

      need your comments

    5. ramnai says:

      Everything is good. But it has design issue whenever there is a paging in the grid.

    6. Khaled Kokah says:

      Simply: Amazing, great job indeed this helped me a lot.
      Thanks.

    7. Khaled Kokah says:

      That was simply great, helped me a lot, thanks.

    8. Eyob says:

      What would you do if you want to group by 2 items? This example works perfectly to group by 1 item.

    9. Ranganath P Deshpande says:

      it is displaying fine, but if I have first column check box and if I checked it is not working ( 1st row is a header, 2nd row if I checked it is showing row index as 0)

    Leave a Reply