GRIDVIEW GROUPING

When displaying data, we sometimes would like to group data for better user experience or when displaying long list of hierarchal data, we would want to display them in a tree view kind of structure. There is more than way of doing this, but I am going to explain achieving this functionality using AJAX Collapsible Panel Extender Control.

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. Our objective is to list all the available sub categories and allow user to expand/collapse to look/hide the list of products belonging to each 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
Register the AjaxControlToolkit at the top of the page (or in the web.config for the whole project)

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

The ScriptManager makes sure that the required ASP.NET AJAX files are included and that AJAX support is added, and has to be included on every page where you wish to use AJAX functionality.

<asp:ToolkitScriptManager runat="server" ID="MainScriptManager" />

I am using SQLDataSource to load the products categories from database. The ConnectionString property of the SqlDataSource control specifies how to connect to the database. This property can be a hard-coded connection string or can point to a connection string in a Web.config file as shown in the code given above. TheSelectCommand property specifies the SQL statement to execute to retrieve the data.

<asp:SqlDataSource ID="sqlDsSubCategories" runat="server" ConnectionString="<%$ ConnectionStrings:Sql %>"

SelectCommand="Select ProductSubCategoryID, Name from

Production.ProductSubCategory">

</asp:SqlDataSource>

I am using AJAX Update Panel to enable partial postbacks to avoid flashing and flickering that occurs when an ASP.NET page posts back to the server and turn it into smooth, flicker-free updates.  Add a GridView control to the template content of the update panel, which will be used to display products subcategories. In order to bind the SQLDataSource created above to the grid, set the “DataSourceID” property of the GridView to “sqlDsSubCategories” (ID property value of SQLDataSource).  I have added an ItemTemplate to render all products related to subcategory.

<asp:UpdatePanel ID="pnlUpdate" runat="server">

<ContentTemplate>

<asp:GridView Width="60%" AllowPaging="True" ID="gvSubCategories"

AutoGenerateColumns="False"

GridLines="None"

PagerStyle-CssClass="pgr"

CssClass="mGrid"

DataSourceID="sqlDsSubCategories" runat="server"

ShowHeader="False"

OnRowCreated="gvSubCategories_RowCreated"

DataKeyNames="ProductSubCategoryID">

<Columns>

<asp:TemplateField>

<ItemStyle Width="200px" />

<ItemTemplate>

<asp:Panel ID="pnlSubCategories" runat="server">

<!--- Header Row-->

</asp:Panel>

<asp:Panel ID="pnlProducts" runat="server">

<!--- Filtered Products by SubCategory-->

</asp:Panel>

<asp:CollapsiblePanelExtender>

<!—different attributes -->

</asp:CollapsiblePanelExtender>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</ContentTemplate>

</asp:UpdatePanel>

I have added pnlSubCategories panel to the ItemTemplate. This is our  header/seperator panel . This is the panel that will expand or collapse the pnlProductspanel on a click. This comprises of an ImageControl  “imgCollapsible “ which would hold Expand/Collapse images.  Depending upon the collapsible state of  the panel, it would show either collapse image or expand image.  I am also displaying the name of the current SubCategory in a span element.

<asp:Panel ID="pnlSubCategories" runat="server">

<asp:Image ID="imgCollapsible" Style="margin-right:5px;" runat="server"/>

<span>SubCategory: <%#Eval("Name")%></span>

</asp:Panel>

I have added another panel pnlProducts to the ItemTemplate. This is the Panel that would expand and collapses on clicking expand/collapse buttons onpnlSubCategories panel. I have added gvProducts gridview control to this panel. In this gridview control, we display all the products for the current subcategory. The datasource for this gridview is a SqlDataSource object  “sqlDsProducts” which would accept ProductSubCategoryID as the parameter. This parameter is set in the RowCreated event of the gvSubCategories.

<asp:SqlDataSource ID="sqlDsProducts" runat="server"

ConnectionString="<%$ ConnectionStrings:Sql %>"

SelectCommand="Select ProductID, Name, ProductNumber

from Production.Product

where ProductSubCategoryID = @ProductSubCategoryID">

<SelectParameters>

<asp:Parameter Name="ProductSubCategoryID" Type="String"

DefaultValue="" />

</SelectParameters>

</asp:SqlDataSource>

<asp:Panel Style="margin-left: 20px; margin-right: 20px" ID="pnlProducts"

runat="server" Width="75%">

<asp:GridView AutoGenerateColumns="False" CssClass="mGrid"

ID="gvProducts" DataSourceID="sqlDsProducts"

runat="server" EnableViewState="False"  GridLines="None"

AlternatingRowStyle-CssClass="alt">

<Columns>

<asp:BoundField HeaderText="Product Name" DataField="Name"/>

<asp:BoundField HeaderText="Product Number"

DataField="ProductNumber"/>

</Columns>

</asp:GridView>

</asp:Panel>

protected void gvSubCategories_RowCreated(object sender,

GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.DataRow)

{

SqlDataSource ctrl = e.Row.FindControl("sqlDsProducts")

as SqlDataSource;

if (ctrl != null && e.Row.DataItem != null)

{

ctrl.SelectParameters["ProductSubCategoryID"].DefaultValue =

gvSubCategories.DataKeys[e.Row.RowIndex].Value.ToString();

}

}

}

All that is left is to add collapsible behavior between pnlSubCategories  and pnlProducts using CollapsiblePanelExtender .  I have added a CollapsiblePanelExtender control to the ItemTemplate. I have Set the TargetControlID  property to pnlProducts and ExpandControlID/ CollapseControlID properties to “pnlSubCategories” header panel. Initially we would want to load the panel in expanded, so I have set the Collapsed property to false. For this example, I am disabling mouse over AutoExpand & AutoCollapse functionality.  Finally, I have set the ExpandedImage & CollapsedImage properties to the url’s of the images you wanted to be displayed.

<asp:CollapsiblePanelExtender ID="ctlCollapsiblePanel"

runat="Server"

TargetControlID="pnlProducts"

CollapsedSize="0" Collapsed="True"

ExpandControlID="pnlSubCategories" CollapseControlID="pnlSubCategories"

AutoCollapse="False" AutoExpand="False" ScrollContents="false"

ImageControlID="imgCollapsible"

ExpandedImage="~/collapse.gif" CollapsedImage="~/expand.gif"

ExpandDirection="Vertical" />

Run the application and you would notice al products are grouped by SubCategory and are in collapsed state. 

Click on expand images to unhide any subcategory group and look at all products belonging to that subcategory.

Complete Design

<asp:ToolkitScriptManager runat="server" ID="MainScriptManager" />

<asp:SqlDataSource ID="sqlDsSubCategories" runat="server"

ConnectionString="<%$ ConnectionStrings:Sql %>"

SelectCommand="Select ProductSubCategoryID, Name from Production.ProductSubCategory">

</asp:SqlDataSource>

<asp:UpdatePanel ID="pnlUpdate" runat="server">

<ContentTemplate>

<asp:GridView Width="45%" AllowPaging="True" ID="gvSubCategories"

AutoGenerateColumns="False"

GridLines="None"

CssClass="mGrid"

DataSourceID="sqlDsSubCategories" runat="server"

ShowHeader="False"

OnRowCreated="gvSubCategories_RowCreated"

DataKeyNames="ProductSubCategoryID">

<Columns>

<asp:TemplateField>

<ItemStyle Width="200px" />

<ItemTemplate>

<asp:Panel ID="pnlSubCategories" runat="server">

<asp:Image ID="imgCollapsible"

Style="margin-right: 5px;" runat="server" />

<span style="font-weight:bold">

SubCategory: <%#Eval("Name")%>

</span>

</asp:Panel>

<asp:SqlDataSource ID="sqlDsProducts" runat="server"

ConnectionString="<%$ ConnectionStrings:Sql %>"

SelectCommand="Select ProductID, Name,

ProductNumber from Production.Product

where ProductSubCategoryID= @ProductSubCategoryID">

<SelectParameters>

<asp:Parameter Name="ProductSubCategoryID"

Type="String"

DefaultValue="" />

</SelectParameters>

</asp:SqlDataSource>

<asp:Panel ID="pnlProducts" runat="server"

Width="75%"

Style="margin-left:20px;margin-right:20px;

height:0px;overflow: hidden;">

<asp:GridView AutoGenerateColumns="False"

CssClass="mGrid" ID="gvProducts"

DataSourceID="sqlDsProducts"

runat="server" EnableViewState="False"

GridLines="None"

AlternatingRowStyle-CssClass="alt">

<Columns>

<asp:BoundField HeaderText="Product Name"

DataField="Name"/>

<asp:BoundField

HeaderText="Product Number"

DataField="ProductNumber"/>

</Columns>

</asp:GridView>

</asp:Panel>

<asp:CollapsiblePanelExtender

ID="ctlCollapsiblePanel"

runat="Server"

TargetControlID="pnlProducts"

CollapsedSize="0" Collapsed="True"

ExpandControlID="pnlSubCategories"

CollapseControlID="pnlSubCategories"

AutoCollapse="False" AutoExpand="False"

ScrollContents="false"

ImageControlID="imgCollapsible"

ExpandedImage="~/collapse.gif"

CollapsedImage="~/expand.gif"

ExpandDirection="Vertical" />

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</ContentTemplate>

</asp:UpdatePanel>