get kendo grid data from Database using ASP.NET

[ak12345]

Hi,

I have a kendo grid in and aspx page that gets data from database. This data is pretty huge and will get bigger. Hence it is taking time. So I want to implement serverpaging and filtering logic here. Here is my code:

// aspx page code

var new_url = "/getdata.aspx?dt="+ date_passed
var data_source1 = new kendo.data.DataSource({

                    transport: {
                        read: {
                            url: new_url,
                            dataType: "json"
                        },
                        cache: true
                    },
                    pageSize: 100,
                    page: 1
                });

                var grid_updated = $("#gridAllRuns").kendoGrid({
                    dataSource: data_source1,
                    dataBound: onDataBound,
                    height: $(document).height() - 250,
                    groupable: true,
                    sortable: true,
                    columnMenu: true,
                    reorderable: true,
                    resizable: true,
                    pageable: {
                        refresh: true,
                        pageSizes: [100, 500, 1000],
                        pageSize: 100,
                        buttonCount: 10
                    },
                   filterable: {
                        extra: false,

                        operators: {
                            string: {
                                eq: "equal to",
                                startswith: "starts with",
                                neq: "not equal to"
                            }
                        }
                    },
                    toolbar: kendo.template($("#template").html()),
                    columns: [{ title: "Firstname", field: "fname"}, { title: "Lastname", field: "lname"}]
});

// getdata.aspx.vb code:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 Dim for_date As String = Request.QueryString("dt")
Dim get_data As String = "select fname, lname where dt_passed ='" & for_date & "' order by fname"
        Dim url As String = HttpContext.Current.Request.Url.AbsoluteUri
        Dim data As String = ""
        Dim constr As String = constr
        Dim connection As New MySqlConnection(constr)
        connection.Open()
        Dim command As New MySqlCommand(get_data, connection)
        command.CommandType = System.Data.CommandType.Text
        Dim da As New MySqlDataAdapter()
        da.SelectCommand = command

        Dim dt As New DataTable()
        da.Fill(dt)
        'Console.Write(GetJson(dt))

        Response.Clear()
        Response.ContentType = "application/json; charset=utf-8"
        Response.Write(GetJson(dt))
        Response.End()
end sub
Public Function GetJson(ByVal dt As DataTable) As String
      Dim serializer As New System.Web.Script.Serialization.JavaScriptSerializer()
        serializer.MaxJsonLength = 107869240
        Dim rows As New List(Of Dictionary(Of String, Object))()
        Dim row As Dictionary(Of String, Object) = Nothing

        For Each dr As DataRow In dt.Rows
            row = New Dictionary(Of String, Object)()
            For Each col As DataColumn In dt.Columns
                row.Add(col.ColumnName.Trim(), dr(col))
            Next
            rows.Add(row)
        Next
        Return serializer.Serialize(rows)
    End Function

I am returning everything at once. Is there any logic that I can perform serverpaging and serverfiltering while getting the data from database? Please help!!!

[Alexander Valchev]

Hello Archana,

Yes this is possible. In terms of Kendo UI you should turn on the serverPaging and serverFiltering options of the dataSource.

• http://docs.telerik.com/kendo-ui/api/framework/datasource#configuration-serverPaging
• http://docs.telerik.com/kendo-ui/api/framework/datasource#configuration-serverFiltering

Then the dataSource will start sending Ajax requests to the server with the paging/filtering parameters and expect the server to return paged/filtered data. In case you need to modify or change the format of request parameters please use the parameterMap function.

There is a sample project which demonstrates such scenario. It can be downloaded from here.

Regards,
Alexander Valchev
Telerik