The DataTables is one of the many JavaScript libraries we can use to enrich the tables in our web projects. In its basic implementation it allows the user to view, filter and order the data with ease. And better yet, without so much extra work for us. And if you are working with more advanced scenarios, you can customize it without problems.

Let’s see below how to implement it in a simple ASP.NET MVC Core website.

Implementation

The normal implementation is relatively simple. All you need is a data source and a View. However, my implementation has some extra things to consider since I am going for a more flexible scenario. The whole idea is to have the backend do most of the data work and leave the library with the formatting. And for that we will use:

1. Data source – No matter which. Memory, SQL Server, Oracle. Your choice;
2. Models and ViewModels – We need some specific objects to make our job easier;
3. Attributes – Important if we want to control the columns in the DataTable;
4. Service, Helpers – Used to transform the raw data into something we can use;
5. Reflection – Because we want the implementation to be as dynamic as possible;
6. JavaScript configuration – Important as we will need to customize a bit the implementation;
7. The API – We will load the data via Ajax, so we need a small API to work with;
8. A View – Because we need to show the DataTable at some point

Data source

If you need to implement a data table it means you have a data source of some sort. In this demo we are going to use in-memory data but it could easily come from any kind of database. Here it is:

using ReportsWeb.Models;
using System;
using System.Collections.Generic;
using System.Linq;

namespace ReportsWeb.Repository
{
    public sealed class PersonRepository
    {
        public IEnumerable<Person> GetPeopleReadOnly()
        {
            return new List<Person>
            {
                new Person {
                    FullName = "John Doe",
                    PreferredName = "John",
                    Email = "john.doe@mail.com",
                    Phone = "555-0000",
                    DateOfBirth = new DateTime(2000, 6, 30),
                    Status = true
                },
                new Person {
                    FullName = "Mary Sue",
                    PreferredName = "Mary",
                    Email = "mary.sue@mail.com",
                    Phone = "555-0001",
                    DateOfBirth = new DateTime(1987, 4, 22),
                    Status = true
                },
                new Person {
                    FullName = "Lily Code",
                    PreferredName = "Lily",
                    Email = "lily.code@mail.com",
                    Phone = "555-0002",
                    DateOfBirth = new DateTime(1976, 1, 7),
                    Status = true
                },
                new Person {
                    FullName = "Amy Trefl",
                    PreferredName = "Amy",
                    Email = "amy.trefl@mail.com",
                    Phone = "555-0003",
                    DateOfBirth = new DateTime(1950, 10, 14),
                    Status = true
                }
            };
        }

        public int Count()
        {
            return GetPeopleReadOnly().Count();
        }
    }
}

As you can see the PersonRepository should return all people (read only) and return the total amount of people in the imaginary “People table”.

Models and ViewModels

Some of these objects should help with data binding, others to help with the data formatting. The most important are:

DataParameters

We will use it to help with the data binding coming from the UI. It contains all we need to filter and order our data.

using Newtonsoft.Json;
using System.Collections.Generic;

namespace ReportsWeb.Models.DataTable
{
    public sealed class DataParameters
    {
        [JsonProperty(PropertyName = "draw")]
        public int Draw { get; set; }

        [JsonProperty(PropertyName = "columns")]
        public List<DataColumn> Columns { get; set; }

        [JsonProperty(PropertyName = "order")]
        public List<DataOrder> Order { get; set; }

        [JsonProperty(PropertyName = "start")]
        public int Start { get; set; }

        [JsonProperty(PropertyName = "length")]
        public int Length { get; set; }

        [JsonProperty(PropertyName = "search")]
        public Search Search { get; set; }
    }
}

PersonTableViewModel

Used to shape the Person data coming from the data source into exactly what we need for the UI. It uses some custom attributes to help shaping the data and also some static methods to help with ordering and filtering.

using ReportsWeb.Attributes;
using ReportsWeb.Helpers;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace ReportsWeb.Models.DataTable
{
    public class PersonTableViewModel
    {
        [ShowOnDataTable(0, "Full name"), Searchable, Orderable]
        public string FullName { get; set; }

        [ShowOnDataTable(1, "Preferred name"), Searchable, Orderable]
        public string PreferredName { get; set; }

        [ShowOnDataTable(2), Searchable, Orderable]
        public string Phone { get; set; }

        [ShowOnDataTable(3, "E-mail"), Searchable, Orderable]
        public string Email { get; set; }

        [ShowOnDataTable(4), Searchable, Orderable]
        public string Age { get; set; }

        [ShowOnDataTable(5), Searchable, Orderable]
        public string Status { get; set; }

        public static void Filter(ref IEnumerable<PersonTableViewModel> items, string searchTerm)
        {
            if (!string.IsNullOrWhiteSpace(searchTerm))
            {
                var searchableProperties = typeof(PersonTableViewModel).GetProperties().Where(p => Attribute.IsDefined(p, typeof(Searchable)));

                var lambda = PrepareFilter(searchTerm, searchableProperties);
                items = items.Where(lambda.Compile());
            }
        }

        public static void Order(ref IEnumerable<PersonTableViewModel> items, int orderColumn, string orderDirection)
        {
            try
            {
                switch (orderColumn)
                {
                    case 0:
                        items = orderDirection == "asc" ? items.OrderBy(o => o.FullName) : items.OrderByDescending(o => o.FullName);
                        break;
                    case 1:
                        items = orderDirection == "asc" ? items.OrderBy(o => o.PreferredName) : items.OrderByDescending(o => o.PreferredName);
                        break;
                    case 2:
                        items = orderDirection == "asc" ? items.OrderBy(o => o.Phone) : items.OrderByDescending(o => o.Phone);
                        break;
                    case 3:
                        items = orderDirection == "asc" ? items.OrderBy(o => o.Email) : items.OrderByDescending(o => o.Email);
                        break;
                    case 4:
                        items = orderDirection == "asc" ? items.OrderBy(o => o.Age) : items.OrderByDescending(o => o.Age);
                        break;
                    case 5:
                        items = orderDirection == "asc" ? items.OrderBy(o => o.Status) : items.OrderByDescending(o => o.Status);
                        break;
                    default:
                        items = items.OrderBy(o => o.FullName);
                        break;
                }
            }
            catch
            {
                throw;
            }
        }

        private static Expression<Func<PersonTableViewModel, bool>> PrepareFilter(string searchTerm, IEnumerable<PropertyInfo> searchableProperties)
        {
            var expressionFilter = new List<ExpressionFilter>();

            foreach (var property in searchableProperties)
            {
                expressionFilter.Add(new ExpressionFilter
                {
                    PropertyName = property.Name,
                    Operation = Operations.Contains,
                    Value = searchTerm
                });
            }

            return ExpressionBuilder.GetExpression<PersonTableViewModel>(expressionFilter, LogicalOperators.Or);
        }
    }
}

TableViewModel

This is the DataTable itself.

using Newtonsoft.Json;
using System.Collections.Generic;

namespace ReportsWeb.Models.DataTable
{
    public class TableViewModel
    {
        [JsonProperty(PropertyName = "draw")]
        public int Draw { get; set; }

        [JsonProperty(PropertyName = "recordsTotal")]
        public int RecordsTotal { get; set; }

        [JsonProperty(PropertyName = "recordsFiltered")]
        public int RecordsFiltered { get; set; }

        [JsonProperty(PropertyName = "data")]
        public List<List<string>> Data { get; set; }
    }
}

Attributes

Since I am generating the DataTable information on the backend I decided to create some custom attributes to help me with it.

• Orderable – In case the property has this attribute it means we can order our data;
• Searchable – The property containing this attribute will be used in the search field;
• ShowOnDataTable – It allows the property to be shown in the UI. You can also use it to set the order it will appear and a custom column name.

Service and Helpers

The Service is just a small mock of a Service Layer but it could be anything depending on the architecture you are following in your project. The most important is to create a specific method which return values formatted for the DataTable, as you can see below:

public (IEnumerable<PersonTableViewModel> Data, int Count) GetPeopleForDataTable(DataParameters parameters)
{
    var items = personRepository.GetPeopleReadOnly()
                            .Select(x => new PersonTableViewModel
                            {
                                FullName = x.FullName,
                                PreferredName = x.PreferredName,
                                Phone = x.Phone,
                                Email = x.Email,
                                Age = (DateTime.Now.Subtract(x.DateOfBirth).Days / 365).ToString(),
                                Status = x.Status == true ? "Active" : "Inactive"
                            });

    if (!string.IsNullOrWhiteSpace(parameters.Search.Value))
    {
        PersonTableViewModel.Filter(ref items, parameters.Search.Value);
    }

    PersonTableViewModel.Order(ref items, parameters.Order[0].Column, parameters.Order[0].Dir);

    return (items, personRepository.Count());
}

Notice that:

• The method accepts an argument of type DataParameters, which contains the values coming from the UI control;
• The static methods of PersonTableViewModel are being used to filter and order the data;
• The return type is a tuple.

As for the Helpers I have 2 classes. The first one is the ExpressionBuilder (bad name, I know), which will help us to build the expression queries for filtering. I am using because since the query is handling the data in-memory. Another scenario where the ExpressionBuilder would be useful is when we are using Entity Framework as we would dynamically build the query before sending to the database.

The other helper is the DataTableHelper. It will prepare both header and content for the UI based on the custom attributes we have in each property of PersonTableViewModel.

using ReportsWeb.Attributes;
using ReportsWeb.Models.DataTable;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Reflection;

namespace ReportsWeb.Helpers
{
    public static class DataTableHelper
    {
        public static TableViewModel BuildDataTable(IEnumerable<PersonTableViewModel> listItems, int recordsCount)
        {
            var listString = new List<List<string>>();

            // Get the properties which should appear in the DataTable
            var properties = GetProperties(listItems.FirstOrDefault());

            foreach (PersonTableViewModel item in listItems)
            {
                var listProperties = PrepareDataTable(properties, item).ToList();

                listString.Add(listProperties);
            }

            return new TableViewModel
            {
                Data = listString,
                RecordsTotal = 0, //TODO: Add total here
                RecordsFiltered = recordsCount,
                Draw = 0
            };
        }

        public static IEnumerable<string> BuildDataTableHeader(PersonTableViewModel tableViewModel)
        {
            var headerString = new List<string>();

            // Get the properties which should appear in the DataTable
            var properties = GetProperties(tableViewModel);

            foreach (var property in properties)
            {
                var listProperties = WebUtility.HtmlEncode(tableViewModel.GetType()
                                                                         .GetProperty(property.Name)
                                                                         .GetCustomAttributes(false)
                                                                         .OfType<ShowOnDataTable>().First().ColumnName
                                                           ?? property.Name);

                // VanityId must *always* be the last property
                //listProperties.Add(item.VanityId.ToString());

                headerString.Add(listProperties);
            }

            return headerString;
        }


        private static IOrderedEnumerable<PropertyInfo> GetProperties(PersonTableViewModel item)
        {
            return item.GetType().GetProperties()
                                 .Where(p => Attribute.IsDefined(p, typeof(ShowOnDataTable)))
                                 .OrderBy(o => o.GetCustomAttributes(false).OfType<ShowOnDataTable>().First().Order);
        }

        private static IEnumerable<string> PrepareDataTable(IOrderedEnumerable<PropertyInfo> properties, PersonTableViewModel item)
        {
            var listProperties = new List<string>();

            foreach (var property in properties)
            {
                listProperties.Add(GetValue(property, item));
            }

            return listProperties;
        }

        private static string GetValue(PropertyInfo property, PersonTableViewModel item)
        {
            return WebUtility.HtmlEncode(item.GetType()
                                             .GetProperty(property.Name)
                                             .GetValue(item)?.ToString())
                                         ?? string.Empty;
        }
    }
}

Reflection

As you could see I am using a little bit of reflection to get the properties and custom attributes. In this way we would be able to add and remove columns by simply editing the PersonTableViewModel without breaking the UI.

JavaScript configuration

As of now, there is no web without JavaScript. And since DataTables is a JavaScript library we need to do a small configuration. Here we will enable a couple of things:

$(document).ready(function () {
    $("#table-list").DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": {
            type: "POST",
            url: "home/getpeopledata"
        }
    });
});

• processing – Enables the process indicator;
• serverSide – Enables server-side processing of data;
• ajax – Loads the data from an Ajax source. In our case a dedicated method in the HomeController.

The API

There are 2 things we need to make it happen in order to make the DataTable look good. The first is to setup the table headers. We will achieve that by returning a List<string> values when we call the page. That is done by using the BuildDataTableHeader method we made in the DataTableHelper, as seen below:

public IActionResult Index()
{
    ViewData["Title"] = "Home Page";
    var header = DataTableHelper.BuildDataTableHeader(new PersonTableViewModel());
    return View(header);
}

The second is to return the content data. We will create a specific method for that and name it GetPeopleData:

[HttpPost]
public IActionResult GetPeopleData(DataParameters parameters)
{
    var items = personService.GetPeopleForDataTable(parameters);

    var dataTable = DataTableHelper.BuildDataTable(items.Data, items.Count);
    dataTable.Draw = parameters.Draw;

    return Ok(dataTable);
}

The idea here is to load the data and pass it into the BuildDataTable method in order to make it compatible with the DataTable library. The library, then, will get the data we are returning and format it into the HTML table.

A View

Our view should be very simple – a <table> tag, a CSS call and some JavaScript configuration. However, since we are aiming for some flexibility, let’s tweak the View a little bit:

@model List<string>
@using System.Text

@section Styles {
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.css">
}

@{
    var tableHeader = new StringBuilder();

    @foreach (var item in Model)
    {
        tableHeader.Append("<th>");
        tableHeader.Append(item);
        tableHeader.Append("</th>");
    }
}

<div>
    <table id="table-list" class="table table-striped table-bordered">
        <thead>
            <tr>
                @Html.Raw(tableHeader.ToString())
            </tr>
        </thead>
        <tfoot>
            <tr>
                @Html.Raw(tableHeader.ToString())
            </tr>
        </tfoot>
    </table>
</div>

@section Scripts{
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.js"></script>
    <script type="text/javascript" src="~/js/index.js"></script>

From the top of the View:

• @model List<string> – Since we need to load the table headers in the beginning, we are passing it in the first request.
• The @foreach loop – Not the best place to do this but we need to prepare the header and footer tags in order to use it in the DataTable
• <table> – A the simple HTML table with some styles and the header/footer we just created
• @section Scripts – With the library and our JavaScript configuration

Don’t forget that the data coming via Ajax should match our columns perfectly, thanks to the DataTable script and the preparation we made in the backend.

Conclusion

And here we have, ladies and gentleman. A nice table with search and ordering:

The demo may seem overly complex for such a small thing but it scales quite well. I am using it both in my website and in a project at work with zero problems so far. Of course, some tweaks will be necessary when adapting to your project but it should be a no brainer. And once you grasp the concept it’s very easy to replicate it.