.Net Funda: Filter ASP.Net GridView using DropDownList

Introduction

In this article I will explain how to change the appearance of gridview and how to filter gridview records with dropdownlist selection using asp.net.

Here I am explaining how to build a feature similar to Microsoft’s Excel AutoFilter in ASP.Net GridView control. Excel AutoFilter allows user to filter the records using the DropDownList in ASP.Net.

There are three steps to insert, update,delete data into database.

Create table in database.
Design .aspx page.
Write Code on .aspx.CS page.

Step 1. Create table in Database

Table Name: - Student

Query for creating a table

Step 2. Create/design .aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>Search Record :</td>
<td><asp:DropDownList ID="DropDownList1" runat="server" Height="30" Width="100">
<asp:ListItem>All</asp:ListItem>
<asp:ListItem>Mca</asp:ListItem>
<asp:ListItem>Mba</asp:ListItem>
<asp:ListItem>Bsc</asp:ListItem>
</asp:DropDownList></td>
<td>   <asp:Button ID="btnsearch" runat="server" Text="Search" Height="30" BackColor="#FF6600" Font-Bold="True" ForeColor="White" BorderStyle="None" OnClick="btnsearch_Click"/></td>
</tr>
</table><br />
Total Record : <asp:Label ID="Label1" runat="server" Text=""></asp:Label><br /><br />
<asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3" AllowPaging="True" OnPageIndexChanging="GridView1_PageIndexChanging">
<FooterStyle BackColor="White" ForeColor="#000066" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<RowStyle ForeColor="#000066" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#007DBB" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#00547E" />
</asp:GridView>
</div>
</form>
</body>
</html>

Screen-shot

Step 3. C# Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ConnectionString);

protected void Page_Load(object sender, EventArgs e)
{
Bindgrid();
}
public void Bindgrid()
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter("select * from student",con);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
Label1.Text = ds.Tables[0].Rows.Count.ToString();
con.Close();
}
protected void btnsearch_Click(object sender, EventArgs e)
{
if (DropDownList1.Text == "All")
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter("select * from student", con);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
Label1.Text = ds.Tables[0].Rows.Count.ToString();
con.Close();
}
else if (DropDownList1.Text == "Mca")
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter("select * from student where cource='"+DropDownList1.Text+"'", con);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
Label1.Text = ds.Tables[0].Rows.Count.ToString();
con.Close();
}
else if (DropDownList1.Text == "Mba")
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter("select * from student where cource='" + DropDownList1.Text + "'", con);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
Label1.Text = ds.Tables[0].Rows.Count.ToString();
con.Close();
}
else if (DropDownList1.Text == "Bsc")
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter("select * from student where cource='" + DropDownList1.Text + "'", con);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
Label1.Text = ds.Tables[0].Rows.Count.ToString();
con.Close();
}

}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
Bindgrid();
}
}

Explanation:

You are advised to change your SqlConnection string where web.config file

Output:

Thank You...

.Net Funda

Sunday, January 22, 2017

Filter ASP.Net GridView using DropDownList