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


I want to implement jquery datatable(With Displaying data, edit, delete, search, sort and paging should be there) with Web API and MVC

I had previously implemented jquery data table with asp.net MVC and it was working, but when I was started using jQuery Datatable with web API, I wasn't able to implement CRUD with searching and pagination in it.

If there are any links or sample code, please provide me for reference.

thanks and regards

G.Ram Mohan


Asked by:- Ram MohanG
1
: 187 At:- 9/12/2017 4:57:12 AM
asp.net jQuery-datatable Web-api






1 Answers
profileImage Answered by:- jaya

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

1
At:- 9/12/2017 7:04:21 AM





Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use
Amazon Deals

Jobs from Indeed