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

Last Reply one month ago By pandeyism

Posted one month ago

hi.

since my table has large number of rows i tried to fetch 10 rows per page into datatable from datatabase. here i used serverSide: true. but giving an error.

is this the correct way(ie using serverSide: true) for loading required amount of rows (ie 10 rows/page) from database to datatable page.

how can i load only first 10 rows from database to first page in datatable and then load next 10 rows to datatable from database when next page option in datatable is clicked.

reference

Edit Update row in jQuery DataTables using Ajax JSON and WebService in ASP.Net

You are viewing reply posted by: pandeyism one month ago.
Posted one month ago

Hi jovceka,

Refer below sample.

Database

For this sample I have used of NorthWind database that you can download using the link given below.

Download Northwind Database

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) {
                        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>

WebServices

C#

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();
    }
}

VB.Net

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class WebService
    <WebMethod()>
<ScriptMethod(UseHttpGet:=True)>
    Public Function GetData(ByVal sEcho As String, ByVal iDisplayStart As Integer, ByVal iDisplayLength As Integer) As String
        Dim entities As NorthwindEntities = New NorthwindEntities()
        sSearch = If(sSearch Is Nothing, "")
        Dim totalRecord As Integer = entities.Customers.Count()
        Dim result = (From customer In entities.Customers Select customer).OrderBy(Function(customer) customer.CustomerID).Skip(iDisplayStart).Take(iDisplayLength).ToList()
        Dim js As JavaScriptSerializer = New JavaScriptSerializer()
        Dim sb As StringBuilder = 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()
    End Function
End Class

Screenshot