In this article I will explain with an example, how to build Excel like AutoFilter feature in ASP.Net GridView control in ASP.Net using C# and VB.Net.
 
 

Database

Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Download and install Northwind Database
 
 

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
 
 

HTML Markup

The following HTML Markup consists of:
GridView – For displaying data.

Columns

The GridView consists of three BoundField columns and a TemplateField column.
 

TemplateField

The TemplateField column consists of a HeaderTemplate and an ItemTemplate.
 

HeaderTemplate

The HeaderTemplate column consists of a DropDownList with two ListItiems.
The DropDownList has been assigned with an OnSelectedIndexChanged event handler.
 

Properties

PageSize – For permitting maximum number of rows to be displayed per page.
AllowPaging – For enabling paging in the GridView control.
 

Events

The GridView has been assigned with an OnPageIndexChanging event handler.
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" AllowPaging="true" PageSize="10"
    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="OnCountryChanged"
                    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>
 
 

Namespaces

You will need to import the following namespaces.
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
 
VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
 
 

Populating the GridView and DropDownList

Populating the GridView

Inside the Page Load event handler, ViewState is set and the BindGrid method is called.
 

BindGrid

Inside the BindGrid method, the Stored Procedure is called and ViewState value is passed as parameter and the GridView is populated with records.
Then, the BindCountryList method is called which accepts the parameter of the reference of the Header Row of Country column as Dynamic DropDownList.
 

Populating the DropDownList

Inside the BindCountryList method, the DISTINCT List of Country name is fetched using ExecuteReader function.
Note: For more details on ExecuteReader function, please refer my article Using SQLCommand ExecuteReader Example in ASP.Net with C# and VB.Net.
 
Finally, the DropDownList is populated with the Country names.
C#
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        ViewState["Filter"] = "ALL";
        this.BindGrid();
    }
}
 
private void BindGrid()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("spx_GetCustomers", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Filter", ViewState["Filter"].ToString());
 
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    sda.SelectCommand = cmd;
                    sda.Fill(dt);
                    gvCustomers.DataSource = dt;
                    gvCustomers.DataBind();
                    DropDownList ddlCountry = (DropDownList)gvCustomers.HeaderRow.FindControl("ddlCountry");
                    this.BindCountryList(ddlCountry);
                }
            }
        }
    }
}
 
private void BindCountryList(DropDownList ddlCountry)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "SELECT DISTINCT Country FROM Customers";
 
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query, 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 Me.IsPostBack Then
        ViewState("Filter") = "ALL"
        Me.BindGrid()
    End If
End Sub
 
Private Sub BindGrid()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("spx_GetCustomers", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@Filter", ViewState("Filter").ToString())
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Using dt As DataTable = New DataTable()
                    sda.SelectCommand = cmd
                    sda.Fill(dt)
                    gvCustomers.DataSource = dt
                    gvCustomers.DataBind()
                    Dim ddlCountry As DropDownList = CType(gvCustomers.HeaderRow.FindControl("ddlCountry"), DropDownList)
                    Me.BindCountryList(ddlCountry)
                End Using
            End Using
        End Using
    End Using
End Sub
 
Private Sub BindCountryList(ByVal ddlCountry As DropDownList)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "SELECT DISTINCT Country FROM Customers"
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query, 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 Using
    End Using
End Sub
 
 

Filtering GridView Records using DropDownList in ASP.Net

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 to the selected value of the DropDownList.
Finally, the BindGrid method is called and GridView is again populated with the filtered records.
C#
protected void OnCountryChanged(object sender, EventArgs e)
{
    DropDownList ddlCountry = (DropDownList)sender;
    ViewState["Filter"] = ddlCountry.SelectedValue;
    this.BindGrid();
}
 
VB.Net
Protected Sub OnCountryChanged(ByVal sender As Object, ByVal e As EventArgs)
    Dim ddlCountry As DropDownList = CType(sender,DropDownList)
    ViewState("Filter") = ddlCountry.SelectedValue
    Me.BindGrid()
End Sub
 
 

Paging

Inside the OnPageIndexChanging event handler, the PageIndex property of the GridView is updated with the new Page Number which was clicked.
Finally, the GridView is populated using the BindGrid method which in-turn displays the new GridView page.
C#
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
    gvCustomers.PageIndex = e.NewPageIndex;
    this.BindGrid();
}
 
VB.Net
Protected Sub OnPaging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
    gvCustomers.PageIndex = e.NewPageIndex
    Me.BindGrid()
End Sub
 
 

Screenshot

Excel like AutoFilter Feature in ASP.Net GridView Control
 
 

Demo

View Demo
 
 

Downloads

Download Code