[Solved] Server Side Paging in jQuery DataTable using ASP.Net WebService not working

lingers
 
on May 15, 2021 07:46 AM
Sample_521276.zip
1190 Views

Server Side Paging in jQuery DataTable using jQuery AJAX and WebService in ASP.Net not working

Refer below sample.

Database

For this sample I have used of NorthWind database

how do i fix it

<script type="text/javascript" src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />
<script type="text/javascript" src="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>
<script type="text/javascript">
    $(function () {
        $('#tblCustomers').DataTable({
            "bServerSide": true,
            "bProcessing": true,
            "sPaginationType": "full_numbers",
            "pageLength": "5",
            "sAjaxSource": "WebService.asmx/GetData",
            "aoColumns": [
                { "mData": "CustomerID" },
                { "mData": "ContactName" },
                { "mData": "City" },
                { "mData": "Country" }
            ],
            "fnServerData": function (source, data, callback) {
                $.ajax({
                    "dataType": 'json',
                    "contentType": "application/json; charset=utf-8",
                    "type": "GET",
                    "url": source,
                    "data": data,
                    "success": function (response) {
                        var json = jQuery.parseJSON(response.d);
                        callback(json);
                    }
                });
            }
        });
    });
</script>
<table id="tblCustomers">
    <thead>
        <tr>
            <th>CustomerID</th>
            <th>ContactName</th>
            <th>City</th>
            <th>Country</th>
        </tr>
    </thead>
</table>

 

using System.Linq;
using System.Web.Services;
using NorthwindModel;
using System.Text;
using System.Web.Script.Serialization;
using System.Web.Script.Services;

/// <summary>
/// Summary description for WebService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{
    [WebMethod]
    [ScriptMethod(UseHttpGet = true)]
    public string GetData(string sEcho, int iDisplayStart, int iDisplayLength)
    {
        NorthwindEntities entities = new NorthwindEntities();
        sSearch = sSearch == null ? "" : sSearch;
        int totalRecord = entities.Customers.Count();
        var result = (from customer in entities.Customers
                      select customer)
        .OrderBy(customer => customer.CustomerID)
        .Skip(iDisplayStart)
        .Take(iDisplayLength).ToList();

        JavaScriptSerializer js = new JavaScriptSerializer();
        StringBuilder sb = new StringBuilder();
        sb.Clear();
        sb.Append("{");
        sb.Append("\"sEcho\": ");
        sb.Append(sEcho);
        sb.Append(",");
        sb.Append("\"iTotalRecords\": ");
        sb.Append(totalRecord);
        sb.Append(",");
        sb.Append("\"iTotalDisplayRecords\": ");
        sb.Append(totalRecord);
        sb.Append(",");
        sb.Append("\"aaData\": ");
        sb.Append(js.Serialize(result));
        sb.Append("}");
        return sb.ToString();
    }
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on May 15, 2021 09:42 AM

Hi lingers,

Refer below updated code.

HTML

<script type="text/javascript" src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />
<script type="text/javascript" src="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>
<script type="text/javascript">
    $(function () {
        $('#tblCustomers').DataTable({
            "bServerSide": true,
            "bProcessing": true,
            "sPaginationType": "full_numbers",
            "pageLength": "5",
            "sAjaxSource": "WebService.asmx/GetData",
            "aoColumns": [
                { "mData": "CustomerID" },
                { "mData": "ContactName" },
                { "mData": "City" },
                { "mData": "Country" }
            ],
            "fnServerData": function (source, data, callback) {
                $.ajax({
                    "dataType": 'json',
                    "contentType": "application/json; charset=utf-8",
                    "type": "GET",
                    "url": source,
                    "data": data,
                    "success": function (response) {
                        debugger;
                        var json = jQuery.parseJSON(response.d);
                        callback(json);
                    }
                });
            }
        });
    });
</script>
<table id="tblCustomers">
    <thead>
        <tr>
            <th>CustomerID</th>
            <th>ContactName</th>
            <th>City</th>
            <th>Country</th>
        </tr>
    </thead>
</table>

WebService

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Script.Serialization;
using System.Web.Script.Services;
using System.Web.Services;

/// <summary>
/// Summary description for WebService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{
    [WebMethod]
    [ScriptMethod(UseHttpGet = true)]
    public string GetData(string sEcho, int iDisplayStart, int iDisplayLength)
    {
        NorthwindEntities entities = new NorthwindEntities();
        // sSearch = sSearch == null ? "" : sSearch;
        int totalRecord = entities.Customers.Count();
        var result = entities.Customers
            .Select(x => new Customer
            {
                CustomerID = x.CustomerID,
                ContactName = x.ContactName,
                City = x.City,
                Country = x.Country
            }).OrderBy(customer => customer.CustomerID).Skip(iDisplayStart).Take(iDisplayLength).ToList();

        JavaScriptSerializer js = new JavaScriptSerializer();
        StringBuilder sb = new StringBuilder();
        sb.Clear();
        sb.Append("{");
        sb.Append("\"sEcho\": ");
        sb.Append(sEcho);
        sb.Append(",");
        sb.Append("\"iTotalRecords\": ");
        sb.Append(totalRecord);
        sb.Append(",");
        sb.Append("\"iTotalDisplayRecords\": ");
        sb.Append(totalRecord);
        sb.Append(",");
        sb.Append("\"aaData\": ");
        sb.Append(js.Serialize(result));
        sb.Append("}");
        return sb.ToString();
    }

    public class Customer
    {
        public string CustomerID { get; set; }
        public string ContactName { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
    }

}

Screenshot