How to create a server side processing JQuery.Datatables using ASP.NET REST-WEB API?

There are so many ways on how to hook up your JQuery.Datatables to your ASP.NET REST-WEB API controller via server side processing.   But I want a simple solution, I don’t want to add complicated classes to my MVC repository just to accommodate my JQuery.Datatables control.

A little background before we start; Server side processing is very important because your browser cannot handle huge data thrown at it by the server in a single transaction.  It will not only take longer for the browser to load the page, it might crash it due to memory constraints.  The solution is to process and filter the data at the server before sending it to your browser (client).

The first step is to create the models.

The first model is my data, in my example, it is the MstArticleCustomer (customer records)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace wfmis.Models
{
    public class MstArticleCustomer
    {
        public Int64 Id { get; set; }
        public Int64 ArticleId { get; set; }
        public string CustomerCode { get; set; }
        public string Customer { get; set; }
        public string Address { get; set; }
        public string ContactNumbers { get; set; }
        public string ContactPerson { get; set; }
        public string EmailAddress { get; set; }
    }
}

The next model is the Pager Model Wrapper Class.  This model will wrapped my data model to an acceptable JSON format of the  JQuery.Datatables control.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace wfmis.Models
{
    public class SysDataTablePager
    {
        public string sEcho { get; set; }
        public int iTotalRecords { get; set; }
        public int iTotalDisplayRecords { get; set; }
        public List aaData { get; set; }
    }
}

The next step is the javascript that invokes the JQuery.Datatables control.  I will not include the HTML code here to make this blog a little bit understandable. 🙂

        $(function () {
            $ArticleCustomerDataTable = $("#TableArticleCustomerList").dataTable({
                "bServerSide": true,
                "sAjaxSource": '/api/MstArticleCustomer',
                "sAjaxDataProp": "aaData",
                "bProcessing": true,
                "bLengthChange": false,
                "sPaginationType": "full_numbers",
                "aoColumns": [
                        {
                            "mData": "Id", "bSearchable": false, "bSortable": false, "sWidth": "40px",
                            "mRender": function (data) {
                                return '<button class="btn btn-primary" type="button">Edit</button>'
                            }
                        },
                        {
                            "mData": "Id", "bSearchable": false, "bSortable": false, "sWidth": "40px",
                            "mRender": function (data) {
                                return '<button class="btn btn-danger" type="button">Delete</button>'
                            }
                        },
                        { "mData": "CustomerCode", "sWidth": "150px" },
                        { "mData": "Customer" },
                        { "mData": "ContactNumbers", "sWidth": "300px" }
                ]
            });
        });

Of course you need some reading on how to create the javascript written above, but in a nutshell, it is just a simple script to create a JQuery.Datatables server side processing control with some rendered columns HTML tags for the Edit and Delete buttons.

The last step is the controller. My controller follows clean REST architecture, that is why the method is just pure Get.

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data.SqlTypes;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web;
using System.Web.Http;
using System.Web.Mvc;
using wfmis.Models;

namespace wfmis.Controllers
{
    public class MstArticleCustomerController : ApiController
    {

        private Data.wfmisDataContext data = new Data.wfmisDataContext();

        private SysSecurity secure = new SysSecurity();

        // GET api/MstArticleCustomer
        public Models.SysDataTablePager Get()
        {
            NameValueCollection nvc = HttpUtility.ParseQueryString(Request.RequestUri.Query);
            string sEcho = nvc["sEcho"].ToString();
            int iDisplayStart = Convert.ToInt32(nvc["iDisplayStart"]);

            var Count = data.MstArticles.Where(d => d.MstUser.Id == secure.GetCurrentUser() &&
                                                    d.MstArticleType.ArticleType == "Customer" &&
                                                    d.MstArticleCustomers.Count() > 0).Count();

            var Customers = (from d in data.MstArticles
                             where d.MstUser.Id == secure.GetCurrentUser() &&
                                  d.MstArticleType.ArticleType == "Customer" &&
                                  d.MstArticleCustomers.Count() > 0
                            select new Models.MstArticleCustomer
                            {
                                Id = d.MstArticleCustomers.First().Id,
                                ArticleId = d.Id,
                                CustomerCode = d.MstArticleCustomers.First().CustomerCode,
                                Customer = d.MstArticleCustomers.First().Customer,
                                Address = d.MstArticleCustomers.First().Address,
                                ContactNumbers = d.MstArticleCustomers.First().ContactNumbers,
                                ContactPerson = d.MstArticleCustomers.First().ContactPerson,
                                EmailAddress = d.MstArticleCustomers.First().EmailAddress
                            }).Skip(iDisplayStart).Take(10);

            var CustomerPaged = new Models.SysDataTablePager();

            CustomerPaged.sEcho = sEcho;
            CustomerPaged.iTotalRecords = Count;
            CustomerPaged.iTotalDisplayRecords = Count;
            CustomerPaged.aaData = Customers.ToList();

            return CustomerPaged;
        }
    }
}

The controller code is a little bit tricky. In order for me to explain it, I need to have some debug screen shots.

Every time the client calls on the AJAX to fill in the data to your JQuery.Datatables control, it will send a series of parameters, which is unseen by the user at the browser level. See the image below:

b1

Even though in the javascript code above I only put in the URL /api/MstArticleCustomer, JQuery.Datatables control added its own multiple parameters before executing the Get method.  That is why the first immediate two lines in my Get controller method I used HttpUtility to capture these parameters and then used it to filter off the data.

After you captured the parameters you need call in your LINQ to fetch the data from your database.  Then wrapped the result using your Pager Model Wrapper Class.

The end return result of the controller is the JSON string format similar to the one below.

{"sEcho":"1","iTotalRecords":1779,"iTotalDisplayRecords":1779,
 "aaData":[{"Id":1,"ArticleId":1,"CustomerCode":"0000000001","Customer":"888 BAKER'S CHOICE DIST. CORP.","Address":"DAVAO CITY ","ContactNumbers":"NA","ContactPerson":"NA","EmailAddress":"NA@NA"},
           {"Id":2,"ArticleId":2,"CustomerCode":"0000000002","Customer":"ABAD, ANA","Address":"MINGLANILLA, CEBU ","ContactNumbers":"NA","ContactPerson":"NA","EmailAddress":"NA@NA"}]}

You need to follow the above JSON format or else your JQuery.Datatables cannot display your data.

Thats it!

If you find this blog helpful, you can make the donation by clicking the Paypal button below.

Advertisements
About

Software developer living in the Philippines.

Posted in ASP.NET
12 comments on “How to create a server side processing JQuery.Datatables using ASP.NET REST-WEB API?
  1. Charles says:

    Just what I was looking for.

  2. Gerald Chudyk says:

    New at this so I know I am missing something. Created CustomerPaged.cs in models folder of my project and inserted above code. This line has syntax problem:

    public List aaData { get; set; }

    Error reads:

    Using the generic type ‘System.Collections.Generic.List’ requires 1 type arguments.

    Mr. Google is not helping me today.

    Clue stick appreciated.

    Thanks,
    Gerald.

  3. Gerald Chudyk says:

    Using the above example: public List MstArticleCustomer {get; set;} produces the same error in vs 2013.

    • Gerald Chudyk says:

      Using public List MstArticleCustomer {get; set;} will produce a clean build, but I don’t think this is the solution you are proposing. Am I missing something critical to c#?

    • Gerald Chudyk says:

      Something went wrong with my last post the code was intended to be:

      public List MstArticleCustomer {get; set;}

  4. Gerald Chudyk says:

    heh. The comment software is filtering the gt/lt symbols. I will try again.

    public list > string < MstArticleCustomer {get; set;}

  5. Gerald Chudyk says:

    public list < string > MstArticleCustomer {get; set;}

  6. Mohsin Patel says:

    can you provide source code for above example. i will be very much thankfull

    • hgminerva says:

      Sorry I could not provide you with code on this blog post except for sample code provided in the post. The post is almost 2 years old and I am now using HTML premium controls.

  7. Hi Harold. What is HTML premium controls?

    • hgminerva says:

      controls that you pay. i use a lot of javascript controls, most of them are free, but there are controls that you could buy, e.g., telerik, wijmo, sencha, etc.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Author

Harold Glenn P. Minerva
Software Developer / Tech Enthusiast
Living in the Philippines

View Harold Glenn Minerva's profile on LinkedIn

Instagram

Software Engineer - Seasonal and Range Trading Software. Magenta Trader is a powerful stock market visualization software that increases your probability of trading success.

Software Architect and Founder - Easyfis.com is a multi-tenant cloud-based Software-as-a-Service (SaaS) business app that caters to micro, small and medium trading businesses.

CTO and Co-Founder - We give your company the leverage by providing innovative software solutions products such as Point-of-Sales (POS), Financial Information System (FMIS), Payroll and DTR (HRIS), and many more.

%d bloggers like this: