Multi Column Filter GridView With Effective Paging (Searchable GridView)

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;  

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