Server Side Pagination not working for the first page kendo grid mvc wrapper

Question

As my kendo grid take too much time to load if data is more than 15000 records, i think of implementing server side paging.

I am not using entity framework. I am using Kendo MVC wrapper and SQL Server Stored procedures to pull and display the data.

I have passed two parameters to Procedure as @Skip and @Take.

Following is my code :

View

@(Html.Kendo().Grid(Model)
    .Name("expandedView_" + Model.ToArray()[0].WidgetId.ToString())
    .Columns(columns =>
    {
        columns.Bound(p => p.FacilityNameAbbreviation).Title(@Resource.FacilityName).Width("15%");
        columns.Bound(p => p.DeviceAbbreviation).Title(@Resource.DeviceName).Width("10%");
        columns.Bound(p => p.EventType).Title(@Resource.EventType).Width("40%");
        columns.Bound(p => p.SubEventCode).Title(@Resource.SubAlarmCode).Width("10%").HtmlAttributes(new { style = "text-align:right" });
        columns.Bound(p => p.FacilityEventTime).Title(@Resource.EventTime).Width("15%");
        columns.Bound(p => p.EventStatus).Title(@Resource.Status).Width("10%");
        columns.Bound(p => p.PriorityColor).Hidden(true);
    })
    .Scrollable()
    .Pageable()
    .DataSource(dt => dt
    .Ajax()
    .PageSize(100)
    .Read(read => read.Action("PageWiseData", "DeviceEvent", new { WidgetId = Model.ToArray()[0].WidgetId, FacilityIds = ViewData["Facids"].ToString() }))
    .ServerOperation(true)
    .Model(model => model.Id(p => p.FacilityEventTime)))
)

Actions :

[HandleError(ExceptionType = typeof(Exception), View = "ApplicationError")]
public PartialViewResult DeviceEventExpandedView(int WidgetId, string FacilityIds)
{
    var userDetail = sessionContext.UserContextBag;

    int Skip = 0; // for first page data static value is passed
    int Take = 100;

    IList<AMI.WebRole.Models.Widgets.DeviceEventModel> result = _deviceEventClient.DeviceEventsForExpandedView(WidgetId, FacilityIds, TenantId, CustomerId, UserId, this.sessionContext.UserContextBag.CultureName, Skip, Take);

    return PartialView(result);
}


public ActionResult PageWiseData(int WidgetId, string FacilityIds, [DataSourceRequest]DataSourceRequest request)
{
    IList<AMI.WebRole.Models.Widgets.DeviceEventModel> Totalresult = null;

    var Skip = (request.Page - 1) * request.PageSize;
    var Take = request.PageSize;

    Totalresult = _deviceEventClient.DeviceEventsForExpandedView(WidgetId, FacilityIds, TenantId, CustomerId, UserId, this.sessionContext.UserContextBag.CultureName, Skip, Take);

    return Json(new
        {
            Data = Totalresult,
            Total = 1500
        });
}

PROBLEM :

For the first time "DeviceEventExpandedView" is called, view is loaded and data is displayed successfully, BUT THE PAGE NUMBER APPEAR AS 1 ONLY for the first time. In my grid, filters are also there, once i click on filter the method "PageWiseData" is called again it pull 100 records and this time is shows page number for subsequent pages. As per my understanting second time the returned data is in JSON which also includes Total=1500, but in first case when the partial view is loaded, this count "Total" is not set.

I am not able to show the page numbers (should be upto 15 in my case) when the partial view is loaded and grid is bind for the first time

CAN ANY ONE HELP HERE, HOW TO PASS COUNT FOR THE FIRST TIME or HOW TO BIND THE GRID WITH METHOD "PageWiseData" FOR THE FIRST TIME....OR any other way to do this...please help !!

Your Action is doing it wrong, let Kendo filter your result as explained by @hutchonoid. — Rosdi Kasim
i think, for this i have to pull all the records from the DB first, am I right?? — devgal
No, .ToDataSourceResult(request) will apply its own filter and pagination to the query, so only the required records will be retrieved. — Rosdi Kasim

hutchonoid hutchonoid · Accepted Answer · 2015-07-10T09:09:26

If you apply the following you should not need to care or worry about handling the paging yourself.

Change your totalResult variable to be an IQueryable<AMI.WebRole.Models.Widgets.DeviceEventModel> instead
Change the DeviceEventsForExpandedView method, remove Skip and Take parameters and also make sure it returns an IQueryable<AMI.WebRole.Models.Widgets.DeviceEventModel>

If you return IQueryable Kendo should apply sorting and filtering at the DB rather than in memory.

Also make sure you use the .ToDataSourceResult(request) to return the result as follows:

public ActionResult PageWiseData(int WidgetId, string FacilityIds, [DataSourceRequest]DataSourceRequest request)
{
    IQueryable<AMI.WebRole.Models.Widgets.DeviceEventModel> totalresult = null;

    var totalresults = _deviceEventClient.DeviceEventsForExpandedView(WidgetId, 
          FacilityIds, TenantId, CustomerId, UserId,
           this.sessionContext.UserContextBag.CultureName);

        var results = totalresults.ToDataSourceResult(request);
        return Json(results);

}

Good article on the subject:

Server Paging, Sorting, and Filtering with Kendo DataSourceRequest

Server Side Pagination not working for the first page kendo grid mvc wrapper

1 Answers