jQuery DataTables server side processing and ASP.Net

I'm trying to use the server side functionality of the jQuery Datatables plugin with ASP.Net. The ajax request is returning valid JSON, but nothing is showing up in the table.

I originally had problems with the data I was sending in the ajax request. I was getting a "Invalid JSON primative" error. I discovered that the data needs to be in a string instead of JSON serialized, as described in this post: http://encosia.com/2008/06/05/3-mistakes-to-avoid-when-using-jquery-with-aspnet-ajax/. I wasn't quite sure how to fix that, so I tried adding this in the ajax request:

"data": "{'sEcho': '" + aoData.sEcho + "'}"

If the aboves eventually works I'll add the other parameters later. Right now I'm just trying to get something to show up in my table.

The returning JSON looks ok and validates, but the sEcho in the post is undefined, and I think thats why no data is being loaded into the table.

So, what am I doing wrong? Am I even on the right track or am I being stupid? Does anyone ran into this before or have any suggestions?

Here's my jQuery:

$(document).ready(function()
{

    $("#grid").dataTable({
            "bJQueryUI": true,
            "sPaginationType": "full_numbers",
            "bServerSide":true, 
            "sAjaxSource": "GridTest.asmx/ServerSideTest", 
            "fnServerData": function(sSource, aoData, fnCallback) {
               $.ajax({
                    "type": "POST",
                    "dataType": 'json',
                    "contentType": "application/json; charset=utf-8",
                    "url": sSource, 
                    "data": "{'sEcho': '" + aoData.sEcho + "'}",
                    "success": fnCallback
                });
           }
         });


 });

HTML:

<table id="grid">
   <thead>
      <tr>
         <th>Last Name</th>
         <th>First Name</th>
         <th>UserID</th>
       </tr>
    </thead>

    <tbody>
       <tr>
    <td colspan="5" class="dataTables_empty">Loading data from server</td>
       </tr>
    </tbody>
 </table>

Webmethod:

 <WebMethod()> _
Public Function ServerSideTest() As Data


    Dim list As New List(Of String)
    list.Add("testing")
    list.Add("chad")
    list.Add("testing")

    Dim container As New List(Of List(Of String))
    container.Add(list)

    list = New List(Of String)
    list.Add("testing2")
    list.Add("chad")
    list.Add("testing")

    container.Add(list)

    HttpContext.Current.Response.ContentType = "application/json"

    Return New Data(HttpContext.Current.Request("sEcho"), 2, 2, container)

End Function


Public Class Data
Private _iTotalRecords As Integer
Private _iTotalDisplayRecords As Integer
Private _sEcho As Integer
Private _sColumns As String
Private _aaData As List(Of List(Of String))

Public Property sEcho() As Integer
    Get
        Return _sEcho
    End Get
    Set(ByVal value As Integer)
        _sEcho = value
    End Set
End Property

Public Property iTotalRecords() As Integer
    Get
        Return _iTotalRecords
    End Get
    Set(ByVal value As Integer)
        _iTotalRecords = value
    End Set
End Property

Public Property iTotalDisplayRecords() As Integer
    Get
        Return _iTotalDisplayRecords
    End Get
    Set(ByVal value As Integer)
        _iTotalDisplayRecords = value
    End Set
End Property



Public Property aaData() As List(Of List(Of String))
    Get
        Return _aaData
    End Get
    Set(ByVal value As List(Of List(Of String)))
  开发者_运维知识库      _aaData = value
    End Set
End Property

Public Sub New(ByVal sEcho As Integer, ByVal iTotalRecords As Integer, ByVal iTotalDisplayRecords As Integer, ByVal aaData As List(Of List(Of String)))
    If sEcho <> 0 Then Me.sEcho = sEcho
    Me.iTotalRecords = iTotalRecords
    Me.iTotalDisplayRecords = iTotalDisplayRecords
    Me.aaData = aaData
End Sub

Returned JSON:

{"__type":"Data","sEcho":0,"iTotalRecords":2,"iTotalDisplayRecords":2,"aaData":[["testing","chad","testing"],["testing2","chad","testing"]]}

---

I changed the data to

"data": "{'sEcho': '"+ aoData[0].value + "'}",

and it worked. So now the question is how to pass the rest of the data to the webservice. I tried using JSON2 to turn the JSON into a string, but that opened up another can of worms, and is a separate issue.

---

There are at least two issues in your javascript code:

1. "data": "{'sEcho': '" + aoData[0].value + "'}",

This was already pointed by Chad. That's the right way to get the sEcho:

1. "success": function (msg) { fnCallback(msg.d); }

If you're using a recent version of .net (I believe 3.5 and up) you need to adjust the success function to return correctly. Read this to understand why you have to pass "msg.d".

Here is your js code updated:

$("#grid").dataTable({
        "bJQueryUI": true,
        "sPaginationType": "full_numbers",
        "bServerSide":true, 
        "sAjaxSource": "GridTest.asmx/ServerSideTest", 
        "fnServerData": function(sSource, aoData, fnCallback) {
           $.ajax({
                "type": "POST",
                "dataType": 'json',
                "contentType": "application/json; charset=utf-8",
                "url": sSource, 
                "data": "{'sEcho': '" + aoData[0].value + "'}",
                "success": function (msg) {
                            fnCallback(msg.d);
                        }
            });
       }
     });

Then to get this working on the server side, I'm not sure what you'll have to change in your code (since I'm not a VB guy), but I know that the following works for me (using a asmx webservice):

using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Collections.Generic;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class GridTest : System.Web.Services.WebService
{

    [WebMethod]
    public FormatedList ServerSideTest(string sEcho)
    {
        var list = new FormatedList();

        list.sEcho = sEcho;
        list.iTotalRecords = 1;
        list.iTotalDisplayRecords = 1;

        var item = new List<string>();
        item.Add("Gecko");
        item.Add("Firefox 1.0");
        item.Add("Win 98+ / OSX.2+");
        item.Add("1.7");
        item.Add("A");
        list.aaData = new List<List<string>>();
        list.aaData.Add(item);

        return list;
    }

}

public class FormatedList
{
    public FormatedList()
    {
    }
    public string sEcho { get; set; }
    public int iTotalRecords { get; set; }
    public int iTotalDisplayRecords { get; set; }
    public List<List<string>> aaData { get; set; }
}

The class "FormatedList" is simply to help with the json return, converted automatically because we're using the ScriptService.

---

I've been working on the same thing and a friend of mine helped me with this part. This code is in C# but you should be able to port it over.

jQuery code:

<script type="text/javascript">
        $(document).ready(function() {
            function renderTable(result) {
                var dtData = [];
                // Data tables requires all data to be stuffed into a generic jagged array, so loop through our
                // typed object and create one.
                $.each(result, function() {
                    dtData.push([
                           this.FirstName,
                           this.LastName,
                           this.Sign
                        ]);
                });

                $('#grid').dataTable({
                    'aaData': dtData,
                    "bJQueryUI": true
                });
            }

            // Make an AJAX call to the PageMethod in the codebehind
            $.ajax({
                url: '<%= Request.Url.AbsolutePath %>/ServerSideTest',
                data: '{}',
                type: 'POST',
                contentType: 'application/json; charset=utf-8',
                dataType: 'json',
                success: function(result) {
                    // Call the renderTable method that both fills the aaData array with data and initializes the DataTable.
                    renderTable(result.d);
                },
                error: function(XMLHttpRequest, textStatus, errorThrown) {
                    alert(XMLHttpRequest + ": " + textStatus + ": " + errorThrown);
                }
            });
        });
    </script>

aspx code:

<table id="grid" width="100%">
        <thead>
            <tr>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Sign</th>
            </tr>
        </thead>

        <tbody>
            <tr>
                <td colspan="5" class="dataTables_empty">Loading data from server</td>
            </tr>
        </tbody>
    </table>

code behind:

// to serialize JSON in ASP.NET, it requires a class template.
    [Serializable]
    public class Data
    {
        // Yay for 3.5 auto properties
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Sign { get; set; }
    };

    [WebMethod]
    public static List<Data> ServerSideTest()
    {
        List<Data> DataList = new List<Data>();

        Data thisData = new Data();
        thisData.FirstName = "Sol";
        thisData.LastName = "Hawk";
        thisData.Sign = "Aries";

        DataList.Add(thisData);

        Data thisData2 = new Data();
        thisData2.FirstName = "Mako";
        thisData2.LastName = "Shark";
        thisData2.Sign = "Libra";

        DataList.Add(thisData2);

        return DataList;
    }

I hope this helps!

The next step for me is getting the filtering, paging and sorting working. Let me know if you get these parts working =)

---

You might want to have a look at zowen's solution here http://weblogs.asp.net/zowens/archive/2010/01/19/jquery-datatables-plugin-meets-c.aspx. He's done a datatable parser that works pretty well.

Hope this helps.