Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
C# code for handling Ajax calls for the DataTables.net client table-rendering plugin.
/// <summary>
/// Resultset to be JSON stringified and set back to client.
/// </summary>
[Serializable]
[SuppressMessage("ReSharper", "InconsistentNaming")]
public class DataTableResultSet
{
/// <summary>Array of records. Each element of the array is itself an array of columns</summary>
public List<List<string>> data = new List<List<string>>();
/// <summary>value of draw parameter sent by client</summary>
public int draw;
/// <summary>filtered record count</summary>
public int recordsFiltered;
/// <summary>total record count in resultset</summary>
public int recordsTotal;
public string ToJSON()
{
return JsonConvert.SerializeObject(this);
}
}
[Serializable]
[SuppressMessage("ReSharper", "InconsistentNaming")]
public class DataTableResultError : DataTableResultSet
{
public string error;
}
// Turns the Ajax call parameters into a DataTableParameter object
// Permission to use this code for any purpose and without fee is hereby granted.
// No warrantles.
using System;
using System.Collections.Generic;
using System.Linq;
using Newtonsoft.Json.Linq;
namespace DataTables
{
public class DataTableParameters
{
public Dictionary<int, DataTableColumn> Columns;
public int Draw;
public int Length;
public Dictionary<int, DataTableOrder> Order;
public bool SearchRegex;
public string SearchValue;
public int Start;
private DataTableParameters()
{
}
/// <summary>
/// Retrieve DataTable parameters from WebMethod parameter, sanitized against parameter spoofing
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public static DataTableParameters Get(object input)
{
return Get(JObject.FromObject(input));
}
/// <summary>
/// Retrieve DataTable parameters from JSON, sanitized against parameter spoofing
/// </summary>
/// <param name="input">JToken object</param>
/// <returns>parameters</returns>
public static DataTableParameters Get(JToken input)
{
return new DataTableParameters
{
Columns = DataTableColumn.Get(input),
Order = DataTableOrder.Get(input),
Draw = (int)input["draw"],
Start = (int)input["start"],
Length = (int)input["length"],
SearchValue =
new string(
((string)input["search"]["value"]).Where(
c => char.IsLetterOrDigit(c) || char.IsWhiteSpace(c) || c == '-').ToArray()),
SearchRegex = (bool)input["search"]["regex"]
};
}
}
public class DataTableColumn
{
public int Data;
public string Name;
public bool Orderable;
public bool Searchable;
public bool SearchRegex;
public string SearchValue;
private DataTableColumn()
{
}
/// <summary>
/// Retrieve the DataTables Columns dictionary from a JSON parameter list
/// </summary>
/// <param name="input">JToken object</param>
/// <returns>Dictionary of Column elements</returns>
public static Dictionary<int, DataTableColumn> Get(JToken input)
{
return (
(JArray)input["columns"])
.Select(col => new DataTableColumn
{
Data = (int)col["data"],
Name =
new string(
((string)col["name"]).Where(
c => char.IsLetterOrDigit(c) || char.IsWhiteSpace(c) || c == '-').ToArray()),
Searchable = (bool)col["searchable"],
Orderable = (bool)col["orderable"],
SearchValue =
new string(
((string)col["search"]["value"]).Where(
c => char.IsLetterOrDigit(c) || char.IsWhiteSpace(c) || c == '-').ToArray()),
SearchRegex = (bool)col["search"]["regex"]
})
.ToDictionary(c => c.Data);
}
}
public class DataTableOrder
{
public int Column;
public string Direction;
private DataTableOrder()
{
}
/// <summary>
/// Retrieve the DataTables order dictionary from a JSON parameter list
/// </summary>
/// <param name="input">JToken object</param>
/// <returns>Dictionary of Order elements</returns>
public static Dictionary<int, DataTableOrder> Get(JToken input)
{
return (
(JArray)input["order"])
.Select(col => new DataTableOrder
{
Column = (int)col["column"],
Direction =
((string)col["dir"]).StartsWith("desc", StringComparison.OrdinalIgnoreCase) ? "DESC" : "ASC"
})
.ToDictionary(c => c.Column);
}
}
<%-- Code fragment showing an example of invoking Ajax --%>
<%-- Notice that the parameters from DataTable must be encapsulated in
a Javascript object, and then stringified.
Notice also that the name of the Javascript object ("paerameters" in this example
has to match the parameter name in the WebMethod, or dotnet won't find the correct
WebMethod and the Ajax call will kick back a 500 error.
Permission to use this code for any purpose and without fee is hereby granted.
No warrantles.
--%>
<script type="text/javascript" src="/assets/js/datatables.min.js"></script>
<script type="text/javascript" >
$(document).ready(function () {
$('#mytable').DataTable({
processing: true,
serverSide: true,
ajax: {
type: "POST",
contentType: "application/json; charset=utf-8",
url: "/DataTables.aspx/Data",
data: function (d) {
return JSON.stringify({ parameters: d });
}
}
});
});
</script>
//This snkppet shows the webmethod to use
// Permission to use this code for any purpose and without fee is hereby granted.
// No warrantles.
[WebMethod (Description = "Server Side DataTables support", EnableSession = true)]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public static void Data(object parameters)
{
var req = DataTableParameters.Get(parameters);
...
var resultSet = new DataTableResultSet();
resultSet.draw = req.Draw;
resultSet.recordsTotal = /* total number of records in table */
resultSet.recordsFltered = /* number of records after search - box filtering is applied */
foreach (var recordFromDb in queryDb) { /* this is pseudocode */
var columns = new List<string>();
columns.Add("first column value");
columns.Add("second column value");
columns.Add("third column value");
/* you may add as many columns as you need. Each column is a string in the List<string> */
resultSet.data.Add(columns);
}
SendResponse(HttpContext.Current.Response, result);
}
private static void SendResponse(HttpResponse response, DataTableResultSet result)
{
response.Clear();
response.Headers.Add("X-Content-Type-Options", "nosniff");
response.Headers.Add("X-Frame-Options", "SAMEORIGIN");
response.ContentType = "application/json; charset=utf-8";
response.Write(result.ToJSON());
response.Flush();
response.End();
}
@jackweldon

This comment has been minimized.

Copy link

commented Jun 16, 2016

Hi, I've been trying to implement your code but when I call the Data(object parameters) function within the controller it is always null. Any ideas?

@eskovar

This comment has been minimized.

Copy link

commented Jul 6, 2016

Hi jackweldon I believe you need to set serverSide property to true, then the object may appear!

@iChiragLad

This comment has been minimized.

Copy link

commented Mar 28, 2017

I too am getting null as an object parameter in controller method. I have set serverSide property to true.

@HugoMtzM

This comment has been minimized.

Copy link

commented Apr 10, 2017

Hello, do everything you put and in the end, bring me all the records, do not filter me by the "recordTotals", what can it be?

Thank you

@abuzaransari

This comment has been minimized.

Copy link

commented Aug 31, 2017

Thank you For Sharing Your Code.
How Ever I am not able to do column Ordering And search is not Working as well.
Kindly Help for the same.

Below Is my Js Code
` <script type="text/javascript">

    $(document).ready(function () {
        $('#example1').DataTable({
            dom: 'Bfrtip',
            //                dom: 'B<clear>frtip',
            buttons: [
            'pageLength',
                                        {
                                            extend: 'copyHtml5',
                                            exportOptions: {
                                                columns: [0, ':visible']
                                            }
                                        },
                                        {
                                            extend: 'excelHtml5',
                                            exportOptions: {
                                                columns: ':visible'
                                            }
                                        },
                                        {
                                            extend: 'pdfHtml5',
                                            exportOptions: {
                                                columns: ':visible'
                                            }
                                        },
                                        'colvis'
                                    ],
            paging: true,
            lengthChange: true,
            DisplayLength: 10,
            searchable: true,
            orderable: true,
            info: true,
            autoWidth: false,
            order: [[0, 'desc']],
            columns: [
            { data: "courierNo", orderable: true, searchable: true },
            { data: "consignerName", orderable: true, searchable: true },
            { data: "consigneeName", orderable: true, searchable: true },
            { data: "bookingDate", orderable: true, searchable: true },
            { data: "dues", orderable: true, searchable: true },
            { data: "edit", orderable: false, searchable: false },
            { data: "print", orderable: false, searchable: false },
        ],
            lengthMenu: [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],
            JQueryUI: true,
            //"processing": true, //control the processing indicator.
            DeferRender: true,
            info: true,   // control table information display field
            stateSave: true,  //restore table state on page reload,
            processing: true,
            serverSide: true,
            ajax: {
                type: "POST",
                contentType: "application/json; charset=utf-8",
                url: '<%=ResolveUrl("~/Test/testtables.aspx/Data") %>',
                data: function (d) {
                    return JSON.stringify({ parameters: d });
                }
            }
        });
    });

</script>`

Its Giving An Error String is not in its correct format.

@Arnonthawajjana

This comment has been minimized.

Copy link

commented Jun 7, 2018

changes as follow it's work for me.

public Dictionary<int, DataTableColumn> Columns; --> public Dictionary<string, DataTableColumn> Columns;

public static Dictionary<int, DataTableColumn> Get(JToken input) --> public static Dictionary<string, DataTableColumn> Get(JToken input)

return Get(JObject.FromObject(input)); --> return Get(JObject.FromObject(input)["parameters"]);

Data = (int)col["data"], --> Data = (string)col["data"],

public static void Data(object parameters) -- > public static void Data([FromBody]object parameters)

@snomula-idc

This comment has been minimized.

Copy link

commented Jul 15, 2018

Can you guys help me with the same to load data in Bootstrap table (Server side pagination and search)? Thanks in advance

@AlexanderKrutov

This comment has been minimized.

Copy link

commented Aug 24, 2018

@snomula-idc Some time ago I've created a small library for processing requests from datatables.js on server side. It supports pagination, sorting and search. You can try it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.