CODEWITHSUNDEEP
jqueryasp.net-mvc-5datatablesentity-framework-6
Minhal
Minhal

Reputation: 85

How to call stored procedure while using jquery Data tables

I am using Jquery Data tables for my project and I am working on Asp.Net MVC 5 and entity framework 6. What I really want to do is just call my stored procedure to be called inside my jquery data tables. For now I am calling the table from my database and the call is a ajax call for jquery data table.

Here is the example of my ajax call for data table.

$('#studentTable').DataTable({
    "ajax": {
        "url": "/StructuredImportTgts/GetData",
        "type": "GET",
        "datatype": "json"
    },
    responsive: 'true',
    dom: 'Bfrtip',
    buttons: [
        'copy', 'excel', 'pdf'
    ],
    "columns": [
        { "data": "PART_NO" },
        { "data": "LEVEL" },
        { "data": "PART_NO" },
        { "data": "PART_NAME" },
        { "data": "L1QTY" },
        { "data": "PL1" },
        { "data": "PL2" },
        { "data": "PL3" },
        { "data": "SupplierLocID" },
        { "data": "SupplierLocID" },
        { "data": "Discrepancies" },
        { "data": "Comments" }
    ]

The code for GETDATA() is in my controller which is as follows, it calls the table from the database and this is where I need to call my Stored procedure.

public ActionResult GetData()
{
    using (Dev_Purchasing_New_ModelEntities db = new Dev_Purchasing_New_ModelEntities())
    {
        db.Configuration.LazyLoadingEnabled = false;
        List<bomStructuredImportTgt> bomStructuredImportTgtList = db.bomStructuredImportTgts.ToList<bomStructuredImportTgt>();
        return Json(new { data = bomStructuredImportTgtList }, JsonRequestBehavior.AllowGet);
    }
}

Upvotes: 0

Views: 3081

Answers (2)

Minhal
Minhal

Reputation: 85

I get it through this approach. I hope it can be of help for someone.

  public ActionResult GetData1()
    {
        using (Dev_Purchasing_New_ModelEntities db = new Dev_Purchasing_New_ModelEntities())
        {
            db.Configuration.LazyLoadingEnabled = false;
            var bomStructuredImportTgtList = db.usp_GetStructureTGT();
            return Json(new { data = bomStructuredImportTgtList }, JsonRequestBehavior.AllowGet);
        }
    }

Upvotes: 0

Hardik
Hardik

Reputation: 3258

Create a helper class for datatable

namespace DataTableHelper
{
    public class DataTableModel
    {
        public int Draw { get; set; }
        public int Start { get; set; }
        public int Length { get; set; }
        public IEnumerable<Column> Columns { get; set; }
        public IEnumerable<Order> Order { get; set; }
        public Search Search { get; set; }
    }
    public class Column
    {
        public string Data { get; set; }
        public string Name { get; set; }
        public bool Searchable { get; set; }
        public bool Orderable { get; set; }
        public Search Search { get; set; }
    }

    public class Search
    {
        public string Value { get; set; }
        public string Regex { get; set; }
    }

    public class Order
    {
        public int Column { get; set; }
        public string Dir { get; set; }
    }
}

Set that class as parameter in controller action

[HttpPost]
public JsonResult GetData(DataTableModel model)
{
    list can be anything
    var list = new List(); // list of records to be displayed in datatable
    return Json(new
            {
                draw = model.Draw,
                data = list,
                recordsTotal = list .Count,
                recordsFiltered = 0
            }, JsonRequestBehavior.AllowGet);

}

datatable settings

$('#datatable').DataTable({
    ajax: {
      url: '/MyController/GetData',
      type: "POST" // ajax type must be match to controllers action type
    },
    serverSide: false,
    processing: true,
    columns: [
    ...
    ]
});

Upvotes: 1

Related Questions