jQuery DataTables server side processing and ASP.Net

Question

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"]]}

Answer 1

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.

Answer 2

How do you change the data?

Answer 3

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

Answer 4

Hi there,

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.