Excel like Auto-filter feature in gridview control in asp.net

Description:-

In this article we will bind gridview control from database and set dropdownlist control for filter operation in header from country column based on dropdown control text selection bind gridview.

like for example if i have selected top 5 then we have to bind only top 5 rows from database, if  i have select particular country then only for display that country data only.

i have created Storeprocedure for geting data from database and bind into gridview. after selection we have to bind country columns value in dropdownlist control because of if dynamically if we have lots of data and it's inserting from other sites or any other source then for each selection we have to bind it. 

Create Table:-

CREATE TABLE [dbo].[Customers](
 [ContactName] [varchar](20) NOT NULL,
 [City] [varchar](20) NOT NULL,
 [PostalCode] [int] NOT NULL,
 [Country] [varchar](20) NOT NULL,
 [Phone] [bigint] NOT NULL,
 [Fax] [bigint] NOT NULL,
 [CustomerId] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
 [CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Create Store Procedure:-

Create procedure [dbo].[spx_GetCustomers]
@Filter varchar(20)
as 
begin
if @Filter='ALL'
 select * from Customers 
else if @Filter='Top 5'
 select top 5* from Customers 
else
 select * from Customers where Country=@Filter
end

Web.Config:-

<connectionStrings>
    <add connectionString="ConnectionString" name="DBCS" providerName="System.Data.SqlClient"/>
</connectionStrings>

Default.aspx:-

    <div>
        <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 5" Value="5"></asp:ListItem>
                        </asp:DropDownList>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <%# Eval("Country") %>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </div>

Default.aspx.cs:-

        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["DBCS"].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["DBCS"].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.FindByText(ViewState["Filter"].ToString()).Selected = true;
}

protected void CountryChanged(object sender, EventArgs e)
{
    DropDownList ddlCountry = (DropDownList)sender;
    ViewState["Filter"] = ddlCountry.SelectedItem.Text;
    this.BindGrid();
}

protected void OnPaging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    this.BindGrid();
}

Author : Umesh Patel

Related Posts