Add multiple filter in ASP.Net GridView header using C# and VB.Net

Hi kankon,

Please refer below sample.

Procedure

CREATE PROCEDURE spx_GetCustomers1
      @Filter VARCHAR(50) = NULL
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 OR ContactName=@Filter OR @Filter IS NULL
END

HTML

<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:TemplateField>
            <HeaderTemplate>
                ContactName:
                <asp:DropDownList ID="ddlContactName" runat="server" OnSelectedIndexChanged="ContactNameChange" 
                    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>
                <asp:Label ID="lblContactName" runat="server" Text='<%# Eval("ContactName") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="City" HeaderText="City" />
        <asp:TemplateField>
            <HeaderTemplate>
                Country:
                <asp:DropDownList ID="MaxCountry" 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>
                <asp:Label ID="lblCountry" runat="server" Text='<%# Eval("Country") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="PostalCode" HeaderText="Postal Code" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        ViewState["Filter"] = "ALL";
        this.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_GetCustomers1");
    cmd.CommandType = CommandType.StoredProcedure;
    if (ViewState["Filter"] != null)
    {
        cmd.Parameters.AddWithValue("@Filter", ViewState["Filter"].ToString());
    }
    cmd.Connection = con;
    sda.SelectCommand = cmd;
    sda.Fill(dt);
    GridView1.DataSource = dt;
    GridView1.DataBind();
    DropDownList MaxCountry = (DropDownList)GridView1.HeaderRow.FindControl("MaxCountry");
    DropDownList ddlContactName = (DropDownList)GridView1.HeaderRow.FindControl("ddlContactName");
    this.BindCountryList(MaxCountry);
    this.BindContactList(ddlContactName);
}

private void BindCountryList(DropDownList MaxCountry)
{
    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",con);
    con.Open();
    MaxCountry.DataSource = cmd.ExecuteReader();
    MaxCountry.DataTextField = "Country";
    MaxCountry.DataValueField = "Country";
    MaxCountry.DataBind();
    con.Close();
    if (MaxCountry.Items.FindByValue(ViewState["Filter"].ToString()) != null)
    {
        MaxCountry.Items.FindByValue(ViewState["Filter"].ToString()).Selected = true;
    }
}

private void BindContactList(DropDownList ddlContactName)
{
    string strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(strConnString);
    SqlDataAdapter sda = new SqlDataAdapter();
    SqlCommand cmd = new SqlCommand("select distinct ContactName from customers", con);
    con.Open();
    ddlContactName.DataSource = cmd.ExecuteReader();
    ddlContactName.DataTextField = "ContactName";
    ddlContactName.DataValueField = "ContactName";
    ddlContactName.DataBind();
    con.Close();
    if (ddlContactName.Items.FindByValue(ViewState["Filter"].ToString()) != null)
    {
        ddlContactName.Items.FindByValue(ViewState["Filter"].ToString()).Selected = true;
    }
}
protected void CountryChanged(object sender, EventArgs e)
{
    DropDownList MaxCountry = (DropDownList)sender;
    ViewState["Filter"] = MaxCountry.SelectedValue;
    this.BindGrid();
}

protected void ContactNameChange(object sender, EventArgs e)
{
    DropDownList ddlContactName = (DropDownList)sender;
    ViewState["Filter"] = ddlContactName.SelectedValue;
    this.BindGrid();
}
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    this.BindGrid();
}

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 dt As DataTable = New DataTable()
    Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Dim con As SqlConnection = New SqlConnection(strConnString)
    Dim sda As SqlDataAdapter = New SqlDataAdapter()
    Dim cmd As SqlCommand = New SqlCommand("spx_GetCustomers1")
    cmd.CommandType = CommandType.StoredProcedure

    If ViewState("Filter") IsNot Nothing Then
        cmd.Parameters.AddWithValue("@Filter", ViewState("Filter").ToString())
    End If

    cmd.Connection = con
    sda.SelectCommand = cmd
    sda.Fill(dt)
    GridView1.DataSource = dt
    GridView1.DataBind()
    Dim MaxCountry As DropDownList = CType(GridView1.HeaderRow.FindControl("MaxCountry"), DropDownList)
    Dim ddlContactName As DropDownList = CType(GridView1.HeaderRow.FindControl("ddlContactName"), DropDownList)
    Me.BindCountryList(MaxCountry)
    Me.BindContactList(ddlContactName)
End Sub

Private Sub BindCountryList(ByVal MaxCountry As DropDownList)
    Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Dim con As SqlConnection = New SqlConnection(strConnString)
    Dim sda As SqlDataAdapter = New SqlDataAdapter()
    Dim cmd As SqlCommand = New SqlCommand("select distinct Country from customers", con)
    con.Open()
    MaxCountry.DataSource = cmd.ExecuteReader()
    MaxCountry.DataTextField = "Country"
    MaxCountry.DataValueField = "Country"
    MaxCountry.DataBind()
    con.Close()

    If MaxCountry.Items.FindByValue(ViewState("Filter").ToString()) IsNot Nothing Then
        MaxCountry.Items.FindByValue(ViewState("Filter").ToString()).Selected = True
    End If
End Sub

Private Sub BindContactList(ByVal ddlContactName As DropDownList)
    Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Dim con As SqlConnection = New SqlConnection(strConnString)
    Dim sda As SqlDataAdapter = New SqlDataAdapter()
    Dim cmd As SqlCommand = New SqlCommand("select distinct ContactName from customers", con)
    con.Open()
    ddlContactName.DataSource = cmd.ExecuteReader()
    ddlContactName.DataTextField = "ContactName"
    ddlContactName.DataValueField = "ContactName"
    ddlContactName.DataBind()
    con.Close()

    If ddlContactName.Items.FindByValue(ViewState("Filter").ToString()) IsNot Nothing Then
        ddlContactName.Items.FindByValue(ViewState("Filter").ToString()).Selected = True
    End If
End Sub

Protected Sub CountryChanged(ByVal sender As Object, ByVal e As EventArgs)
    Dim MaxCountry As DropDownList = CType(sender, DropDownList)
    ViewState("Filter") = MaxCountry.SelectedValue
    Me.BindGrid()
End Sub

Protected Sub ContactNameChange(ByVal sender As Object, ByVal e As EventArgs)
    Dim ddlContactName As DropDownList = CType(sender, DropDownList)
    ViewState("Filter") = ddlContactName.SelectedValue
    Me.BindGrid()
End Sub

Protected Sub OnPaging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
    GridView1.PageIndex = e.NewPageIndex
    Me.BindGrid()
End Sub

Screenshot