How to use jQuery datatable with Web API in MVC? want to implement CRUD, searching and sorting

Take a look at these articles which will give you details and step by step procedure for it

1. jQuery datatable server-side processing with Api

2.Create, Edit & delete using asp.net Web api & jQuery datatable server side processing

Updated: Take a Look at the article on this jQuery datatable server-side processing with Api

Or

You can use these steps to create server side processing with web api and jQuery datatable

Step 1: Create Model for the example,let's say 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; }
    }
}

Step 2: Create model for handling paging/data of jQuery datatable, this model will accpet JSON data of jQuery datatables

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; }
    }
}

Step 3 : create javascript for jQuery datatable to  initialize and accept data from server side  , so the javascript code for it will be

$(function () {
    $ArticleCustomerDataTable = $("#TableArticleCustomerList").dataTable({
        "bServerSide": true, //make server side processing to true
        "sAjaxSource": '/api/MstArticleCustomer', //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": "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" }
        ]
    });
});

I have explained above javascript code line meaning using comment

Step 4: Create web api controller to get data from back end, it is just GET method with Rest architecture

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);
           //sEcho gives the number of time method was called
            string sEcho = nvc["sEcho"].ToString();
           //starting index number
            int iDisplayStart = Convert.ToInt32(nvc["iDisplayStart"]);
 
//get total value count
            var Count = data.MstArticles.Where(d => d.MstUser.Id == secure.GetCurrentUser() &&
                                                    d.MstArticleType.ArticleType == "Customer" &&
                                                    d.MstArticleCustomers.Count() > 0).Count();

 //get data from database
            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);
             
            //Create a model for datatable paging and sending data
            var CustomerPaged = new Models.SysDataTablePager();
           //enter all the required values
            CustomerPaged.sEcho = sEcho;
            CustomerPaged.iTotalRecords = Count;
            CustomerPaged.iTotalDisplayRecords = Count;
            CustomerPaged.aaData = Customers.ToList();
 
            return CustomerPaged;
        }
    }
}

In the above code, first we are fetching jQuery datatable details, and then using the database context we are getting data from database, for entering it into model, then passing it to front end using JSON format.

That's it, you should get JSON data in front end like 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"}]}

Here is the source link

You can refer other links also like

https://www.codeproject.com/Articles/1158982/Using-DataTables-with-Web-API-Part-Paging-Sorting

http://jasonwatmore.com/post/2012/10/23/using-mvc-4-web-api-with-jquery-datatables

The above links also give example with code for using jQuery datatable with Web-api