Click here to Skip to main content
15,867,771 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

 
AnswerRe: Query Pin
sukeshchand17-Apr-14 20:56
professionalsukeshchand17-Apr-14 20:56 
QuestionCancel option Pin
Member 1075541016-Apr-14 21:29
Member 1075541016-Apr-14 21:29 
AnswerRe: Cancel option Pin
sukeshchand17-Apr-14 0:56
professionalsukeshchand17-Apr-14 0:56 
GeneralRe: Cancel option Pin
Member 1075541018-Apr-14 22:13
Member 1075541018-Apr-14 22:13 
QuestionCalendar control Pin
Prakash Sekhar16-Apr-14 17:37
Prakash Sekhar16-Apr-14 17:37 
AnswerRe: Calendar control Pin
sukeshchand16-Apr-14 19:51
professionalsukeshchand16-Apr-14 19:51 
QuestionFiltering not working Pin
madhavicsawant13-Apr-14 23:07
madhavicsawant13-Apr-14 23:07 
AnswerRe: Filtering not working Pin
sukeshchand13-Apr-14 23:14
professionalsukeshchand13-Apr-14 23:14 
In the filter you should add values like the following

si* : this will filter all the values starts with si
s* : this will filter all the values starts with s

Please download the attached sample project and go through it...

Thanks,

sukesh chand
GeneralRe: Filtering not working Pin
madhavicsawant13-Apr-14 23:37
madhavicsawant13-Apr-14 23:37 
GeneralMy vote of 5 Pin
Renju Vinod19-Mar-14 20:01
professionalRenju Vinod19-Mar-14 20:01 
GeneralRe: My vote of 5 Pin
sukeshchand20-Mar-14 21:20
professionalsukeshchand20-Mar-14 21:20 
Questionplease send a demo/sample of your project Pin
azeran9-Feb-14 20:52
azeran9-Feb-14 20:52 
AnswerRe: please send a demo/sample of your project Pin
sukeshchand11-Feb-14 2:01
professionalsukeshchand11-Feb-14 2:01 
QuestionRe: please send a demo/sample of your project Pin
azeran11-Feb-14 22:57
azeran11-Feb-14 22:57 
AnswerRe: please send a demo/sample of your project Pin
sukeshchand12-Feb-14 1:16
professionalsukeshchand12-Feb-14 1:16 

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.