jQuery datatable server side processing in Web API C#

In this article, I am going to explain you about using jQuery Datatable Server side processing with C# and Web-API, it is quite common to use jQuery datatable in ASP.NET MVC  now- a days in web-application as it easy to use, and handle's a large amount of data easily in grid view.

This Solution is based on Web-API, you can refer to jQuery Datatable Server side processing in C# MVC without Web-API also.

So, let me show you Step by Step how can we create jQuery datatable using server-side processing in asp.net web api.

1.Open Visual Studio(I am using 2017 community version), go to file, create new project and name it.

2. Now select Web API project template for your solution

3.Right Click on Models Folder, Select "Add" -> "New Item" -> "Data" ->"ADO.NET Entity Data Model" -> Name it -> Click "Add"

4. Select "EF designer from Database" -> "Next" ->Click "New Connection" ->  Connect it with yout database  by entering your SQL server username/password and select database -> Click "ok"

5. Click "Next"-> Select Entity Framework version -> Click "Next" -> Select Tables -> Click "Finish"

6. Create Model class (SysDataTablePager) for handling paging/data of jQuery datatable, this model will accpet JSON data of jQuery datatables

public class SysDataTablePager<T>
    {
        public SysDataTablePager(IEnumerable<T> items,
            int totalRecords,
            int totalDisplayRecords,
            string sEcho)
        {
            aaData = items;
            iTotalRecords = totalRecords;
            iTotalDisplayRecords = totalDisplayRecords;
            this.sEcho = sEcho;
        }



        public IEnumerable<T> aaData { get; set; }

        public int iTotalRecords { get; set; }

        public int iTotalDisplayRecords { get; set; }

        public string sEcho { get; set; }
    }

7. Create a Web API for Datatable, Right click on Controller Folder -> Select "Web Api 2 Controller -Empty" option, Click "Add", Name it(DataTableAPIController) and Click "ADD"

8. Create a Model class for List of the data

 public class BlogsList
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string CategoryName { get; set; }
        public int PostCount { get; set; }
    }

Here is my database table screenshot which I am using in this article while explaining code

9. Create a Function to Get list of Blog table data from database using Ado.NET model in WEB API, so here is the C# code for it, which we will use in  API controller to fetch list of tables from database

BlogDbEntities context = new BlogDbEntities();
        // GET api/datatableapi
        public SysDataTablePager<BlogsList> Get()
        {
            NameValueCollection nvc = HttpUtility.ParseQueryString(Request.RequestUri.Query);
            string sEcho = nvc["sEcho"].ToString();
            string sSearch = nvc["sSearch"].ToString();
            int iDisplayStart = Convert.ToInt32(nvc["iDisplayStart"]);
            int iDisplayLength = Convert.ToInt32(nvc["iDisplayLength"]);

           
                //get total value count
                var Count = context.Blogs.Count();

                //get data from database
                var Customers = context.Blogs. //specify conditions if there is any using .Where(Condition)                             
                                 Select(d=> new BlogsList
                                 {
                                     Id = d.BlogId,
                                     Name= d.Name,
                                     CategoryName = d.CategoryName,
                                       PostCount=d.PostCount
                                 }).OrderBy(d=>d.Id).Skip(iDisplayStart).Take(10);

                //Create a model for datatable paging and sending data & enter all the required values
   var CustomerPaged = new SysDataTablePager<BlogsList>(Customers, Count, Count, sEcho);
                
             
            
               return CustomerPaged;
            

        }

9. In your Views->Home-> Index.cshtml page, use the code below to generate datatable, we will be using necessary .js files and .css files, with the required  HTML code to generate server-side jQuery data table

<link href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" rel="stylesheet" />

<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>

<table id="TableBlogList">
    <thead>
        <tr>
            <th>Name</th>
            <th>Category </th>
            <th>Post Count</th>
            <th>Actions</th>
       </tr>
    </thead>
    <tbody>
        
    </tbody>
</table>
<script>
$(function () {
    $ArticleCustomerDataTable = $("#TableBlogList").dataTable({
        "bServerSide": true, //make server side processing to true
        "sAjaxSource": '/api/DataTableAPI', //url of the Ajax source,i.e. web api method
        "sAjaxDataProp": "aaData", // data property of the returned ajax which contains table data
        "bProcessing": true,
        "bLengthChange": false,
        "sPaginationType": "full_numbers",//pagination type
        "aoColumns": [
              
                { "mData": "Name"},
                { "mData": "CategoryName" },
                { "mData": "PostCount" },
                {
                    "mData": "Id", "bSearchable": false, "bSortable": false, "sWidth": "40px",
                    "mRender": function (data) {
                        return '<button class="btn btn-primary" type="button" data-Id="' + data + '" >Edit</button>'
                    }
                }
        ]
    });
    });
    </script>

in the above js code you can see we are processing jquery Data table on the server side with Ajax & have provided its URL as api url.

10. Now Build and run your project using Visual Studio, on the home page you can see your generated Datatable using web API  in MVC as below

You can check, paging will be working, but sorting, searching won't be working as we haven't implemented code for it yet.

Note: You may get an error "The 'ObjectContent '1' type failed to serialize the response body for content type 'application/json; charset=utf-8'." so to resolve that issue please add this code in your Global.asax

GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings
    .ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;
            GlobalConfiguration.Configuration.Formatters
                .Remove(GlobalConfiguration.Configuration.Formatters.XmlFormatter);

11. Now we can move on to implement, sorting and searching in the above code, let's go back to web API controller method & do the necessary changes, i am commenting code to make it understandable

 public SysDataTablePager<BlogsList> Get()
        {
            NameValueCollection nvc = HttpUtility.ParseQueryString(Request.RequestUri.Query);
            string sEcho = nvc["sEcho"].ToString();
            string sSearch = nvc["sSearch"].ToString();
            int iDisplayStart = Convert.ToInt32(nvc["iDisplayStart"]);
            int iDisplayLength = Convert.ToInt32(nvc["iDisplayLength"]);

            //iSortCol gives your Column numebr of for which sorting is required
            int iSortCol = Convert.ToInt32(nvc["iSortCol_0"]);
            //provides your sort order (asc/desc)
            string sortOrder = nvc["sSortDir_0"].ToString();

            var Customers = new List<BlogsList>();
           
                //get total value count
            var Count = context.Blogs.Count();

          
            //Search query when sSearch is not empty
            if (sSearch != "" && sSearch != null) //If there is search query
            {

                Customers = context.Blogs.Where(a=>a.Name.ToLower().Contains(sSearch.ToLower()) || a.CategoryName.ToLower().Contains(sSearch.ToLower())).                          
                                     Select(d => new BlogsList
                                     {
                                         Id = d.BlogId,
                                         Name = d.Name,
                                         CategoryName = d.CategoryName,
                                         PostCount = d.PostCount
                                     }).ToList();
                // Call SortFunction to provide sorted Data, then Skip using iDisplayStart  
                Customers = SortFunction(iSortCol, sortOrder, Customers).Skip(iDisplayStart).Take(iDisplayLength).ToList();
            }
            else
            {
                //get data from database
                Customers = context.Blogs. //speficiy conditions if there is any using .Where(Condition)                             
                                     Select(d => new BlogsList
                                     {
                                         Id = d.BlogId,
                                         Name = d.Name,
                                         CategoryName = d.CategoryName,
                                         PostCount = d.PostCount
                                     }).ToList();

                // Call SortFunction to provide sorted Data, then Skip using iDisplayStart  
                Customers = SortFunction(iSortCol, sortOrder, Customers).Skip(iDisplayStart).Take(iDisplayLength).ToList();
            }

                //Create a model for datatable paging and sending data & enter all the required values
        var CustomerPaged = new SysDataTablePager<BlogsList>(Customers, Count, Count, sEcho);
             
            
               return CustomerPaged;
            

        }

        //Sorting Function
        private List<BlogsList> SortFunction(int iSortCol, string sortOrder, List<BlogsList> list)
        {

            //Sorting for String columns
            if (iSortCol == 1 || iSortCol == 0)
            {
                Func<BlogsList, string> orderingFunction = (c => iSortCol == 0 ? c.Name : iSortCol == 1 ? c.CategoryName : c.Name); // compare the sorting column

                if (sortOrder == "desc")
                {
                    list = list.OrderByDescending(orderingFunction).ToList();
                }
                else
                {
                    list = list.OrderBy(orderingFunction).ToList();

                }
            }
            //Sorting for Int columns
            else if(iSortCol ==2 )
            {
                Func<BlogsList, int> orderingFunction = (c => iSortCol == 2 ? c.PostCount : c.Id); // compare the sorting column

                if (sortOrder == "desc")
                {
                    list = list.OrderByDescending(orderingFunction).ToList();
                }
                else
                {
                    list = list.OrderBy(orderingFunction).ToList();

                }
            }
            
            return list;
        }

In the above code as you see I have created new function named as SortFunction, which takes, list and Sort order, and sorting column number, with the help of which we sort the list and return it back, now how to we got this Sort Order and Sorting column number?, here is the code for it

   //fetch jquery datatable query    
   NameValueCollection nvc = HttpUtility.ParseQueryString(Request.RequestUri.Query);
    //sEcho provider service request count
            string sEcho = nvc["sEcho"].ToString();
   /sSearch provides the query to search
            string sSearch = nvc["sSearch"].ToString();
   //iDisplayStart Provides list start number
            int iDisplayStart = Convert.ToInt32(nvc["iDisplayStart"]);

  //this provides display length of table it can be 10,25, 50
            int iDisplayLength = Convert.ToInt32(nvc["iDisplayLength"]);

            //iSortCol gives your Column numebr of for which sorting is required
            int iSortCol = Convert.ToInt32(nvc["iSortCol_0"]);
            //provides your sort order (asc/desc)
            string sortOrder = nvc["sSortDir_0"].ToString();

These are the details which jQuery datatable send to server when requesting data from it, which in turns helps us on server to know, which page we need to fetch, if there is any search query or not.

You can check request details in your web browser also, it may look something like this

So, click on 'Post Count' Column, and check the sorted data, in your table now

Now try to search some data, let's try "Test 5", output will be as below

So we have achieved to Show, Search, sort data using jQuery datatable server-side processing with Web API, we will continue this article, to show Create, Update, delete (CRUD) using Web Api and jQuery datatable (Part 2) (reading data part is shown in this article, remaining is in next article.)

If you have any doubts or questions, you can add question-related to this article on question section, linking to this page or comment below, You can also downlod the source code of the project.