jQuery Datatables Wrapper

Introduction

The jQuery DataTables plug-in is an excellent client-side component that can be used to create rich-functional tables in the web browser. This plug-in adds lot of functionalities to the plain HTML tables that are placed in web pages such as filtering, paging, sorting, changing page length, etc.es etc. But it need some efforts to integrate that plagin in MVC application.

Imagine you want to edit, delete record on some page in DataTables and after that actions you may want to return to the same page. Supposing also that from different datasource you want to put different columns into DataTable.

To achive that in mvc application you should to make and handle "Edit", "Delete" buttons, remember current page, filter string, sorted column, get informatioln from somewhere about columns you want to place into DataTable. To do all of that of cause need some your efforts in Java Script and C# code.

This article shows on the example of symple Foreign banks system how to facilitate that process.

Using the code

There is the code to realise View page you see on the picture.

@using BankRegistry.Models
@using BankRegistry.Class
@model RcBnk
@{
    ViewBag.Title = "Довідник іноземних банків";
}
 
<h2>Перелік іноземних банків</h2>
 
@using (Html.BeginForm("Create", "EditBank",
    new Position(ViewBag.cp), FormMethod.Get))
    {
        if (((Infrastructure)ViewBag.cp).IsAdmin)
        {
            @*  Html Helper to to generate add button  *@
            @Html.AddRecord();
        }
    }
@*  Html Helper to to generate HTML Tag for Table tag for nessesary columns from table source   *@
@(Html.DataTable<RcBnk, RcBnkMetaData>(ViewData))
<div style="width:300;">
<link href="~/Content/css/jquery.dataTables.min.css" rel="stylesheet" />
@section Scripts
{
    <script src="~/Scripts/datatable_ext/table_init.js"></script>
    <script type="text/javascript">
    $(document).ready(function () {
        @*  Html Helper to to generate HTML java script code for call DataTables and make hundler for "edit" and "delete" buttons   *@
        @(BankRegistry.Class.DataTableHelper.DataTableInit<RcBnk, RcBnkMetaData>(
                    ViewData,
                    @* Infrastructure class  *@
                    ViewBag.cp,
                    @*  Controler and action to load data from datasource *@
                    "/Banks/LoadTable",
                    @*  key field *@
                    "B010",
                    @*  Controler and action to edit current row *@
                    "/EditBank/EditBankRecord",
                    @*  Controler and action to delete current row *@
                    "/Banks/DeleteRecord",
                    ((Infrastructure)ViewBag.cp).IsAdmin
                    ))
    }
    )
</script>
 }
</div>

To mark columns I want to show in DataTable I use user attribute DataTableColumnAttribute.

[AttributeUsage(AttributeTargets.Method | AttributeTargets.Property | AttributeTargets.Field | AttributeTargets.Parameter, AllowMultiple = false)]
  sealed public class DataTableColumnAttribute : Attribute
  {
      public DataTableColumnAttribute(bool active, string name="")
      {
      }
  }

Then I marks necessary columns in model.

[MetadataType(typeof(RcBnkMetaData))]
    public partial class RcBnk
    {
    }
    public class RcBnkMetaData
    {
        [Key]
        [Display(Name = "Код")]
        [DataTableColumnAttribute(true)]
        public string B010 { get; set; }
        [Display(Name = "Країна")]
        [Required]
        [DataTableColumnAttribute(active: true, name: "K0401")]
        public string K040 { get; set; }
        [DataTableColumnAttribute(true)]
        public string SWIFT { get; set; }
        [Display(Name = "Назва")]
        [DataTableColumnAttribute(true)]
        public string Name { get; set; }
        [Display(Name = "Місцезнаходження")]
        [DataTableColumnAttribute(true)]
        public string Location { get; set; }
        [Display(Name = "Статус")]
        public int Status { get; set; }
        public int InQueue { get; set; }
        [Display(Name = "Користувач")]
        public int UserID { get; set; }
        [Display(Name = "Дата заведення")]
        [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
        public System.DateTime EntryTime { get; set; }
        [Display(Name = "Файл імпорту")]
        public Nullable<int> ImpFile { get; set; }
        [Display(Name = "Файл експорту")]
        public Nullable<int> ExpFile { get; set; }
        [Display(Name = "Користувач, який останеній коригував")]
        [ReadOnly(true)]
        public Nullable<int> UpdUserID { get; set; }
        [Display(Name = "Дата останнього коригування")]
        [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
        public Nullable<System.DateTime> LastUpdated { get; set; }
        public Nullable<int> CountryID { get; set; }
    }

There is the code of Html.DataTable and DataTableInit. Both of them read information about necessary columns from metadata. So I can place that information in one place.

DataTable:

public static IHtmlString DataTable<T,metatData>(this HtmlHelper helper, ViewDataDictionary<T> Model)
       {
 
           TagBuilder table = new TagBuilder("table id=\"tblGreed\" class=\"table table-striped table-bordered\" cellspacing=\"0\" data-page-length='10'");
           TagBuilder tableThead = new TagBuilder("thead");
           TagBuilder tableTr = new TagBuilder("tr");
 
           var type = typeof(metatData);
           TagBuilder tableTh;
           foreach (var property in Model.ModelMetadata.Properties)
           {
               var memInfo = type.GetMember(property.PropertyName);
               if (memInfo.Length == 0) continue;
               var attributes = memInfo[0].GetCustomAttributes(typeof(BankRegistry.Controllers.DataTableColumnAttribute), true);
               if (attributes.Count() > 0)
               {
                   var attributeName = memInfo[0].GetCustomAttributes(typeof(System.ComponentModel.DataAnnotations.DisplayAttribute), true);
                   string colname = attributeName.Length > 0 ? 
                       ((System.ComponentModel.DataAnnotations.DisplayAttribute)attributeName[0]).Name :
                       property.PropertyName;
                   tableTh = new TagBuilder("th");
                   tableTh.InnerHtml = colname;
                   tableTr.InnerHtml += tableTh;
              }
           }
           tableTh = new TagBuilder("th");
           tableTr.InnerHtml += tableTh;
 
           tableThead.InnerHtml += tableTr;
           table.InnerHtml += tableThead;
           return new HtmlString(table.ToString());
       }

DataTableInit:

public static IHtmlString DataTableInit<T,metaData>
                                  (ViewDataDictionary<T> Model,
                                   Infrastructure cp,
                                   string LoadTable="",
                                   string KeyColumn="",
                                   string EditUrl="",
                                   string DeleteUrl = "",
                                   bool   FullAccess = true
                                   )
       {

           System.Text.StringBuilder sb = new System.Text.StringBuilder();
           sb.Append("var ColumnsArray = new Array;");
           var type = typeof(metaData);

           foreach (var property in Model.ModelMetadata.Properties)
           {
               var memInfo = type.GetMember(property.PropertyName);
               if (memInfo.Length == 0) continue;
               var attributes = memInfo[0].GetCustomAttributes(typeof(BankRegistry.Controllers.DataTableColumnAttribute), true);
               if (attributes.Count() > 0)
               {
                   sb.Append("ColumnsArray.push({data: \"" + property.PropertyName + "\", autoWidth: true});");
                   sb.Append(Environment.NewLine);

               }
           }
           if (FullAccess)
               sb.Append("ColumnsArray.push({data:\"\", width: \"20%\", orderable: false});");
           else
               sb.Append("ColumnsArray.push({data:\"\", width: \"10%\", orderable: false});");
           sb.Append(Environment.NewLine);
           sb.Append("var CurrentPage=" + cp.currentPage + ";");
           sb.Append(Environment.NewLine);
           sb.Append("var Sorting=" + cp.sorting + ";");
           sb.Append(Environment.NewLine);
           sb.Append("var Filter='" + cp.filter + "'" + ";");
           sb.Append(Environment.NewLine);
           sb.Append("var FullAccess='" + FullAccess + "'" + ";");
           sb.Append(Environment.NewLine);
           sb.Append("var table = DataTable(ColumnsArray, '" + LoadTable + "', CurrentPage, Sorting, Filter, FullAccess)" + ";");

           string editclick =
               "$('#tblGreed tbody').on('click', '#edit', function () {"+
               "var data = table.row($(this).parents('tr')).data();"+
               "$(location).attr('href', '" + EditUrl + "?id=' + data['" + KeyColumn + "']" +
                   "+ '&pageNumber=' + table.page.info().page"+
                   "+ '&filter=' + $('div.dataTables_filter input').val()"+
                   "+ '&sorting=' + table.order()[0][0]);});";

           sb.Append(editclick);

           string deleteclick =
               "$('#tblGreed tbody').on('click', '#delete', function () {"+
               "var conf = confirm('Вилучити запис ?');"+
               "if (conf==true)"+
               "{"+
                   "var data = table.row($(this).parents('tr')).data();"+
                   "$(location).attr('href', '" + DeleteUrl + "?id=' + data['" + KeyColumn + "']" +
                       "+ '&pageNumber=' + table.page.info().page"+
                       "+ '&filter=' + $('div.dataTables_filter input').val()"+
                       "+ '&sorting=' + table.order()[0][0]);"+
               "}})";
           sb.Append(deleteclick);
           return new HtmlString(sb.ToString());
       }

There is the Java Script code of patern for Jquery DataTable. There are necessary parameters for infrastructure to save page current position, filter, admin sign.

function DataTable(
                    ColumnsArray, // Array of columns to show
                    LoadTable,    // Url of mvc code to load date
                    CurrentPage,  //  Int number of current page
                    Sorting,      // int number of  column by with DataTable is sorted
                    Filter,       //  string filter by with DataTable is filtered
                    FullAccess    //  sign of full access to show update buttons 
                   )
{
 
    var texteditbutton = 'Коригувати'
    var deletetag = "<button id='delete' type='button' class='btn btn-default'>Вилучити</button>"
    if (FullAccess != 'True') {
        texteditbutton = 'Переглянути'
        deletetag = ''
    }
 
    var table = $('#tblGreed').DataTable({
        "lengthMenu": [[5, 10, 25, 50, -1], [5, 10, 25, 50, "Всі"]],
        iDisplayLength: -1,
        initComplete: function () {
            table.search(Filter).draw();
            table.page(CurrentPage).draw(false);
        },
        searching: true,
        ordering: true,
        order: [[Sorting, "desc"]],
        "bInfo": false,
        'bLengthChange': true,
        oLanguage: {
            oPaginate: {
                sNext: "Вперед",
                sPrevious: "Назад",
                sFirst: "Початок",
                sLast: "Кінець",
            },
            sLengthMenu: "Відображати _MENU_",
            sSearch: "Пошук: "
        },
        ajax: {
            url: LoadTable,
            contentType: "application/json"
 
        },
        columns: ColumnsArray,
        columnDefs: [{
            "targets": -1,
            "data": null,
            "defaultContent":
            "<div class='btn-group'>" +
            "<button id='edit' type='button' class='btn btn-default'>" + texteditbutton + "</button><span> </span>" +
            deletetag
        }]
    });
 
    $('#tblGreed thead th').each(function () {
        var title = $(this).text();
        $(this).html('<input type="text" placeholder="Пошук ' + title + '" />');
    });
    table.columns().eq(0).each(function (colIdx) {
        $('input', table.column(colIdx).header()).on('keyup change', function () {
            table
                .column(colIdx)
                .search(this.value)
                .draw();
        });
    })
 
    return table;
}

There is the code in mvc action to load data into DataTable. Here also information about columns also reads from model metadata.

[HttpGet]
        //[OutputCache(Duration=50, VaryByParam="none")]
        public ActionResult LoadTable()
        {
 
            var names = typeof(RcBnk).GetProperties()
                        .Select(property => property.Name)
                        .ToArray();
            var type = typeof(RcBnkMetaData);
 
            StringBuilder sb = new StringBuilder("new (");
 
            foreach (string property in names)
            {
                var memInfo = type.GetMember(property);
                if (memInfo.Length == 0) continue;
                var attributes = memInfo[0].GetCustomAttributes(typeof(BankRegistry.Controllers.<code>DataTableColumnAttribute</code>), true);
                if (attributes.Length == 0) continue;
                if (attributes.Count() > 0)
                {
                    string sbstring;
                    if (property == "K040") sbstring = "K0401.TXT as K040";
                    else
                        sbstring = property;
                    sb.Append(sbstring + ",");
                }
            }
            sb.Append(")");
            string select = sb.ToString();
            select = select.Remove(select.LastIndexOf(','),1);
 
 
            var jsonData = new
            {
                data = _db.Banks.All.Where(x => x.Status == 0).OrderBy(r => r.Name).
                    Select(select)
            };
            return Json(jsonData, JsonRequestBehavior.AllowGet);
        }

Summary

By that way you can handle any other data source in mvc application. All you need to mark necessary columns by attibute and write action for data update in controler. Using entity faramework it is rather simply.