jQuery DataTable Integration with Server Side Processing Using Dynamic LINQ

• Download source - 14 KB

Introduction

While integrating jQuery Datatable, first of all we have to ready with JSON results sets then after require initializing jQuery DataTable with that result. It works smoothly if number of records count or result content length/size is very less.

Grid will able to initialize after JSON result loaded completely to client browser. All functionalities will automatically work! Because, it’s default feature of jQuery Datatable E.g. Global Text Search in all columns, Individual Column Search, Sorting etc

If we are going to deal jQuery Datatable with large numbers of records set, then above approach will not work well, because it will consume too much time to fetch from database as well load data in client machine. Means data transmission time will automatically increase from Sql Server to IIS Server and IIS server to Client machine (Browser). You can easily feel grid performance has going to slow down.

Instead of fetching all records from ms sql database, require to fetch/retrieve only few records as per grid page size. Again, we have to write code for all primary functions, like Search records in all column called here Global text search, Search text in Individual/specific column, order by operation(ascending/descending) on column and finally take n-number of records basis on grid page length, So obviously LOC(Line of code) will more. It will be true for both of case either using Entity Framework or Store Procedure (SP).

Here everything is fine but you need to write code with many numbers of lines, since Global Text Search or Individual Column Search function require handling each column. When table design is going change or newly column has to introduce then you have to recode for it. All EF command is strongly bind with expressions for this scenario.

So I have thought to prepare extension plug and play method which will take care for every case e.g. Database table design has change or newly column has introduce. This extension method has tested handle with Int32, String and DateTime? Datatype using System.Dynamic.Linq(Version 1.0.6132.35681) and EntityFramework (Version 6.0.0.0)

I have not require to code for each column.

Server Side Processing - Extension Methods

I have prepared following primary function to handle all kind of server side processing.

ToGlobalSearchInAllColumn()

This function requires DTParameters as parameter. It is very common strongly type model which is automatically initialize at time interaction with jQuery Datatable post method in controller.

Where condition: string raw command is different basis on data type E.g.Int32, String and DateTime?

So need to detect its data type before preparing where condition query. I have used System.Reflection to find out all get;set; properties and concatenate string as query building.

ToIndividualColumnSearch()

This method is helping to make query as filter in specific column, so we need to find out column name as well search text from DTParameters param.I have listed all column name and its search text from it by checking search text string.IsNullOrEmpty()

I have again used reflection to detect all properties datatype and made where condition (raw sql query) accordingly.

ToSorting()

This method is useful to sort records. Get the sorting column name and it direction (asc/desc) using DTParameters param.

Param.SortOrder is name of sorting columns and Param.Order[0].Dir will return its direction.

ToPagination()

This function’s job is to take n-numbers of records basis on grid page length size.

We can detect start index as well length of records using property Param.Start and Param.Length respectively.

Let’s start with coding self-explanation along with comment summary.

Procedure/Steps

Step 1: Install Entity Framework using NuGet command 'Install-Package EntityFramework'

Step 2: Set Connection String for Your Database in Web.Config

<connectionStrings>
   <add name="BookInformation" connectionString="Data Source=.;Initial Catalog=BookInformation;Persist Security Info=True;User ID=sa;Password=XXXXX" providerName="System.Data.SqlClient" />
 </connectionStrings>

Step 3: Enable Migration for Code First Approach using NuGet command 'Enable-Migrations'

Step 4: Prepare for Context as well entity model for Database design

public class Category
    {
        public int Id { get; set; }
        public string CategoryName { get; set; }
        public string Description { get; set; }
    }

public class Book
    {
        public int Id { get; set; }
        public string BookName { get; set; }
        public string Author { get; set; }
        public string About { get; set; }        
        public DateTime? CreatedOn { get; set; }
        [ForeignKey("CategoryId")]
        public virtual Category Category { get; set; }
        public int CategoryId { get; set; }
    }

 public class BookContext : DbContext
    {
        public BookContext() : base("name=BookInformation") {
            Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
        }
        public DbSet<Category> Category { get; set; }
        public DbSet<Book> Book { get; set; }
    }

Step 5: Trigger NuGet Command for  Table Creation

1. Add-Migration InitialCreate
2. Update-Database

Creating custom extension methods which supports to Server side processing

public static IQueryable<T> ToGlobalSearchInAllColumn<T>(this IQueryable<T> table, DTParameters Param)
       {
           var GlobalSearchText = Param.Search != null && Param.Search.Value != null ? Param.Search.Value : string.Empty;
           if (!string.IsNullOrEmpty(GlobalSearchText))
           {
               // return BooksData.Where(x => x.BookId.ToString() == GlobalSearchText || x.BookName.Contains(GlobalSearchText) || x.Category.Contains(GlobalSearchText));
               StringBuilder WhereQueryMaker = new StringBuilder();
               Type BookType = table.FirstOrDefault().GetType();
               DateTime CreatedOn;
               foreach (PropertyInfo prop in BookType.GetProperties())
               {
                   if (prop.PropertyType == typeof(System.String))
                       WhereQueryMaker.Append((WhereQueryMaker.Length == 0 ? "" : " OR ") + prop.Name + ".Contains(@0)");
                   else if (prop.PropertyType == typeof(System.Int32))
                       //if data type is integer then you need to parse to ToString() to use Contains() function
                       WhereQueryMaker.Append((WhereQueryMaker.Length == 0 ? "" : " OR ") + prop.Name + ".ToString().Contains(@0)");
                   else if (prop.PropertyType == typeof(System.DateTime?) && DateTime.TryParseExact(GlobalSearchText, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out CreatedOn))
                       //Date object comparison required to follow DateTime(2018,08,15) as format. so need to supply yyyy, MM, dd value on it.
                       WhereQueryMaker.Append((WhereQueryMaker.Length == 0 ? "" : " OR ") + prop.Name + "== DateTime(" + CreatedOn.Year + ", " + CreatedOn.Month + ", " + CreatedOn.Day + ")");
               }
               return table.Where(WhereQueryMaker.ToString(), GlobalSearchText);
           }
           return table;
       }

       public static IQueryable<T> ToIndividualColumnSearch<T>(this IQueryable<T> table, DTParameters Param)
       {
           if (Param.Columns != null && Param.Columns.Count() > 0 && table.FirstOrDefault() != null)
           {
               Type EntityType = table.FirstOrDefault().GetType();
               var Properties = EntityType.GetProperties();
               DateTime CreatedOn;
               int Id;
               //listing necessary column where individual columns search has applied. Filtered with search text as well it data types
               Param.Columns.Where(w => w.Search != null &&
               !string.IsNullOrEmpty(w.Search.Value)).ToList().ForEach(x =>
               {
                   //x.Data is column name as string format coming from Param object.
                   //x.Search.Value specific search text applied on column
                   //Added extra check on column name coming from Param and its data type on search text.
                   if (int.TryParse(x.Search.Value, out Id) && Properties.Count(p => p.Name == x.Data && p.PropertyType == typeof(System.Int32)) > 0)
                       table = table.Where(x.Data + ".ToString().Contains(@0)", x.Search.Value);
                   else if (DateTime.TryParseExact(x.Search.Value, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out CreatedOn) && Properties.Count(p => p.Name == x.Data && p.PropertyType == typeof(System.DateTime?)) > 0)
                       table = table.Where(x.Data + "==DateTime(" + CreatedOn.Year + ", " + CreatedOn.Month + ", " + CreatedOn.Day + ")");
                   else if (Properties.Count(p => p.Name == x.Data && p.PropertyType == typeof(System.String)) > 0)
                       table = table.Where(x.Data + ".Contains(@0)", x.Search.Value);
               });
           }
           return table;
       }

       public static IQueryable<T> ToSorting<T>(this IQueryable<T> table, DTParameters Param)
       {
           //Param.SortOrder return sorting column name
           //Param.Order[0].Dir return direction as asc/desc
           return table.OrderBy(Param.SortOrder + " " + Param.Order[0].Dir).AsQueryable();
       }

       public static IQueryable<T> ToPagination<T>(this IQueryable<T> table, DTParameters Param)
       {
           //Param.Start return start index
           //Param.Length page length
           if (Param.Length > 0)
               return table.Skip(Param.Start).Take(Param.Length);
           else return table.Skip(Param.Start);
       }

Step 6: jQuery Datatable initilization on cshtml page

<head>
    <link href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css" rel="stylesheet" />
    <script src="https://code.jquery.com/jquery-3.3.1.js"></script>
    <script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
    <script>
        $(function () {
            var table = $('#example').DataTable({
                "serverSide": true,
                "ajax": {
                    "url": "/Book/LoadData",
                    "type": "POST",
                    "datatype": "json"
                },
                "columns": [{
                    "data": "BookId",
                },
                {
                    "data": "BookName",
                },
                {
                    "data": "Author",
                },
                {
                    "data": "AboutIt",
                },
                {
                    "data": "CategoryName",
                },
                {
                    "data": "CategoryDescription",
                },
                {
                    "data": "CreatedOn",
                }]
            })
            // Setup - add a text input to each footer cell
            $('#example tfoot th').each(function () {
                var title = $(this).text();
                $(this).html('<input type="text" placeholder="Search ' + title + '" />');
            });
            // Apply the search
            table.columns().every(function () {
                var that = this;
                $('input', this.footer()).on('keyup change', function () {
                    if (that.search() !== this.value) {
                        that
                            .search(this.value)
                            .draw();
                    }
                });
            });
        });
    </script>
</head>
<br/>
<table class="table" id="example">
    <thead>
        <tr>
            <th>Id</th>
            <th>BookName</th>
            <th>Author</th>
            <th>About</th>
            <th>Category</th>
            <th>CategoryDescription</th>
            <th>CreatedOn</th>
        </tr>
    </thead>
    <tbody></tbody>
    <tfoot>
        <tr>
            <th>Id</th>
            <th>BookName</th>
            <th>Author</th>
            <th>About</th>
            <th>Category</th>
            <th>CategoryDescription</th>
            <th>CreatedOn</th>
        </tr>
    </tfoot>
</table>

Step 7: Prepare post method in controller to handle jQuery datatable's actions

[HttpPost]
 public ActionResult LoadData(DTParameters param)
 {
     int Count;
     var Result = RetrieveData(param, out Count);
     DTResult<BookInfo> result = new DTResult<BookInfo>
     {
         draw = param.Draw,
         data = Result,
         recordsFiltered = Count,
         recordsTotal = Count
     };
     JsonSerializerSettings jsSettings = new JsonSerializerSettings();
     jsSettings.DateFormatString = "dd/MM/yyy";
     jsSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;
     string json = JsonConvert.SerializeObject(result, jsSettings);
     return Content(json, "application/json");
 }

 private List<BookInfo> RetrieveData(DTParameters Param, out int Count)
 {
     using (var Context = new BookContext())
     {
         Count = Context.Book.Count();
         var BookData = Context.Book.Select(s => new BookInfo() { BookId = s.Id, BookName = s.BookName, Author = s.Author, AboutIt = s.About, CategoryName = s.Category.CategoryName, CategoryDescription = s.Category.Description,CreatedOn=s.CreatedOn }).AsQueryable();
         //Global Text Search
         var GlobalSearchFilteredData = BookData.ToGlobalSearchInAllColumn(Param);
         //Search Text in Specific or Individual
         var IndividualColSearchFilteredData = GlobalSearchFilteredData.ToIndividualColumnSearch(Param);
         //Sorting order
         var SortedFilteredData = IndividualColSearchFilteredData.ToSorting(Param);
         //Apply Pagination (Taking N number by page size)
         var SortedData = SortedFilteredData.ToPagination(Param).ToList();
         return SortedData;
     }
 }

Result/output

Points of Interest

• Always use IQueryable<T> until you need actual data from Sql Server to in memory. Means don't use .ToList() method until required final outcome/result. ToList() function will do fetching records directly from sql server.
• We can also make only one function with merging all primary function e.g. ToGlobalSearchInAllColumn(),ToIndividualColumnSearch(),ToSorting() and ToPagination()  together, so LOC will become only one line to process all kind of server side processing.
• It can be use as plug and play helper method and extention method LOC will remain same even n-number of column new introduce. Its all dyanamic.
• System.Dyanic.Linq where clause string format vary from Int32,System.String and System.DateTime?
• You can monitor raw sql query generated from EntityFramework internally, in Visul Studio Output window (Ctrl+Alt+O) Need to write code in constructor in Context
   Database.Log = s => System.Diagnostics.Debug.WriteLine(s)