In this article I will explain with an example, how to build Excel like AutoFilter Feature in ASP.Net GridView control using C# and VB.Net.
In Excel file, the Auto Filter function works within the Header Row and hence this article will illustrate how to place the DropDownList control inside the GridView Header and then filter the GridView records using DropDownList in ASP.Net using C# and VB.Net.
Database
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
HTML Markup
The HTML Markup consists of an ASP.Net GridView control. A DropDownList has been placed inside the HeaderTemplate of the GridView control.
The DropDownList has been assigned a SelectedIndexChanged event handler and the AutoPostBack property is set to True.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true"
PageSize="10" Font-Names="Arial" Font-Size="11pt" AlternatingRowStyle-BackColor="#C2D69B"
HeaderStyle-BackColor="green" OnPageIndexChanging="OnPaging">
<Columns>
<asp:BoundField DataField="ContactName" HeaderText="Contact Name" />
<asp:BoundField DataField="City" HeaderText="City" />
<asp:TemplateField>
<HeaderTemplate>
Country:
<asp:DropDownList ID="ddlCountry" runat="server" OnSelectedIndexChanged="CountryChanged"
AutoPostBack="true" AppendDataBoundItems="true">
<asp:ListItem Text="ALL" Value="ALL"></asp:ListItem>
<asp:ListItem Text="Top 10" Value="10"></asp:ListItem>
</asp:DropDownList>
</HeaderTemplate>
<ItemTemplate>
<%# Eval("Country") %>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="PostalCode" HeaderText="Postal Code" />
</Columns>
</asp:GridView>
Stored Procedure
The following Stored Procedure is used to filter the GridView records. The selected value of the DropDownList will be passed as parameter to the Stored Procedure.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE spx_GetCustomers
@Filter VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
IF @Filter = 'ALL'
SELECT ContactName, City, Country, PostalCode
FROM Customers
ELSE IF @Filter = '10'
SELECT TOP 10 ContactName, City, Country, PostalCode
FROM Customers
ELSE
SELECT ContactName, City, Country, PostalCode
FROM Customers WHERE Country=@Filter
END
GO
Populating the GridView and DropDownList
Populating the GridView
Inside the Page Load event, the BindGrid method is called. Inside the BindGrid method, the Stored Procedure is called and the value of the Filter parameter is passed ALL, in order to load all records.
Populating the DropDownList
The DropDownList is populated using the BindCountryList method. Inside the BindCountryList method, the DropDownList inside the HeaderTemplate of GridView is populated with distinct List of Countries from the Customers Table.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ViewState["Filter"] = "ALL";
BindGrid();
}
}
private void BindGrid()
{
DataTable dt = new DataTable();
String strConnString = System.Configuration.ConfigurationManager
.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("spx_GetCustomers");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Filter", ViewState["Filter"].ToString());
cmd.Connection = con;
sda.SelectCommand = cmd;
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
DropDownList ddlCountry =
(DropDownList)GridView1.HeaderRow.FindControl("ddlCountry");
this.BindCountryList(ddlCountry);
}
private void BindCountryList(DropDownList ddlCountry)
{
String strConnString = System.Configuration.ConfigurationManager
.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("select distinct Country" +
" from customers");
cmd.Connection = con;
con.Open();
ddlCountry.DataSource = cmd.ExecuteReader();
ddlCountry.DataTextField = "Country";
ddlCountry.DataValueField = "Country";
ddlCountry.DataBind();
con.Close();
ddlCountry.Items.FindByValue(ViewState["Filter"].ToString())
.Selected = true;
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
ViewState("Filter") = "ALL"
BindGrid()
End If
End Sub
Private Sub BindGrid()
Dim dt As New DataTable()
Dim strConnString As String = System.Configuration.ConfigurationManager _
.ConnectionStrings("conString").ConnectionString
Dim con As New SqlConnection(strConnString)
Dim sda As New SqlDataAdapter()
Dim cmd As New SqlCommand("spx_GetCustomers")
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Filter", ViewState("Filter"))
cmd.Connection = con
sda.SelectCommand = cmd
sda.Fill(dt)
GridView1.DataSource = dt
GridView1.DataBind()
Dim ddlCountry As DropDownList = DirectCast(GridView1.HeaderRow _
.FindControl("ddlCountry"), DropDownList)
Me.BindCountryList(ddlCountry)
End Sub
Private Sub BindCountryList(ByVal ddlCountry As DropDownList)
Dim strConnString As String = System.Configuration.ConfigurationManager _
.ConnectionStrings("conString").ConnectionString
Dim con As New SqlConnection(strConnString)
Dim sda As New SqlDataAdapter()
Dim cmd As New SqlCommand("select distinct Country" & _
" from customers")
cmd.Connection = con
con.Open()
ddlCountry.DataSource = cmd.ExecuteReader()
ddlCountry.DataTextField = "Country"
ddlCountry.DataValueField = "Country"
ddlCountry.DataBind()
con.Close()
ddlCountry.Items.FindByValue(ViewState("Filter").ToString()) _
.Selected = True
End Sub
AutoFilter Functionality
Inside the DropDownList’s SelectedIndexChanged event handler, the GridView records are filtered based on the selected value of the DropDownList by setting the ViewState variable value with the selected value of the DropDownList.
C#
protected void CountryChanged(object sender, EventArgs e)
{
DropDownList ddlCountry = (DropDownList)sender;
ViewState["Filter"] = ddlCountry.SelectedValue;
this.BindGrid();
}
VB.Net
Protected Sub CountryChanged(ByVal sender As Object, ByVal e As EventArgs)
Dim ddlCountry As DropDownList = DirectCast(sender, DropDownList)
ViewState("Filter") = ddlCountry.SelectedValue
Me.BindGrid()
End Sub
Handling the GridView Paging
Inside the OnPageIndexChanging event handler of the GridView, the PageIndex property of the GridView is updated and the BindGrid method is called.
C#
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
this.BindGrid();
}
VB.Net
Protected Sub OnPaging(sender As Object, e As GridViewPageEventArgs)
GridView1.PageIndex = e.NewPageIndex
Me.BindGrid()
End Sub
Screenshot
![Excel like AutoFilter Feature in ASP.Net GridView Control]()
Demo
Downloads
