How to use server filtering and server paging and server sorting options with page,pagesize, take and skip parameters.

[Ganesh]

Hello Everyone,

grid is loading very slow when database records are more then 15000

i developed one kendo ui grid,
but sometimes i got the error like "jsonmaxlength exceeded "
for this error i given maximum length in web.config file, when i got this error my database has 36500 row .


This is my webmethod:

    <WebMethod(enableSession:=True)> _
    Public Function BindStyles() As String

        Dim obj As KendoData

        If Session("kendodata") Is Nothing OrElse DateDiff(DateInterval.Minute, CType(Session("kendodata"), KendoData)._time, Date.Now) >= 20 OrElse CType(Session("kendodata"), KendoData).StyleJSON Is Nothing Then
            ' obj.data = "[{Style:'S\'1',Color:'C1'},{Style:'S2',Color:'C2'}]"
            Dim dsresult As DataSet = Charts.BindStyles()
            Dim lst As New List(Of stylelookup)
            If dsresult IsNot Nothing AndAlso dsresult.Tables.Count > 0 AndAlso dsresult.Tables(0).Rows.Count > 0 Then
                lst = (From p In dsresult.Tables(0).AsEnumerable Select New  _
                                               stylelookup With _
                                               {.Color = CStr(p("color")).Trim, .Description = CStr(p("desc")).Trim, .Division = CStr(p("division")).Trim, .Group = CStr(p("group")).Trim, .primkey = CInt(p("primkey")), .Scale = CStr(p("scale")).Trim, .Season = CStr(p("season")).Trim, .selectstyle = CBool(p("select")), .Status = If(CStr(p("status")).Trim.ToLower.Replace("a", "") = "", "Active", "In-Active"), .Style = CStr(p("style")).Trim, .Whlprice = CDec(p("whlprice")), .Year = CInt(p("year"))}).ToList

            End If

            Dim objser As New JavaScriptSerializer()
            Dim json As String = objser.Serialize(lst)

            obj = New KendoData
            obj.StyleJSON = json
            obj._time = Date.Now
            Session("kendodata") = obj

            Return json

        Else
            obj = Session("kendodata")
            Return obj.StyleJSON

        End If
    End Function

and this is my default.aspx:

 function webmethod() {
               analytics.BindStyles(succ, failed);
    }
    function succ(res) {
        data = eval(res);
        load();
    }
    function failed(ex) {
      
        alert(ex._message);
    }


    function load() {
                      $("#grid").kendoGrid({
            dataSource: {
                data: data,
                pageSize: ((__gridH - 30 - 30) / 35),
                serverPaging: true, serverFiltering: true, serverSorting: true
            },
            dataBound: function (arg) {
                ___gridobj = this;
                $("#totalcount").html('' + this.dataSource._data.length + '');
                Pageing(this._data, this._data.length);

            },
            height: __gridH,

            scrollable: true,
            sortable: true,
            filterable: true,
            pageable: true,

            columns: [
         { title: "Select", width: "50px", filterable: false, sortable: false, template: '#= mycheckbox(primkey,selectstyle)#'
         },
                            {
                                field: "Style",
                                width: "100px",
                                title: "Style"
                            },
                            {
                                field: "Color",
                                width: "60px",
                                title: "Color"
                            },

                            {
                                field: "Description",
                                width: "120px",
                                title: "Description"
                            },
                            {
                                field: "Division",
                                width: "50px",
                                title: "Div"
                            },
                             {
                                 field: "Season",
                                 width: "70px",
                                 title: "Season"
                             },
                            {
                                field: "Scale",
                                width: "60px",
                                title: "Scale"
                            },
                             {
                                 field: "Status",
                                 width: "70px",
                                 title: "Status"
                             },
                             {
                                 field: "Group",
                                 width: "50px",
                                 title: "Grp"
                             },
                            {
                                field: "Whlprice",
                                width: "70px",
                                title: "WhlPrice"

                            },
                            {
                                field: "Year",
                                width: "40px",
                                title: "Year"
                            }

                      ]
        });
        return false;
    }

i attached my grid,

Can you explain how to bind grid with parameter like take ,skip,page,pagesize

[Kjell]

The parameters are passed in the query string, so you just have to pick them up in your web service.  Here is what it looks like:

&take=100&skip=0&page=1&pageSize=100

I'm actually in the same boat and just figured out how to implement the virtual scrolling.  In my case the page size is static so I only pick up the "skip" field so I know which segment of the data to return.  I hardcoded in the total just to get it working but obviously that's not a permanent solution, I'll go back and figure that out once it's all functional.

Now I'm trying to do sorting which seems a little trickier but I'll update the thread when I figure it out.

[Karl Mikesell]

How to trap the Column to sort event, and the Page selected event.  The dynamic remote data (10000s rows) is only loaded per page size; so really only previous & next (no page numbers) are required.

The sort operation will require an entire re-bind since it must use current page with column sort.

When page numbers appear does a seperate event exist for the Ellipsis (...) selection.  Then dyanmic data can loaded until Ellipsis appears, but does the kendoGrid have a property to indicates the Ellipsis is persent?

All of these events would be helpful in server-side paging, filtering, & sorting.

[Karl Mikesell]

Here is how to trap the page & column events:

    function onKendoDataBound(arg) {
        var src = jQuery(event.srcElement);
        var page = parseInt(src.attr("data-page"));
        if (!isNaN(page)) {
            _currentPage = page;
            return;
        }
        var column = src.parent().attr("data-field");
        if (typeof column == "string") {
            if (src.children(".k-arrow-up").length > 0) {
                _decSort = true;
            }
            _currentColumn = column;
            return;
        }
    }

Add dataBound event handler:

jQuery("#kendoGridId").kendoGrid({
    ...
    dataBound: onKendoDataBound,
    ...
));