Bind a database table with Kendo Grid (MVC) using stored procedure, with large amounts of data with proper server side paging, sorting, filtering

Question

We have a table with a large amount of data (in the millions of rows). We are using a stored procedure to fetch the records and binding that data with kendo grid using mvc approach. But we want to fetch only desired rows from table instead of getting the whole set of data in one go and then make filtering, sorting, paging options on that.

Would it be possible to fetch only one page of rows at a time? Please help and suggest some approach.

Your question is too broad... maybe you should start by reading kendo's documentation. — The_Black_Smurf, Apr 09 '17 at 22:41

score 1 · Answer 1 · answered Apr 09 '17 at 09:59

First, you should have some paging logic inside your stored procedure. You can take a look at this blog post for suggestions: https://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch

Then, in your view, set your grid's data source to call a custom action in your controller. Make sure you enable paging. Something like this:

.Pageable()
.DataSource(dataSource => dataSource
        .Ajax()
        .Read(read => read.Action("CustomAjaxBinding_Read", "Grid"))
    )

Finally in your controller create the action and make it expect a DataSourceRequest parameter. That parameter will be receiving an object from the grid, which will include the page size and the current page number. You can use those to pass them to your stored procedure and make it fetch only that page of data. It will be something like this:

public ActionResult CustomAjaxBinding_Read([DataSourceRequest] DataSourceRequest request)
        {
            // Get the page number and size, which the grid is requesting
            var pageNumber = request.Page;
            var pageSize = request.PageSize;

            // Call your stored procedure here
            // ...

            // Now return the data to the grid, formatted as JSON
            return Json(result);
        }

You can see an example of ajax datasource on Telerik's website: Look at http://demos.telerik.com/aspnet-mvc/grid/customajaxbinding.

This is exactly what i was thinking to implement, But still i want to ask, can we have Iqueryable store procedure ? — Mayank, Apr 09 '17 at 13:06
Just to show you my initial work : string filterLogic = ""; if (request.Filters.Any()) { foreach (var filter in request.Filters) { var descriptor = filter as FilterDescriptor; filterLogic = " where " + descriptor.Member + " = '" + descriptor.Value + "'"; } } — Mayank, Apr 09 '17 at 13:08
Your code suggests that you'd have the stored procedure return everything and then you would apply your filters/paging on that. This might not be efficient. Why not build the filtering logic inside the stored proc and then send the members and values as parameters like this http://stackoverflow.com/questions/697671/stored-procedure-with-optional-where-parameters. You can use OFFSET and FETCH NEXT for the paging logic. — Oggy, Apr 09 '17 at 15:57

score 1 · Answer 2 · answered Mar 22 '18 at 13:39

I've just done it like this (note: I'm using Dapper and EF)

var gridBinder = new GridBinder(request);

var filters = gridBinder.GetFilterDescriptor();

var sorting = gridBinder.SortInfo.Member.HasValue() ? string.Format("{0} {1}", gridBinder.SortInfo.Member, gridBinder.SortInfo.Direction) : "";

var p = new DynamicParameters();
p.Add("@Page", gridBinder.PageNumber, DbType.Int32, ParameterDirection.Input);
p.Add("@PageSize", gridBinder.PageSize, DbType.Int32, ParameterDirection.Input);
p.Add("@Filter", filters);
p.Add("@SortOrder", sorting);
p.Add("@TotalRowCount", dbType: DbType.Int32, direction: ParameterDirection.Output);

var data = _db.Connection().Query<IndexVm>("dbo.People_GetAll", p, commandType: System.Data.CommandType.StoredProcedure, transaction: _db.Database.CurrentTransaction.GetDbTransaction());

gridBinder.RecordCount = data.FirstOrDefault().TotalRowCount;

return Json(new DataSourceResult
{
    Total = gridBinder.RecordCount,
    Data = data
});

GridBinder

public class GridBinder
{
    public int PageNumber { get; set; } = 1;

    public int PageSize { get; set; } = 10;

    public int RecordCount { get; set; }

    public SortInfo SortInfo { get; set; } = new SortInfo() { Direction = SortDirection.Asc, Member = string.Empty };

    private readonly DataSourceRequest _command;

    public GridBinder(DataSourceRequest command)
    {
        _command = command;
        PageNumber = command.Page;
        PageSize = command.PageSize;
        GetSortDescriptor();
    }

    private void GetSortDescriptor()
    {
        foreach (SortDescriptor descriptor in _command.Sorts)
        {
            SortInfo.Member = descriptor.Member;
            SortInfo.Direction = descriptor.SortDirection == ListSortDirection.Ascending ? SortDirection.Asc : SortDirection.Desc;
        }
    }

    public string GetFilterDescriptor()
    {
        string filters = string.Empty;
        foreach (IFilterDescriptor filter in _command.Filters)
        {
            filters += ApplyFilter(filter);
        }

        return filters;
    }

    private static string ApplyFilter(IFilterDescriptor filter)
    {
        var filters = "";
        if (filter is CompositeFilterDescriptor)
        {
            filters += "(";
            var compositeFilterDescriptor = (CompositeFilterDescriptor)filter;
            foreach (IFilterDescriptor childFilter in compositeFilterDescriptor.FilterDescriptors)
            {
                filters += ApplyFilter(childFilter);
                filters += " " + compositeFilterDescriptor.LogicalOperator.ToString() + " ";
            }
        }
        else
        {
            string filterDescriptor = "{0} {1} {2}";
            var descriptor = (FilterDescriptor)filter;
            if (descriptor.Operator == FilterOperator.StartsWith)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, "LIKE", "'" + descriptor.Value + "%'");
            }
            else if (descriptor.Operator == FilterOperator.EndsWith)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, "LIKE", "'%" + descriptor.Value + "'");
            }
            else if (descriptor.Operator == FilterOperator.Contains)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, "LIKE", "'%" + descriptor.Value + "%'");
            }
            else if (descriptor.Operator == FilterOperator.DoesNotContain)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, "NOT LIKE", "'%" + descriptor.Value + "%'");
            }
            else if (descriptor.Operator == FilterOperator.IsEqualTo)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, "=", "'" + descriptor.Value + "'");
            }
            else if (descriptor.Operator == FilterOperator.IsNotEqualTo)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, "<>", "'" + descriptor.Value + "'");
            }
            else if (descriptor.Operator == FilterOperator.IsGreaterThan)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, ">", "'" + descriptor.Value + "'");
            }
            else if (descriptor.Operator == FilterOperator.IsGreaterThanOrEqualTo)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, ">=", "'" + descriptor.Value + "'");
            }
            else if (descriptor.Operator == FilterOperator.IsLessThan)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, "<", "'" + descriptor.Value + "'");
            }
            else if (descriptor.Operator == FilterOperator.IsLessThanOrEqualTo)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, "<=", "'" + descriptor.Value + "'");
            }

            filters = filterDescriptor;
        }

        filters = filters.EndsWith("And ") == true ? filters.Substring(0, filters.Length - 4) + ")" : filters;
        filters = filters.EndsWith("Or ") == true ? filters.Substring(0, filters.Length - 4) + ")" : filters;

        return filters;
    }
}
public class SortInfo
{
    public string Member { get; set; }
    public SortDirection Direction { get; set; }
}

public enum SortDirection
{
    Asc, Desc
}

Tim how do you have your stored procedure setup for these parameters? Can you post it please — Terrance Jackson, Jan 15 '19 at 15:49

Bind a database table with Kendo Grid (MVC) using stored procedure, with large amounts of data with proper server side paging, sorting, filtering

2 Answers2

Linked