Monday, May 5, 2014

JQuery Data Tables - Server Side Usage

The jQuery DataTable plugin can be used both client side and server side within any application. For most cases client side usage will be more than sufficient. However in cases where there are thousands of rows to be processed, loading the entire resultset into the DOM will be resource intensive. Hence in these cases Server-side scripting is required. Client-side scripting is easy and straightforward. However the usage of the plugin server side requires a little bit of coding effort on the developers part.


The Setup
Create a basic HTML page like the screenshot below.

<div id="noteTypesIndex" style="width: 100%; overflow: hidden;">
    <table id="noteTypes" cellspacing="0" cellpadding="0" class="datatable">
        <thead>
            <tr>
                <th>Name</th>
                <th>Active</th>
                <th>@*For details*@</th>
            </tr>
            <tr id="filterRow" class="filterRow">
                <th>Search Name</th>
                <th>Search Active</th>
                <th>@*For details*@</th>
            </tr>
        </thead>
        <tbody>
        </tbody>
    </table>
</div>

Add the Javascript to ensure that the datatable plugin is applied to the table that we just created.

<script type="text/javascript">
    var oNoteType;
    var noteTypeSettings;

    $(function () {
        oNoteType = $("#noteTypes").dataTable({
            "bServerSide": true,
            "sAjaxSource": "/Admin/NoteType/AjaxHandler",
            "bProcessing": true,
            "bColumnFilters": true,
            "bSortCellsTop": true,
            "bLengthChange": false,
            "iDisplayLength": 20,
            "sPaginationType": "simple_numbers",
            //"bScrollCollapse": false,
            //"bAutoWidth": false,
            "aoColumns": [
                /*NoteType*/{ "sWidth": "50%" },
                /*Active*/{
                    "sType": "html", "sWidth": "25%", "fnRender": function (oObj) {
                        if(oObj.aData[1]=='True')
                            return '<div class=\"active-icon\" title=\"Active\"><span style=\"display: none\">' + oObj.aData[1] + '</span></div>';
                        else
                            return '<div class=\"inActive\" title=\"Inactive\"><span style=\"display: none\">' + oObj.aData[1] + '</span></div>';
                    }
                },
                /*DetailIcon*/{
                    "bSortable": false, "sType": "html", "sWidth": "25%", "fnRender": function (oObj) {
                        return '<a href=\"Detail/' +
                        oObj.aData[2] + '\" class=\"viewDetail\" title=\"View Note Type\"></a>';
                    }}],
            "sDom": '<"top"f>rt<"bottom"piX><"clear">'
        }).columnFilter({
            sPlaceHolder: "head:after",
            bUseColVis: true,
            aoColumns: [
                { type: "text" },
                { type: "multi-select", values: ["True", "False"] }
            ]
        });
    });

</script>

The properties for Server Side processing should be set appropriately.

            "bServerSide": true,
            "sAjaxSource": "/Admin/NoteType/AjaxHandler",
            "bProcessing": true,

Create a class called DataTableParamModel as shown below

    public class DataTableParamModel
    {
        public string sEcho { get; set; }
        public string sSearch { get; set; }
        public int iDisplayLength { get; set; }
        public int iDisplayStart { get; set; }
        public int iColumns { get; set; }
        public int iSortingCols { get; set; }
        public string sColumns { get; set; }
    }

These are the properties that will be passed in by the handler in when an Ajax call is made to the controller.

The Code

Next setup a controller method in the relevant controller.

The first method will be a method that just returns the view.

        public ActionResult Index2()
        {
            return View();
        }

The next method will be the ajax call that will execute every time the data table is refreshed whether it is during page load, sort, filter or a search. This method will fetch data from the server through the appropriate mechanism (EF, SQL Call etc.). In our case it is through the manager which in turn calls the repositories via EF.

public ActionResult AjaxHandler(DataTableParamModel param)
        {
            var companyId = UserSecurity.CurrentCompanyId;
            var noteTypes = _noteTypeManager.GetNoteTypeData(companyId);
            var filteredNoteTypes = noteTypes;

            if (!string.IsNullOrEmpty(param.sSearch))
            {
                if (param.sSearch == "true")
                {
                    filteredNoteTypes = noteTypes.Where(n => n.Name.Contains(param.sSearch, StringComparison.OrdinalIgnoreCase) || n.Active).ToList();
                }
                else if (param.sSearch == "false")
                {
                    filteredNoteTypes = noteTypes.Where(n => n.Name.Contains(param.sSearch, StringComparison.OrdinalIgnoreCase) || !n.Active).ToList();
                }
                else
                {
                    filteredNoteTypes = noteTypes.Where(n => n.Name.Contains(param.sSearch, StringComparison.OrdinalIgnoreCase)).ToList();
                }
            }

            var sName = Request["sSearch_0"].ToString();
            var sActive = Request["sSearch_1"].ToString();

            if (!string.IsNullOrEmpty(sName))
            {
                filteredNoteTypes = noteTypes.Where(n => n.Name.Contains(sName, StringComparison.OrdinalIgnoreCase)).ToList();
            }

            if (!string.IsNullOrEmpty(sActive))
            {
                if (sActive.Equals("true", StringComparison.OrdinalIgnoreCase))
                    filteredNoteTypes = noteTypes.Where(n => n.Active).ToList();
                else
                    filteredNoteTypes = noteTypes.Where(n => !n.Active).ToList();
            }

            var displayedNoteTypes = filteredNoteTypes.Skip(param.iDisplayStart).Take(param.iDisplayLength).ToList();

            var result = from n in displayedNoteTypes
                         select new string[] { n.Name, n.Active.ToString(),n.Id.ToString() };

            return Json(new
            {
                sEcho = param.sEcho,
                iTotalRecords = noteTypes.Count,
                iTotalDisplayRecords = filteredNoteTypes.Count,
                aaData = result
            },
                JsonRequestBehavior.AllowGet);
        }

And your data table is now working server side. The individual column filters also work Server Side.

Note

  • The Multi-Select control is something that was created in house as an extension. This is not base functionality available in the data table.