Click here to Skip to main content
15,867,308 members
Articles / Web Development / ASP.NET

Multi Column Filter GridView With Effective Paging (Searchable GridView)

Rate me:
Please Sign up or sign in to vote.
4.75/5 (29 votes)
25 Apr 2014CPOL3 min read 115.3K   5K   38   52
ASP.NET Custom GridView with multi column filter functionality
 

Download SearchableGridView.zip

Introduction

Here I have extended the ASP.NET Grid View by adding build in filter and paging functionality with effective searching. It will load only required data for current page from database (like 10 records per page at a time) and when the user navigates into next page, it will load only required next page data. The grid will dynamically create text boxes in run time for filter functionality.

Image 1

Image 2

Background

I was using the same kind of code in my ASP.NET pages for implementing search/filter functionalities. It was working fine, but its kills lots of my time for rewriting the code again and again and aligning the text boxes and other related controls was a big issue. So I thought of creating a reusable control which will do all of the work by itself. I have tested the grid view only with SQL Server 2008. I think anybody can change the syntax in the stored procedure and connection will be able to use with other databases.

Using the Code

I have extended System.Web.UI.WebControls.BoundField class and added property called SearchExpression for holding the search expression which will be passed directly to SQL Stored procedure.

C#
public class SearchBoundField : System.Web.UI.WebControls.BoundField
    {
        private const string SEARCH_EXPRESSION = "SearchExpression";
        public string SearchExpression
        {
            get
            {
                if (this.ViewState[SEARCH_EXPRESSION] == null)
                {
                    this.ViewState[SEARCH_EXPRESSION] = this.DataField;
                }
 
                return (string)this.ViewState[SEARCH_EXPRESSION];
            }
            set
            {
                this.ViewState[SEARCH_EXPRESSION] = value;
            }
        }
    } 

The control will create text boxes at the top of each column of the grid view and at the last column the control will create few additional buttons like Filter, Cancel Filter. When the GridView.DataBound() is called, it will create rows with a footer row which will have the navigation control box and record status label.

In the footer row with navigation controls, we can see the total number of records, current page number and total page count. This is referring to the data source which will have column TotalRows.

We can navigate to the next, previous, last, first and with a specified page by clicking the navigation buttons.

The following are the properties which are used:

ASP.NET
public bool ShowEmptyFooter 

ShowEmptyFooter: To hide/show Empty footer when no records are present.

C#
public int TotalSearchRecords 

TotalSearchRecords will hold the total number of records that exist for the current search.

C#
public int TotalSearchPages 

TotalSearchPages holds the total number of pages that exist for the current search. Normally, this will calculate by using the TotalSearchRecords and PageSize.

C#
public int? CurrentSearchPageNo 

CurrentSearchPageNo will hold the current page number. This will change when the user navigates through pages. When the user clicks on page change buttons, the data will fetch from the database and rebuild the grid.

C#
public bool SelectableDataRow 

This is an important property which is used to specify whether the grid is able to accepts row click events or not. If it is true, then while building the grid, the following code will be added in the OnRowDataBound

C#
protected override void OnRowDataBound(GridViewRowEventArgs e)
{
    base.OnRowDataBound(e);
    if (SelectableDataRow == true)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Attributes.Add("onmouseover", "this.style.backgroundColor='#ceedfc'");
            e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=''");
            e.Row.Attributes.Add("style", "cursor:pointer;");
            e.Row.Attributes.Add("onclick", Page.ClientScript.
        GetPostBackClientHyperlink(this, "Select$" + e.Row.RowIndex));
        }
    }
    //Initilize search filter data
    InitSearchFilterData();
}
public DataTable SearchFilters 

The above property is used to store SearchFilters DataTable which will be created when each search call happens, a data table will be created with the SearchString and Value and this will be passed to the SQL Stored procedure, and the stored procedure will parse the Datatable and build dynamic SQL Query and execute it.

C#
public string CurrentSortExpression
public string CurrentSortDirection  

The above two properties will be used to store the Current Sort Expression and sort direction which is used to pass to the SQL procedure for building the query. The value of the property will change when the user clicks on the column header.

When the user clicks on filter button, the FilterButtonClick event will occur and we have to handle the event in out front end like the following:

C#
public void sgvSearchClientMaster_FilterButtonClick(object sender, SearchGridEventArgs e)
        {
            FilterToNthPage(e.SearchFilterValues, 
        sgvSearchClientMaster.CurrentSortExpression,
        sgvSearchClientMaster.CurrentSortDirection, 1);
        } 

When the user clicks any one of the navigation buttons, the NavigationButtonClick event will occur and we have to handle it like the following code:

C#
       public void sgvSearchClientMaster_NavigationButtonClick(object sender, NavigationButtonEventArgs e)
{
    if (e.NavigationButtonsType == NavigationButtonsTypes.GoFirst)
    {
        FilterToNthPage(sgvSearchClientMaster.SearchFilters, 
            sgvSearchClientMaster.CurrentSortExpression, 
            sgvSearchClientMaster.CurrentSortDirection, 1);
    }
    else if (e.NavigationButtonsType == NavigationButtonsTypes.GoLast)
    {
        FilterToNthPage(sgvSearchClientMaster.SearchFilters,
            sgvSearchClientMaster.CurrentSortExpression,
            sgvSearchClientMaster.CurrentSortDirection,
            sgvSearchClientMaster.TotalSearchPages);
    }
    else if (e.NavigationButtonsType == NavigationButtonsTypes.GoNext)
    {
        if (sgvSearchClientMaster.CurrentSearchPageNo < sgvSearchClientMaster.TotalSearchPages)
        {
            FilterToNthPage(sgvSearchClientMaster.SearchFilters,
                sgvSearchClientMaster.CurrentSortExpression,
                sgvSearchClientMaster.CurrentSortDirection,
                (int)sgvSearchClientMaster.CurrentSearchPageNo + 1);
        }
    }
    else if (e.NavigationButtonsType == NavigationButtonsTypes.GoPrevious)
    {
        if (sgvSearchClientMaster.CurrentSearchPageNo > 1)
        {
            FilterToNthPage(sgvSearchClientMaster.SearchFilters,
                sgvSearchClientMaster.CurrentSortExpression, 
                sgvSearchClientMaster.CurrentSortDirection, 
                (int)sgvSearchClientMaster.CurrentSearchPageNo - 1);
        }
    }
    else if (e.NavigationButtonsType == NavigationButtonsTypes.GoToPage)
    {
        FilterToNthPage(sgvSearchClientMaster.SearchFilters,
            sgvSearchClientMaster.CurrentSortExpression, 
            sgvSearchClientMaster.CurrentSortDirection, 
            (int)e.PageIndex);
    }
} 

We can pass the DataTable to SQL Server by using the following code:

C#
SqlParameter tvpParam = cmd.Parameters.AddWithValue(SQLTableVariableName, SearchFilterValues);
tvpParam.SqlDbType = SqlDbType.Structured;  

Added git repository, You can clone and controbute https://bitbucket.org/sukeshchand/searchable-grid-view

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead Soft To Rule Solutions
India India
Working as a Freelance application software developer.

Comments and Discussions

 
QuestionHow to add Where statement in procedure stored procedure stp_ClientMaster_SearchForPagedData Pin
Member 1207919312-Aug-18 19:42
Member 1207919312-Aug-18 19:42 
Questionsukeshchand Pin
Member 1391549027-Jul-18 12:32
Member 1391549027-Jul-18 12:32 
Questionhow??? Pin
Vera Tutova26-Mar-18 6:14
Vera Tutova26-Mar-18 6:14 
QuestionClarification for Oracle Table - Filter Pin
Member 117127833-May-16 1:17
Member 117127833-May-16 1:17 
AnswerRe: Clarification for Oracle Table - Filter Pin
sukeshchand15-Jul-16 3:42
professionalsukeshchand15-Jul-16 3:42 
Questionthe download link is not working Pin
Member 1029044010-Nov-15 10:07
Member 1029044010-Nov-15 10:07 
AnswerRe: the download link is not working Pin
sukeshchand15-Jul-16 3:45
professionalsukeshchand15-Jul-16 3:45 
QuestionSearch gridview with textbox keypress Pin
Member 783763810-Dec-14 20:38
Member 783763810-Dec-14 20:38 
QuestionNumber Column Pin
Member 1101872910-Sep-14 2:01
Member 1101872910-Sep-14 2:01 
AnswerRe: Number Column Pin
sukeshchand11-Sep-14 18:17
professionalsukeshchand11-Sep-14 18:17 
AnswerRe: Number Column Pin
sukeshchand11-Sep-14 18:17
professionalsukeshchand11-Sep-14 18:17 
QuestionMy vote of 5, but ... Pin
Claire Streb25-Aug-14 12:02
Claire Streb25-Aug-14 12:02 
AnswerRe: My vote of 5, but ... Pin
sukeshchand26-Aug-14 19:12
professionalsukeshchand26-Aug-14 19:12 
QuestionMulti Column Filter GridView With Effective Paging Pin
Praveena18201117-Jul-14 2:51
Praveena18201117-Jul-14 2:51 
AnswerRe: Multi Column Filter GridView With Effective Paging Pin
sukeshchand17-Jul-14 21:28
professionalsukeshchand17-Jul-14 21:28 
Questionerror while converting c# to vb.net Pin
Member 437506615-Jul-14 3:18
Member 437506615-Jul-14 3:18 
AnswerRe: error while converting c# to vb.net Pin
Member 437506617-Jul-14 19:46
Member 437506617-Jul-14 19:46 
GeneralRe: error while converting c# to vb.net Pin
sukeshchand17-Jul-14 21:27
professionalsukeshchand17-Jul-14 21:27 
QuestionAdd button link to your program Pin
rosy845-Jul-14 4:45
rosy845-Jul-14 4:45 
AnswerRe: Add button link to your program Pin
sukeshchand5-Jul-14 7:51
professionalsukeshchand5-Jul-14 7:51 
GeneralRe: Add button link to your program Pin
rosy845-Jul-14 22:29
rosy845-Jul-14 22:29 
GeneralRe: Add button link to your program Pin
sukeshchand6-Jul-14 20:16
professionalsukeshchand6-Jul-14 20:16 
QuestionNeed help for database.... Pin
Member 43750661-Jul-14 20:03
Member 43750661-Jul-14 20:03 
AnswerRe: Need help for database.... Pin
sukeshchand1-Jul-14 22:49
professionalsukeshchand1-Jul-14 22:49 
GeneralMy vote of 5 Pin
Volynsky Alex25-Apr-14 23:36
professionalVolynsky Alex25-Apr-14 23:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.