Filter ASP.Net GridView using DropDownList

GridView Markup

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

In this tutorial I’ll be explaining the AutoFilter feature using the Country Field of the Customers table hence you’ll notice I have used a Template field for the Country column in GridView with a DropDownList in the header template which will be used to Filter the records

Database

For this tutorial I am using Microsoft’s NorthWind Database. You can download it using the link below

Stored Procedure

I’ll be using stored procedure to get the Customer records from the Customers table of NorthWind Database.

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

You will notice above the stored procedure accepts a parameter called @Filter. Based on the value of this parameter the stored procedure filters the records

Binding the GridView

C#

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

}

VB.Net

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

You will notice above, I am calling the same store procedure I created earlier and passing the Filter criteria as parameter to it. Also I am calling a method BindCountryList that will be used to populate my AutoFilter Country DropDownList

Populating AutoFilter DropDownList

The following method is used to populate the AutoFilter DropDownList which present in the Header Template of the ASP.Net GridView control

C#

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

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

The above method simply fires a query on the Customers table to get distinct country names present in the table

AutoFilter Functionality

The AutoFilter functionality is manages by the Country DropDownList’s OnSelectedIndexChanged event.

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

Here I am simply setting the filter value from the DropDownList’s selected item’s value into the ViewState variable Thus when the DropDownList’s selected value is changed this event is fired and the GridView is rebind with filtered records

Page Load Event

Here is the page load event of the ASP.net Web Page it is simply used to bind the Grid when the page loads for first time

C#

protected void Page_Load(object sender, EventArgs e)

{

    if (!IsPostBack)

    {

        ViewState["Filter"] = "ALL";

        BindGrid();

    }

}

VB.Net

Protected Sub OnPaging(sender As Object , e As GridViewPageEventArgs)

    GridView1.PageIndex = e.NewPageIndex

    Me.BindGrid()

End Sub

PageIndexChaging event

And here is the OnPageIndexChaging Event

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

The animated screenshot below describes how the AutoFilter feature works in ASP.Net GridView