In this article I will explain with an example, how to filter data in GridView control with DropDownList 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.
 
 

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.
 
The GridView has been assigned with following 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>
 
 

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.
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
 
 

Namespaces

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

Populating the GridView and DropDownList

Inside the Page_Load event handler, ViewState is set and the BindGrid method is called.
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.
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 spName = "spx_GetCustomers";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(spName, con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Filter", ViewState["Filter"].ToString());
 
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    gvCustomers.DataSource = dt;
                    gvCustomers.DataBind();
                    DropDownList ddlCountry = (DropDownList)gvCustomers.HeaderRow.FindControl("ddlCountry");
                    this.BindCountryList(ddlCountry);
                }
            }
        }
    }
}
 
private void BindCountryList(DropDownList ddlCountry)
{
    string sql = "SELECT DISTINCT Country FROM Customers";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(sql, 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 spName As String = "spx_GetCustomers"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(spName, 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.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 sql As String = "SELECT DISTINCT Country FROM Customers"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(sql, 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
 
 

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 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
 
 

Handling the GridView 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

Filter ASP.Net GridView using DropDownList
 
 

Demo

 
 

Downloads