How to call stored procedure while using jquery Data tables

Question

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

That seems totally dangerous. Client side code, your JQuery, should not have direct access to your database — Cleptus, Jun 04 '19 at 16:16
Possible duplicate of [How to call Stored Procedure in Entity Framework 6 (Code-First)?](https://stackoverflow.com/questions/20901419/how-to-call-stored-procedure-in-entity-framework-6-code-first) — devlin carnate, Jun 04 '19 at 16:24
@devlincarnate can you please help me out by providing an example for my code? — Minhal, Jun 04 '19 at 16:28
`{ "draw": 1, "recordsTotal": 57, "recordsFiltered": 57, "data": [ your array ] } ` try to retrurn this type of object from controller — Hardik, Jun 04 '19 at 16:48
@Minhal - Check out the link I posted above. Lots of examples there. — devlin carnate, Jun 04 '19 at 16:50
Yes, and return above type of **json response** from controller. please have a look on link what I share just next to my message and click on **ajax** tab. You'll get more idea about it. — Hardik, Jun 04 '19 at 18:15

score 1 · Answer 1 · answered Jun 23 '19 at 05:27

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: [
    ...
    ]
});

score 0 · Accepted Answer · answered Jul 09 '19 at 14:21

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

How to call stored procedure while using jquery Data tables

2 Answers2