Sample implementation of serverside processing in C# MVC, EF with paging/sorting/searching

allan

@farshidazii Our Editor .NET libraries support server-side processing with SearchPanes: https://github.com/DataTables/Editor-NET .

Allan

farshidazii

Which class should be used? for map the datatable inbound JSON requests to classes

public class jQueryDataTableParamModel
    {
        /// <summary>
        /// Request sequence number sent by DataTable,
        /// same value must be returned in response
        /// </summary>
        public string sEcho { get; set; }
        /// <summary>
        /// Text used for filtering
        /// </summary>
        public string sSearch { get; set; }
        /// <summary>
        /// Number of records that should be shown in table
        /// </summary>
        public int iDisplayLength { get; set; }
        /// <summary>
        /// First record that should be shown(used for paging)
        /// </summary>
        public int iDisplayStart { get; set; }
        /// <summary>
        /// Number of columns in table
        /// </summary>
        public int iColumns { get; set; }
        /// <summary>
        /// Number of columns that are used in sorting
        /// /// </summary>
        public int iSortingCols { get; set; }
        /// <summary>
        /// Comma separated list of column names
        /// </summary>
        public string sColumns { get; set; }
    }
}

or

public class DataTableAjaxPostModel
    {
        // properties are not capital due to json mapping
        public int draw { get; set; }
        public int start { get; set; }
        public int length { get; set; }
        public List<Column> columns { get; set; }
        public Search search { get; set; }
        public List<Order> order { get; set; }
    }

    public class Column
    {
        public string data { get; set; }
        public string name { get; set; }
        public bool searchable { get; set; }
        public bool orderable { get; set; }
        public Search search { get; set; }
    }

    public class Search
    {
        public string value { get; set; }
        public string regex { get; set; }
    }

    public class Order
    {
        public int column { get; set; }
        public string dir { get; set; }
    }
    /// End- JSon class sent from Datatables

What is the difference between these two classes

allan

The Editor getting started documentation will hopefully help.

For security you'll want to add .Set(false) to your fields to make sure no one can submit data to be edited (since it is editable by default with the Editor server-side libraries!).

Allan

ZahidMohammed

Sorting functionality EF

// c#



 public class JQueryDataTableParamModel 
{
        public Int32 start { get; set; }

        public Int32 length { get; set; }

        public JQueryDataTableOrder[] order { get; set; }

       public class JQueryDataTableOrder
        {
            public Int32 column { get; set; }

            public String dir { get; set; }
        }
}

// Script

 MyFunctionName: function (tableId)
{
     $(tableId).DataTable({
                        serverSide: true,
                        processing: true,
                        deferRender: true,
                        "bPaginate": true,
                        "bLengthChange": false,
                        "bFilter": false,
                        "bInfo": true,
                        "bAutoWidth": true,
                        "ordering": true,
                        "bSortClasses": false,
                        "bDestroy": true,
                        bLengthChange: false,
                        "iDisplayLength": 50,
                        responsive: true,
                        dom: 'Bfrtip',
                        ajax: function (data, callback, s) {
                            var parms = {
                                start: data.start,
                                length: data.length,
                                order: [{ column: data.order[0].column, dir: data.order[0].dir }],
                                addtionalParam: $('#IdOfTheTextBox').val(),
                            }

                            ajax =
                                $.ajax({
                                    url: '/AreaName/ControllerName/Method',
                                    type: "POST",
                                    contentType: "application/json",
                                    dataType: 'json',
                                    data: JSON.stringify(parms),
                                    success: function (data) { callback(data); },
                                    error: function (e) {
                                      // can add sweetalert or normal alert
                                    }
                                });
                        },
                        aoColumns: [
                            {
                                sName: "Name",
                                render: function (a, b, c) {
                                    return '<td>' + a + '</td>'
                                }
                            },
                            {
                                sName: "Surname",
                                render: function (a, b, c) { return '<td>' + a + '</td>' }
                            },                   
                            {
                                sName: "Id",
                                "bSortable": false,
                                sClass: "action",
                                render: function (a, b, c) {
                                 return "This will be your button(s)"
                                }
                            }
                        ]
                    });
}

// controller

 public JsonResult MethodSignature(string additionalParam, JQueryDataTableParamModel param)
        {
          
           var myList= GetYourList();

            if (param.order[0].dir == "asc")
               myList= myList.OrderBy(x=>x.Name);
            else
               myList= myList.OrderByDescending(x=>x.Name);

            var dataToReturn= sm.Skip(param.start).Take(param.length).ToList().Select(x 
             => new object[]
            {
                x.Name,
                x.Surname,
                x.Id
            });

            return Json(new
            {
                iTotalRecords = myList.Count(),
                iTotalDisplayRecords = myList.Count(),
                aaData = dataToReturn,
            }, JsonRequestBehavior.AllowGet);
        }

NoobProgrammer

I tried it, but the order column has value 0, but order dir is always null.
So I encounter server side error.
I tried modified it like this
if (model.order != null)
{
if (model.order[0].dir != null)
{
// in this example we just default sort on the 1st column
sortBy = model.columns[model.order[0].column].data;
sortDir = model.order[0].dir.ToLower() == "asc";
}
}

But, when i clicked the sort arrow in datatable, it called the server action but the
model.order[0].dir is always null. Not only that, the search function is not working as well, it looks like the json posted to the action is always the same default valu which make order and search not functioning.
Help me please

colin

@NoobProgrammer We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

catch

In the particular above example in the following code,
if (model.order != null)
{
// in this example we just default sort on the 1st column
sortBy = model.columns[model.order[0].column].data;
sortDir = model.order[0].dir.ToLower() == "asc";
}

when the page load by default I need sort on the second column. I had implemented the above way and working fine, but the issue is on page load I need to sort on the second column, model.order[0].column **always get '0' by default **and always sorting on first column. can you please help me on this.
Thanks

colin

@catch - I'm not following, but you can set the default order with order. If that doesn't help, we're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin