ASP.NET Gridview with Filter in Header

• Download source - 3.9 MB

Introduction

ASP.NET gridview by default provides facility for sorting and paging but no inbuilt facility to filter column. This article looks at possible way to implement filtering function within the gridview.

Background

I came across this requirement of having a gridview which allows filtering data from within the gridview. I also wanted to preserve the sorting and paging of the gridview.
Rather than creating separate panel above gridview for each of the fields to filter data, wouldn't it be nice to put a textbox along with each header column to filter data.
This leads me to this solution I derived for it. This may not be the best solution to do it but it definitely works. Our goal is to achieve this.

Using the Code

1. Create ASP.NET Web Application project in Visual Studio. First of all, we will create a DTO class to hold some data that we can display in a gridview. For this demo, I have created a DTO class of outstanding orders that contains some properties and some dummy data.

           [Serializable]
           public class Outstanding
           {
               public string Item { get; set; }
               public string Order { get; set; }
               public int Line { get; set; }
               public int Status { get; set; }
               public string ToLocation { get; set; }
               public decimal Qty { get; set; }
               public DateTime RegDate { get; set; }
               public string Location { get; set; }
               public decimal AllocQty { get; set; }
   
               public List<Outstanding> GetOutstanding()
               {
                   List<Outstanding> lstOrders = new List<Outstanding>();
   
                   lstOrders.Add(new Outstanding() { Item = "CocaCola", 
                   Order = "000101", Line = 1, Status = 20, 
                   ToLocation = "Sydney", 
                   Qty = 2000, RegDate = new DateTime(2014, 1, 1), 
                   Location = "USA", AllocQty = 100 });
                   lstOrders.Add(new Outstanding() { Item = "BubbleGum", 
                   Order = "000101", Line = 1, Status = 20, 
                   ToLocation = "Sydney", 
                   Qty = 2500, RegDate = new DateTime(2014, 1, 11), 
                   Location = "USA", AllocQty = 300 });
                   lstOrders.Add(new Outstanding() { Item = "Coffee", 
                   Order = "000111", Line = 1, Status = 50, 
                   ToLocation = "Melbourne", 
                   Qty = 2500, RegDate = new DateTime(2014, 1, 10), 
                   Location = "USA", AllocQty = 100 });
                   lstOrders.Add(new Outstanding() { Item = "Sugar", 
                   Order = "000112", Line = 1, Status = 50, 
                   ToLocation = "Melbourne", 
                   Qty = 2300, RegDate = new DateTime(2014, 1, 10), 
                   Location = "NZ", AllocQty = 300 });
                   lstOrders.Add(new Outstanding() { Item = "Milk", 
                   Order = "000112", Line = 1, Status = 50, 
                   ToLocation = "Melbourne", 
                   Qty = 2300, RegDate = new DateTime(2014, 1, 10), 
                   Location = "NZ", AllocQty = 200 });
                   lstOrders.Add(new Outstanding() { Item = "Green Tea", 
                   Order = "000112", Line = 1, Status = 20, 
                   ToLocation = "Melbourne", 
                   Qty = 300, RegDate = new DateTime(2014, 1, 10), 
                   Location = "NZ", AllocQty = 220 });
                   lstOrders.Add(new Outstanding() { Item = "Biscuit", 
                   Order = "000131", Line = 1, Status = 70, 
                   ToLocation = "Perth", 
                   Qty = 200, RegDate = new DateTime(2014, 1, 12), 
                   Location = "IND", AllocQty = 10 });
                   lstOrders.Add(new Outstanding() { Item = "Wrap", 
                   Order = "000131", Line = 1, Status = 20, 
                   ToLocation = "Perth", 
                   Qty = 2100, RegDate = new DateTime(2014, 1, 12), 
                   Location = "IND", AllocQty = 110 });
   
                   return lstOrders;
               }
           }

2. Now in the Default.aspx page, add a gridview. To preserve sorting, add link button in HeaderTemplate with CommandName as "Sort" and CommandArgument as name of the column. Also, for the purpose of filtering the application will bind all the textboxes to single event (OnTextChanged="txtItem_TextChanged" ) and within the event we will determine which textbox fired it and take action accordingly. So columns of the gridview will look like this. I have used different filters like =,>,<,>=&<= for numeric data and "contains" filter for string values.

       <asp:TemplateField SortExpression="Item">
       <HeaderTemplate>
         <asp:LinkButton ID="lbItem" runat="server" Text="Item" 
         CommandName="Sort" CommandArgument="Item"></asp:LinkButton>
         <br />
         <asp:TextBox runat="server" ID="txtItem" AutoPostBack="true" 
         OnTextChanged="txtItem_TextChanged"></asp:TextBox>
       </HeaderTemplate>
       <ItemTemplate>
          <%#Eval("Item") %>
       </ItemTemplate>
       </asp:TemplateField><asp:TemplateField SortExpression="Line" 
       ItemStyle-HorizontalAlign="Right" HeaderStyle-HorizontalAlign="Right">
        <HeaderTemplate>
           <asp:LinkButton ID="lbLine" runat="server" Text="Line" 
           CommandName="Sort" CommandArgument="Line"
           CssClass="RightAlign"></asp:LinkButton>
         <br />
        <table>
         <tr>
          <td>
           <asp:DropDownList runat="server" 
           ID="ddlFilterTypeLine" CssClass="upperCaseText">
           <asp:ListItem Text="=" Value="=" 
           Selected="True"></asp:ListItem>
           <asp:ListItem Text=">" Value=">"></asp:ListItem>
            <asp:ListItem Text=">=" Value=">="></asp:ListItem>
            <asp:ListItem Text="<" Value="<"></asp:ListItem>
           <asp:ListItem Text="<="  Value="<="></asp:ListItem>
            </asp:DropDownList>
         </td>
        <td>
        <asp:TextBox runat="server" ID="txtLine" Width="50" 
        AutoPostBack="true" OnTextChanged="txtItem_TextChanged" 
        CssClass="upperCaseText"></asp:TextBox>
       </td>
   </tr>
   </table></HeaderTemplate>
   <ItemTemplate>
   <%#Eval("Line","{0:0}")%>
   </ItemTemplate>
   </asp:TemplateField> 

3. Now in the Page_Load event, we will bind gridview to the dummy data. I have kept data in ViewState for this demo.

    if (!Page.IsPostBack)
               {
                   Outstanding objOutstanding = new Outstanding();
   
                   List<Outstanding> lstOutstandingOrders = new List<Outstanding>();
                   lstOutstandingOrders = objOutstanding.GetOutstanding();
   
                   ViewState["columnNameO"] = "RegDate";
                   grdViewOutstanding.DataSource = lstOutstandingOrders;
                   grdViewOutstanding.DataBind();
   
                   ViewState["lstOutstandingOrders"] = lstOutstandingOrders;
                   upnlOutstanding.Update();
               }  

4.  In the textbox's text change event, we will find out which textbox fired it by looking at the ID of a sender and take action accordingly. Finally, we will bind the data to gridview. To preserve the values in filter after postback, I created a seperate method which gets called everytime postback occurs and set values in corresponding textboxes and filters after postback.

    protected void txtItem_TextChanged(object sender, EventArgs e)
           {
   
               if (sender is TextBox)
               {
                   if (ViewState["lstOutstandingOrders"] != null)
                   {
                       List<Outstanding> allOutstanding = 
                       (List<Outstanding>)ViewState["lstOutstandingOrders"];
                       TextBox txtBox = (TextBox)sender;
                       if (txtBox.ID == "txtItem") //Find out which textbox 
                       				// fired the event  and take action 
                       {
                           allOutstanding = allOutstanding.Where
                           (x => x.Item.Contains(txtBox.Text.Trim().ToUpper())).ToList();
                           ViewState["OItemNo"] = txtBox.Text.Trim().ToUpper();
                          
                       }
                       else if (txtBox.ID == "txtOrder")
                       {
                           allOutstanding = allOutstanding.Where(x => 
                           x.Order.Contains(txtBox.Text.Trim().ToUpper())).ToList();
                           ViewState["OOrder"] = txtBox.Text.Trim().ToUpper();
                          
                       }
                       else if (txtBox.ID == "txtLine")
                       {
                           string filtrerType = ((DropDownList)grdViewOutstanding.HeaderRow.FindControl
                           ("ddlFilterTypeLine")).SelectedItem.Value;
   
                           if (filtrerType == "=")
                           {
                               allOutstanding = allOutstanding.Where
                               	(x => x.Line == int.Parse(txtBox.Text.Trim())).ToList();
                           }
                           else if (filtrerType == ">")
                           {
                               allOutstanding = allOutstanding.Where
                               (x => x.Line > int.Parse(txtBox.Text.Trim())).ToList();
                           }
                           else if (filtrerType == ">=")
                           {
                               allOutstanding = allOutstanding.Where
                               (x => x.Line >= int.Parse(txtBox.Text.Trim())).ToList();
                           }
                           else if (filtrerType == "<")
                           {
                               allOutstanding = allOutstanding.Where
                               (x => x.Line < int.Parse(txtBox.Text.Trim())).ToList();
                           }
                           else if (filtrerType == "<=")
                           {
                               allOutstanding = allOutstanding.Where
                               (x => x.Line <= int.Parse(txtBox.Text.Trim())).ToList();
                           }
                           ViewState["OFilterLine"] = filtrerType;
                           ViewState["OLine"] = txtBox.Text.Trim();
                           
                       } ..... ....ViewState["lstOutstandingOrders"] = allOutstanding; 
                       grdViewOutstanding.DataSource = allOutstanding; 
                       grdViewOutstanding.DataBind();ResetFilterAndValueOutstanding(); 

5.  ResetFilterAndValueOutstanding() method restores values in filter textbox and filter type in dropdown after each postback.

    protected void ResetFilterAndValueOutstanding()
           {
               if (ViewState["OItemNo"] != null)
                   ((TextBox)grdViewOutstanding.HeaderRow.FindControl("txtItem")).Text = 
                   ViewState["OItemNo"].ToString().ToUpper();
               if (ViewState["OOrder"] != null)
                   ((TextBox)grdViewOutstanding.HeaderRow.FindControl("txtOrder")).Text = 
                   ViewState["OOrder"].ToString().ToUpper();
               if (ViewState["OFilterLine"] != null)
               {
                   foreach (ListItem li in 
                   ((DropDownList)grdViewOutstanding.HeaderRow.FindControl("ddlFilterTypeLine")).Items)
                   {
                       if (li.Text == ViewState["OFilterLine"].ToString()) 
                       li.Selected = true; else li.Selected = false;
                   }
               }.... 

6. Add a link button on top of the gridview called "Remove Filter" which will reset all the ViewStates and rebind gridview to data and reset all filters to its original values.

    protected void lbRemoveFilterOutstanding_Click(object sender, EventArgs e)
           {
               if (ViewState["OItemNo"] != null) ViewState["OItemNo"] = null;
               if (ViewState["OOrder"] != null) ViewState["OOrder"] = null;
               if (ViewState["OFilterLine"] != null) ViewState["OFilterLine"] = null;
               if (ViewState["OLine"] != null) ViewState["OLine"] = null;
               if (ViewState["OFilterStatus"] != null) ViewState["OFilterStatus"] = null;
               if (ViewState["OStatus"] != null) ViewState["OStatus"] = null;
               if (ViewState["OLocation"] != null) ViewState["OLocation"] = null;
               if (ViewState["OToLocation"] != null) ViewState["OToLocation"] = null;
               if (ViewState["OFilterQty"] != null) ViewState["OFilterQty"] = null;
               if (ViewState["OQty"] != null) ViewState["OQty"] = null;
               if (ViewState["OFilterAllocQty"] != null) ViewState["OFilterAllocQty"] = null;
               if (ViewState["OAllocQty"] != null) ViewState["OAllocQty"] = null;
               if (ViewState["OFilterRegDate"] != null) ViewState["OFilterRegDate"] = null;
               if (ViewState["ORegDate"] != null) ViewState["ORegDate"] = null;
              
               Outstanding objOutstanding = new Outstanding();
               List<Outstanding> lstOutstandingOrders = new List<Outstanding>();
               lstOutstandingOrders = objOutstanding.GetOutstanding();
             
               grdViewOutstanding.DataSource = lstOutstandingOrders;
               grdViewOutstanding.DataBind();
   
               ViewState["lstOutstandingOrders"] = lstOutstandingOrders;
           }

Points of Interest

Gridview does not support filtering of data directly but it can be implemented using TemplateField in the Gridview.

History

• 22^rd January, 2014: Initial post