Click here to Skip to main content
Click here to Skip to main content

Multi Column Filter GridView With Effective Paging (Searchable GridView)

, 25 Apr 2014
Rate this:
Please Sign up or sign in to vote.
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.

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.

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:

public bool ShowEmptyFooter 

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

public int TotalSearchRecords 

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

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.

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.

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

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.
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:

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:

       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:

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

License

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

About the Author

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

Comments and Discussions

 
QuestionMulti Column Filter GridView With Effective Paging PinmemberPraveena18201117-Jul-14 2:51 
AnswerRe: Multi Column Filter GridView With Effective Paging Pinprofessionalsukeshchand17-Jul-14 21:28 
Questionerror while converting c# to vb.net PinmemberMember 437506615-Jul-14 3:18 
AnswerRe: error while converting c# to vb.net PinmemberMember 437506617-Jul-14 19:46 
GeneralRe: error while converting c# to vb.net Pinprofessionalsukeshchand17-Jul-14 21:27 
QuestionAdd button link to your program Pinmemberrosy845-Jul-14 4:45 
AnswerRe: Add button link to your program Pinprofessionalsukeshchand5-Jul-14 7:51 
GeneralRe: Add button link to your program Pinmemberrosy845-Jul-14 22:29 
GeneralRe: Add button link to your program Pinprofessionalsukeshchand6-Jul-14 20:16 
QuestionNeed help for database.... PinmemberMember 43750661-Jul-14 20:03 
AnswerRe: Need help for database.... Pinprofessionalsukeshchand1-Jul-14 22:49 
GeneralMy vote of 5 PinpremiumVolynsky Alex25-Apr-14 23:36 
GeneralMy vote of 5 PinpremiumAnurag Gandhi25-Apr-14 19:15 
QuestionAdding columns PinmemberMember 1075541023-Apr-14 4:50 
AnswerRe: Adding columns Pinprofessionalsukeshchand23-Apr-14 8:02 
GeneralRe: Adding columns PinmemberMember 1075541024-Apr-14 20:13 
GeneralRe: Adding columns Pinprofessionalsukeshchand24-Apr-14 20:25 
GeneralRe: Adding columns PinmemberMember 1075541024-Apr-14 20:45 
GeneralRe: Adding columns Pinprofessionalsukeshchand25-Apr-14 2:21 
GeneralRe: Adding columns PinmemberMember 1075541027-Apr-14 18:53 
GeneralRe: Adding columns Pinprofessionalsukeshchand27-Apr-14 19:23 
GeneralRe: Adding columns PinmemberMember 1075541030-Apr-14 0:06 
GeneralRe: Adding columns Pinprofessionalsukeshchand2-May-14 4:37 
QuestionQuery PinmemberMember 1075541017-Apr-14 17:30 
AnswerRe: Query Pinprofessionalsukeshchand17-Apr-14 20:56 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 25 Apr 2014
Article Copyright 2014 by sukeshchand
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid