We use filter to Search information’s easier. Recall a web-page where you have lot of records in a Grid. What your Customer wants is “They want to provide Search filter in the header row for some columns of the grid”. You can say like MS-Excel column filter. Here my app is with ASP.NET. To display data I am using a Gridview. Here in Gridview I am with 3 columns Name, ID and Status. Refer to my Customer they wants me to implement a dropdownlist with all the possible status in Status column header. Functionally, On change of dropdownlist event he wants to filter only those records which are having the selected Status.
Look at the below demo app. Here in place of database I am fetching data from a MS-Excel file. Data structure I maintained in Excel file is with 3 columns Name, ID and Status. Similar to a database table here Emp ID acts like the Primary Key. This is unique for each.
Gridview
<asp:GridView ID="grdRecords" runat="server" AutoGenerateColumns="false" AllowPaging="true" PageSize="10" Font-Names="Arial" Font-Size="11pt" AlternatingRowStyle-BackColor="#C2D69B" HeaderStyle-BackColor="green" HeaderStyle-ForeColor="#FFFFFF" OnPageIndexChanging="OnPaging" AutoGenerateEditButton="true" Width="100%">
<Columns>
<asp:TemplateField HeaderText="Employee Name" SortExpression="Name">
<ItemTemplate>
<asp:Label ID="lblEmpName" runat="server" Text='<%# Bind("Name")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Employee ID" SortExpression="ID">
<ItemTemplate>
<asp:Label ID="lblEmpID" runat="server" Text='<%# Bind("ID")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
Status:
<asp:DropDownList ID="ddlStatus" runat="server" OnSelectedIndexChanged="CountryChanged" AutoPostBack="true" AppendDataBoundItems="true">
<asp:ListItem Text = "ALL" Value = "ALL"></asp:ListItem>
</asp:DropDownList>
</HeaderTemplate>
<EditItemTemplate>
<asp:DropDownList ID="ddlRowStatus" runat="server" AutoPostBack="true">
<asp:ListItem>thisis</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<%# Eval("Status") %>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
Like the general, to bind data to the grid I am calling BindGrid() subroutine under page_load. Additionally I am with 1 more sub-routine BindCountryList() which responsible to display all possible Status from Excel File. To reset custom search in 0th index of dropdownlist I added “ALL”. By changing dropdownlist selected value to “ALL”, it is updating a ViewState variable. Which conditionally filtering using a SQL Query in BindGrid() subroutine.
VB.NET Code Behind
Imports System.IO
Imports System.Data.OleDb
Imports System.Data
Partial Class _Default
Inherits System.Web.UI.Page
Dim FilePath As String = "C:\Users\biswabhusan_panda\Documents\Visual Studio 2013\WebSites\ExcelApp\Files\temp.xlsx"
Dim Extension As String = ".xlsx"
Dim ConStr As String = ConfigurationManager.ConnectionStrings("ConnString").ConnectionString()
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
ViewState("Filter") = "ALL"
BindGrid(FilePath, Extension)
End If
End Sub
Protected Sub OnPaging(sender As Object, e As GridViewPageEventArgs)
grdRecords.PageIndex = e.NewPageIndex
Me.BindGrid(FilePath, Extension)
End Sub
Protected Sub CountryChanged(ByVal sender As Object, ByVal e As EventArgs)
Dim ddlStatus As DropDownList = DirectCast(sender, DropDownList)
ViewState("Filter") = ddlStatus.SelectedValue
Me.BindGrid(FilePath, Extension)
End Sub
Private Sub BindGrid(ByVal FilePath As String, ByVal Extension As String)
ConStr = String.Format(ConStr, FilePath)
Dim connExcel As New OleDbConnection(ConStr)
Dim cmdExcel As New OleDbCommand()
Dim oda As New OleDbDataAdapter()
Dim dtbl As New DataTable()
cmdExcel.Connection = connExcel
'Get the name of First Sheet
connExcel.Open()
Dim dtExcelSchema As DataTable
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
connExcel.Close()
'Read Data from First Sheet
connExcel.Open()
If ViewState("Filter").ToString() = "ALL" Then
cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
Else
cmdExcel.CommandText = "SELECT * From [" & SheetName & "] WHERE Status='" & ViewState("Filter").ToString() & "'"
End If
oda.SelectCommand = cmdExcel
oda.Fill(dtbl)
connExcel.Close()
'Bind Data to GridView
grdRecords.DataSource = dtbl
grdRecords.DataBind()
Dim ddlStatus As DropDownList = DirectCast(grdRecords.HeaderRow _
.FindControl("ddlStatus"), DropDownList)
Me.BindCountryList(ddlStatus, FilePath, Extension)
End Sub
Private Sub BindCountryList(ByVal ddlStatus As DropDownList, ByVal FilePath As String, ByVal Extension As String)
ConStr = String.Format(ConStr, FilePath)
Dim ConnExcel As New OleDbConnection(ConStr)
Dim CmdA As New OleDbCommand()
Dim oda As New OleDbDataAdapter()
Dim dtbl As New DataTable()
CmdA.Connection = ConnExcel
'Get the name of First Sheet
ConnExcel.Open()
Dim dtExcelSchema As DataTable
dtExcelSchema = ConnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
CmdA.CommandText = "SELECT DISTINCT Status From [" & SheetName & "]"
ddlStatus.DataSource = CmdA.ExecuteReader()
ddlStatus.DataTextField = "Status"
ddlStatus.DataValueField = "Status"
ddlStatus.DataBind()
ConnExcel.Close()
ddlStatus.Items.FindByValue(ViewState("Filter").ToString()) _
.Selected = True
End Sub
End Class
