Custom pagination and cascaded filtering with rad grid

• Download source - 61.8 KB

You need to download all necessary packages and references to build this source.

Introduction

This article explains the approach and steps involved in customizing the pagination, issues faced with dependent features, implementation of excel-like cascaded filtering with RadGrid.

Background

Recently, I was working in an assignment in which the rad grid was used to display data from a large dataset (above 320,000,000 rows) and have the excel-like cascaded filtering.
Rad grid has got very good feature set and most of these features can be availed without writing a single line of code.
It works like charm for small datasets (say 200 records), when the size of the dataset increases, the performance of the application or the rad grid will go down.
This leads us to change the default implementation of the features to deal with the larger dataset.
Unsurprisingly, rad grid supports the customization most of it's features. One possible problem is, customization of a feature may affect one or more dependent features.
It's not so hard as it looks like now, rest of the article discusses the implementation[Attached the sample project, "AdventureWorks2012" database is used for demo].

Tools/Frameworks used:

1. Visual Studio 2015
2. Targeting 4.5.2
3. Entity Framework 6.1.3
4. Telerik Version 2013.3.1114.45
5. Auto Mapper 5.1.1
6. SQL Server 2012, AdventureWorks2012

Pagination

Let's start with custom pagination, Below are the changes required in the aspx page.

1. Set AllowPaging = true, AllowCustomPaging = true --> Instructs the rad grid to not to use the built-in pagination logic.
2. Set VirtualItemCount = 100, Page size = 10 --> Set these properties dynamically in the code behind also.
3. Set event handler[CustomGrid_NeedDataSource] for OnNeedDatasource event.

<telerik:RadGrid runat="server" ID="CustomGrid"
	AllowPaging="true" AllowCustomPaging="true" PageSize="10" VirtualItemCount="1000" 
	OnItemDataBound="CustomGrid_ItemDataBound" AutoGenerateColumns="False" CellSpacing="0" 

GridLines="None">

In the code behind, add the below code to load and assign dataset to the grid inside the OnNeedDatasource event handler.

protected void CustomGrid_NeedDataSource(object sender, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
        {
            var customers = CustomerService.GetCustomerDetail(Filter, CustomGrid.CurrentPageIndex, 

CustomGrid.PageSize);
            var totalRow = customers.Count() > 0 ? customers.First().TotalRow : 0;

            CustomGrid.DataSource = customers;
            CustomGrid.VirtualItemCount = totalRow;            
        }

First two lines in the function loads the result set and total rows count.

1. CustomGrid.DataSource = customers; --> Assigns & binds the dataset to the grid, no need to invoke the DataBind() explicitly while using the OnNeedDatasource handler.
2. CustomGrid.VirtualItemCount = totalRow; --> Sets the total count of rows returned by the query, rad grid uses this value to calculate the actual pages.

Let's look at the pagination logic that resides in the stored procedure.

CREATE PROCEDURE USP_GetCustomerDetail
	@FirstName Varchar(50) = NULL,
	@LastName Varchar(50) = NULL,
	@EmailAddress Varchar(250) = NULL,
	@City Varchar(50) = NULL,
	@StateProvinceName Varchar(50) = NULL,
	@PostalCode Varchar(50) = NULL,
	@CountryRegionName Varchar(50) = NULL,

	@PageNumber Int = 1,
	@PageSize Int = 10
AS
BEGIN
	SET NOCOUNT ON;

	--Validate pagination parameters
	IF(@PageNumber IS NULL Or @PageNumber <= 0) SET @PageNumber = 1
	IF(@PageSize IS NULL Or @PageSize < 0) SET @PageSize = 10
	
	--Calculate start and end row to return
	Declare @StartRow Int = ((@PageNumber - 1) * @PageSize) + 1      
	Declare @EndRow Int = @PageNumber * @PageSize

	--Validate filter values
	IF (@FirstName = '' OR @FirstName = 'All' OR @FirstName = 'NULL') SET @FirstName = NULL;
	IF (@LastName = '' OR @LastName = 'All' OR @LastName = 'NULL') SET @LastName = NULL;
	IF (@EmailAddress = '' OR @EmailAddress = 'All' OR @EmailAddress = 'NULL') SET @EmailAddress = 

NULL;
	IF (@City = '' OR @City = 'All' OR @City = 'NULL') SET @City = NULL;
	IF (@StateProvinceName = '' OR @StateProvinceName = 'All' OR @StateProvinceName = 'NULL') SET 

@StateProvinceName = NULL;
	IF (@PostalCode = '' OR @PostalCode = 'All' OR @PostalCode = 'NULL') SET @PostalCode = NULL;
	IF (@CountryRegionName = '' OR @CountryRegionName = 'All' OR @CountryRegionName = 'NULL') 

SET @CountryRegionName = NULL;

	SELECT FirstName, LastName, EmailAddress, PhoneNumber, EmailPromotion, City, 

StateProvinceName, PostalCode, CountryRegionName, TotalRow
	FROM
	(
		SELECT FirstName, LastName, EmailAddress, PhoneNumber, EmailPromotion, City, 

StateProvinceName, PostalCode, CountryRegionName
		, ROW_NUMBER() OVER(Order By FirstName ASC, LastName ASC) RowNumber, COUNT(1) 

OVER() TotalRow
		FROM [AdventureWorks2012].[Sales].[vIndividualCustomer]
		WHERE (@FirstName IS NULL OR @FirstName = FirstName)
		And (@LastName IS NULL OR @LastName = LastName)
		And (@EmailAddress IS NULL OR @EmailAddress = EmailAddress)
		And (@City IS NULL OR @City = City)
		And (@StateProvinceName IS NULL OR @StateProvinceName = StateProvinceName)
		And (@PostalCode IS NULL OR @PostalCode = PostalCode)
		And (@CountryRegionName IS NULL OR @CountryRegionName = CountryRegionName)
	) innerTable
	WHERE RowNumber BETWEEN @StartRow And @EndRow
	ORDER BY FirstName, LastName

END
GO

Parameters in the top segment are defined to pass the currently seelcted filter values, the last two parameters are used to pass the values required for pagination.
ROW_NUMBER function is used to calculate the position of a row in the result set. Total number of the records are calculated using COUNT function.
The result set has a separate column for TotalRow, this is the work around to avoid running a separate SQL statement to get the total row count.

All done, Running the custom pagination grid would show the grid like below.

Pagination is working perfectly as expected, this approach works greatly for Asp.Net GridView also with few minor changes.

Filtering

Let's turn on the default filter feature and see how it works!

Set AllowFilteringByColumn = true in master table view, this enables the built-in filtering feature for all the columns.

Above screen shot shows the rad grid with default filtering feature, Let's discuss the issues with it!

If we try filtering by a value which is not available in the currently displayed page, grid will show no results.
This is happening as the built-in filtering logic searches the items in the current page only, not the entire table/result set.
Ideally, users would expect to apply filter on entire table, not just limited to the current page, this leads to do the custom implementation for filtering also.

Having the possible filter values pre loaded in a select list and making the filters cascaded will make the grid more usable and efficient.
Possible filter values means, the distinct values in a column from the entire result set, not limited to what is being displayed in the page.
Example: Assume a grid has two filter "state" and "city", when a user selects "NY" in "state" filter, "city" filter should have only the cities which belongs to the state "NY" regardless of the selected page number or page size.

Let's start with with the custom filtering!

Set filter template for the columns wherever the filtering is required

--Textbox filter
<telerik:GridBoundColumn HeaderText="First Name" uniqueName="colFirstName" DataField="FirstName" 

DataType="System.String">
    <FilterTemplate>
        <telerik:RadTextBox ID="filterFirstName" runat="server" Style="width: auto !important;" 
            data-filtercolumn="colFirstName" ClientEvents-OnValueChanged="textChanged">
        </telerik:RadTextBox>
    </FilterTemplate>
</telerik:GridBoundColumn>

--Select box filter
<telerik:GridBoundColumn HeaderText="City" uniqueName="colCity" DataField="City" 

DataType="System.String" AllowFiltering="true">
    <FilterTemplate>
        <telerik:RadComboBox ID="filterCity" runat="server" Style="width: auto !important;" 

AppendDataBoundItems="true"
            data-filtercolumn="colCity"  OnClientSelectedIndexChanged="filterChanged" >
            <Items>
                <telerik:RadComboBoxItem Text="All" />
            </Items>
        </telerik:RadComboBox>
    </FilterTemplate>
</telerik:GridBoundColumn>

Following are the important properties set in the above code:

1. data-filtercolumn --> this custom attribute (HTML 5 feature) is used to hold the UniqueName of the filter column.
2. OnClientSelectedIndexChanged --> Used to specify the name of the client side function to call when the filter value is changed in the select box filter.
3. ClientEvents-OnValueChanged --> Used to specify the name of the client side function to call when the filter value is changed in the textbox filter.

Call the rad grid filter function tableView.filter(uniqueName, filterValue, filterType) inside JavaScript function which triggers the post back with selected filter value.

<script type="text/javascript">
        function filterChanged(sender, args) {
            var tableView = $find('<%=CustomGrid.ClientID %>').get_masterTableView();
            var filterColumn = sender.get_element().getAttribute('data-filtercolumn');
            tableView.filter(filterColumn, args.get_item().get_value(), "EqualTo");
        }

         function textChanged(sender, args) {
            var tableView = $find('<%=CustomGrid.ClientID %>').get_masterTableView();
             var filterColumn = sender.get_element().getAttribute('data-filtercolumn');
            tableView.filter(filterColumn, sender._text, "EqualTo");
        }
    </script>

Assign a handler CustomGrid_ItemDataBound for the OnItemDataBound event which is raised whenever a row is added to the rad grid.

<telerik:RadGrid runat="server" ID="CustomGrid" AllowFilteringByColumn="true" MasterTableView-

AllowFilteringByColumn="true"
            AllowPaging="true" AllowCustomPaging="true" PageSize="10" VirtualItemCount="1000" 

GroupingSettings-CaseSensitive="false"
            OnNeedDataSource="CustomGrid_NeedDataSource" OnItemDataBound="CustomGrid_ItemDataBound"
            AutoGenerateColumns="False" CellSpacing="0" GridLines="None">

Below is the stored procedure to select the possible filter values.

CREATE PROCEDURE USP_GetCustomerDetailFilterValues
	@FirstName Varchar(50) = NULL,
	@LastName Varchar(50) = NULL,
	@EmailAddress Varchar(250) = NULL,
	@City Varchar(50) = NULL,
	@StateProvinceName Varchar(50) = NULL,
	@PostalCode Varchar(50) = NULL,
	@CountryRegionName Varchar(50) = NULL
AS
BEGIN
	SET NOCOUNT ON;

	--Validate filter values
	IF (@FirstName = '' OR @FirstName = 'All' OR @FirstName = 'NULL') SET @FirstName = NULL;
	IF (@LastName = '' OR @LastName = 'All' OR @LastName = 'NULL') SET @LastName = NULL;
	IF (@EmailAddress = '' OR @EmailAddress = 'All' OR @EmailAddress = 'NULL') SET @EmailAddress = 

NULL;
	IF (@City = '' OR @City = 'All' OR @City = 'NULL') SET @City = NULL;
	IF (@StateProvinceName = '' OR @StateProvinceName = 'All' OR @StateProvinceName = 'NULL') SET 

@StateProvinceName = NULL;
	IF (@PostalCode = '' OR @PostalCode = 'All' OR @PostalCode = 'NULL') SET @PostalCode = NULL;
	IF (@CountryRegionName = '' OR @CountryRegionName = 'All' OR @CountryRegionName = 'NULL') 

SET @CountryRegionName = NULL;
		
		SELECT Distinct City, StateProvinceName, PostalCode, CountryRegionName
		FROM [AdventureWorks2012].[Sales].[vIndividualCustomer]
		WHERE (@FirstName IS NULL OR @FirstName = FirstName)
		And (@LastName IS NULL OR @LastName = LastName)
		And (@EmailAddress IS NULL OR @EmailAddress = EmailAddress)
		And (@City IS NULL OR @City = City)
		And (@StateProvinceName IS NULL OR @StateProvinceName = StateProvinceName)
		And (@PostalCode IS NULL OR @PostalCode = PostalCode)
		And (@CountryRegionName IS NULL OR @CountryRegionName = CountryRegionName)

END
GO

In the code behind, CustomGrid_OnItemDataBound function has the code to load & bind filter values to controls inside the filter template.
Property Filter returns a filter object which has all currently selected filter values. Selecting "All" in the filter drop down will reset the filter value to null (none).

GetCustomterDetailFilter() function returns a object that has possible filter values based on the currently applied filters in the grid.
(This second database call may be expensive for few cases, If so, use DataReader to read multiple result sets in single call.
I have used this approach (two database calls) as the entity framework 6 doesn't have much support for reading multiple result and it didn't contribute to any performance issue.)

Having all the above mentioned changed completed, the code behind will look like below, pagination & filtering should work without any issues.

using System;
using System.Linq;
using System.Web.UI;
using RadGridCustomPaginationAndCustomFilter.BusinessLogic;
using RadGridCustomPaginationAndCustomFilter.Model;
using Telerik.Web.UI;

namespace RadGridCustomPaginationAndCustomFilter
{
    public partial class _Default : Page
    {
        private CustomerDetailService CustomerService = new CustomerDetailService();

        #region Uninque Name for Custom Grid Columns
        private string colFirstName = "colFirstName";
        private string colLastName = "colLastName";
        private string colEmailAddress = "colEmailAddress";
       
        private string colCity = "colCity";
        private string colStateProvinceName = "colStateProvinceName";
        private string colPostalCode = "colPostalCode";
        private string colCountryRegionName = "colCountryRegionName";
        #endregion

        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void CustomGrid_NeedDataSource(object sender, 

Telerik.Web.UI.GridNeedDataSourceEventArgs e)
        {
            var customers = CustomerService.GetCustomerDetail(Filter, CustomGrid.CurrentPageIndex, 

CustomGrid.PageSize);
            var totalRow = customers.Count() > 0 ? customers.First().TotalRow : 0;

            CustomGrid.DataSource = customers;
            CustomGrid.VirtualItemCount = totalRow;            
        }

        protected void CustomGrid_ItemDataBound(object sender, Telerik.Web.UI.GridItemEventArgs e)
        {
            if (e.Item.ItemType == GridItemType.FilteringItem)
            {
                LoadFilterValues(e);
            }
        }

        private CustomerDetailFilter Filter
        {
            get
            {
                return new CustomerDetailFilter
                {

                    FirstName = CustomGrid.MasterTableView.GetColumn(colFirstName).CurrentFilterValue,
                    LastName = CustomGrid.MasterTableView.GetColumn(colLastName).CurrentFilterValue,
                    EmailAddress = CustomGrid.MasterTableView.GetColumn(colEmailAddress).CurrentFilterValue,
                    City = CustomGrid.MasterTableView.GetColumn(colCity).CurrentFilterValue,
                    PostalCode = CustomGrid.MasterTableView.GetColumn(colPostalCode).CurrentFilterValue,
                    StateProvinceName = CustomGrid.MasterTableView.GetColumn

(colStateProvinceName).CurrentFilterValue,
                    CountryRegionName = CustomGrid.MasterTableView.GetColumn

(colCountryRegionName).CurrentFilterValue
                };
            }
        }

        private void LoadFilterValues(GridItemEventArgs arg)
        {
            var result = CustomerService.GetCustomerDetailFilterValues(Filter);

            var filterFirstName = arg.Item.FindControl("filterFirstName") as RadTextBox;
            var filterLastName = arg.Item.FindControl("filterLastName") as RadTextBox;
            var filterEmailAddress = arg.Item.FindControl("filterEmailAddress") as RadTextBox;

            filterFirstName.Text = Filter.FirstName;
            filterLastName.Text = Filter.LastName;
            filterEmailAddress.Text = Filter.EmailAddress;

            var filterCity = arg.Item.FindControl("filterCity") as RadComboBox;
            var filterStateProvinceName = arg.Item.FindControl("filterStateProvinceName") as RadComboBox;
            var filterPostalCode = arg.Item.FindControl("filterPostalCode") as RadComboBox;
            var filterCountryRegionName = arg.Item.FindControl("filterCountryRegionName") as RadComboBox;
            
            filterCity.DataSource = result.City;
            filterCity.DataBind();
            filterCity.SelectedValue = Filter.City;

            filterStateProvinceName.DataSource = result.StateProvinceName;
            filterStateProvinceName.DataBind();
            filterStateProvinceName.SelectedValue = Filter.StateProvinceName;

            filterPostalCode.DataSource = result.PostalCode;
            filterPostalCode.DataBind();
            filterPostalCode.SelectedValue = Filter.PostalCode;

            filterCountryRegionName.DataSource = result.CountryRegionName;
            filterCountryRegionName.DataBind();
            filterCountryRegionName.SelectedValue = Filter.CountryRegionName;
        }

    }
}

Below is the screen shot of the rad grid with custom pagination and custom filtering.

Conclusion

Is this the best approach? Yes for my previous assignment!, It depends on the factors such as data set size, indexing, application design/architecture and user behaviour/expectation.
In my case, this approach has worked really well for custom pagination and cascaded filtering with large dataset. If there are any other better approaches, please share !