Grouping in Asp.net gridview control

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 Tableobject’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:

source : http://technico.qnownow.com/grouping-gridview-aspnet/