Implementing DataTables using EF. No results showing, no errors

Question

I try to follow this tutorial on how to use DataTables plugin: https://www.codeproject.com/Articles/155422/jQuery-DataTables-and-ASP-NET-MVC-Integration-Part?fid=1609174&df=90&mpp=25&sort=Position&spc=Relaxed&tid=4604201 but with using EF.

I've installed via package manager "Mvc.JQuery.DataTables".

This is my Controller (db is made out of generated edmx from Database, jQueryDataTableParamModel is a copy from a tutorial):

public class TablesController : Controller
{
    public ActionResult Index()
    {
        return View();
    }
    // AjaxHandler
    public ActionResult AjaxHandler(jQueryDataTableParamModel param)
    {
        var result = from p in db.Tables
                     select p;

        return Json(new
        {
            sEcho = param.sEcho,
            iTotalRecords = db.Tables.Count(),
            iTotalDisplayRecords = db.Tables.Count(),
            aaData = result
        },
        JsonRequestBehavior.AllowGet);
    }
}

This is my model class:

public partial class Table
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
    public string Date { get; set; }
    public Nullable<decimal> DateValue { get; set; }
    public Nullable<int> cs_Table { get; set; }

    public virtual Surname Surname1 { get; set; }
    public virtual Date Date1 { get; set; }
    public virtual Name Name1 { get; set; }
}

This is my View:

@model IEnumerable<EFModel.Table>

@{
    ViewBag.Title = "Index";
}

 <h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table" id="myDataTable">
    <thead>
    <tr>
        <th>
             @Html.DisplayNameFor(model => model.Id)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Surname)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Date)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.DateValue)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.cs_Table)
        </th>
    </tr>
    </thead>
    <tbody>
    </tbody>
</table>

And this is my index.js with function for handling tables:

$(document).ready(function () {

    $('#myDataTable').dataTable({
        "bServerSide": true,
        "sAjaxSource": "Tables/AjaxHandler",
        "bProcessing": true,
        "aoColumns": [
            {
                "sName": "ID",
                "bSearchable": false,
                "bSortable": false,
                "fnRender": function (oObj) {
                    return '<a href=\"Details/' +
                        oObj.aData[0] + '\">View</a>';
                }
            },
            { "sName": "Name" },
            { "sName": "Surname" },
            { "sName": "Date" },
            { "sName": "DateValue" }
            { "sName": "cs_Tables"}
        ]
    });
});

I import needed files for DataTables to work in the Head in _Layout.cshtml:

<script src="~/Scripts/jquery-3.3.1.min.js"
        type="text/javascript"></script>
<script src="~/Scripts/DataTables/jquery.dataTables.min.js"
        type="text/javascript"></script>
<script src="~/Scripts/DataTables/index.js"
        type="text/javascript"></script>

From what I've read it should suffice to display records in my view Index but nothing comes up. I've put breakpoint on AjaxHandler method but it doesn't go there.

Do you get any js console errors? can you see the datatables resources being loaded? — markpsmith
– markpsmith, Commented Apr 15, 2019 at 11:19

Uxmaan Ali · Accepted Answer · 2019-04-15 14:27:21Z

I am using 1.10 Jquery Datatables and for the templating i am using Underscore JS.

Here is my code for loading from server side.

Put this template and table in your html code.

<table class="table table-bordered table-condensed" id="tblAccounts"></table>

<script type="text/template" id="tmpl_Grid">
    <td><%= Id %></td>
    <td><%= Amount %></td>
    <td><%= Date %></td>
    <td><%= Type %></td>        
</script>

Then this method for js to load data from server side.

function  Load() {
            var tmpl = _.template($('#tmpl_Grid').html());
            $('#tblAccounts').DataTable({
                "dom": "<'row'<'col-md-6 col-sm-12'l><'col-md-6 col-sm-12'f>r>t<'row'<'col-md-5 col-sm-12'i><'col-md-7 col-sm-12'p>>",
                "paging": true,
                "info": true,
                "ordering": true,
                "search": true,
                "processing": true,
                "serverSide": true,
                "destroy": true,
                "ajax": {
                    "url": "/Accounts/LoadList",
                    "type": "POST",
                    "data": function (d) {
                        d.startDate = $("#txtStartDate").val();
                        d.endDate = $("#txtEndDate").val();
                        d.head = $("#drpAccountHeads").val();
                        var accounts = $("#drpAccountTypes").val();
                        d.accounts = accounts == null ? [] : accounts;
                        d.phase = $("#drpPhases option:selected").val();
                    }
                },
                "columns": [
                    { "data": null, "title": "ID", "className": "", "orderable": false, "searchable": false },
                    { "data": null, "title": "AMOUNT", "className": "", "orderable": false, "searchable": false },
                    { "data": null, "title": "DATE", "className": "", "orderable": false, "searchable": false },
                    { "data": null, "title": "TYPE", "className": "", "orderable": false, "searchable": false }
                ],
                "order": [[0, "asc"]],
                "rowCallback": function (row, data) {
                    $(row).html(tmpl(data));
                },
                "initComplete": function (settings, json) {

                }
            });
        }

Here is the code for controller.

[HttpPost]
        public async Task<JsonResult> LoadList()
        {
           var search = Request.Form["search[value]"] + "";

            var totalCount = 0;
            var fList = _context.Expenses.Include(x => x.AccountType).AsQueryable();
            if (!string.IsNullOrEmpty(search))
            {
                fList = fList.Where(x => x.Description.ToLower().Trim().Contains(search.ToLower().Trim()));
            }

            var list = await fList.OrderByDescending(x => x.Id).Skip(start).Take(length).Select(x => new
            {
                x.Id,
                x.Amount,
                Date = x.Date != null ? x.Date.Value.ToString("dd-MMM-yyyy") : "",
                Type = x.AccountTypeId != null ? x.AccountType.Name : "",
                x.Description,
                x.BillAmount,
                x.Payment,
                x.AccountTypeId
            }).ToListAsync();

            if (list.Any())
            {
                totalCount = fList.Count();
            }

            var result = JObject.FromObject(new
            {
                draw,
                recordsFiltered = totalCount,
                recordsTotal = totalCount,
                data = list
            });
            return result;
        }

tschmit · Accepted Answer · 2019-04-15 09:25:23Z

1

I use datatables 1.10.18 and my return class is:

public class DataTableProcessingResult {
    public int draw { get; set; }
    public int recordsTotal { get; set; }
    public int recordsFiltered { get; set; }
    public object data { get; set; }
    public string error { get; set; }
}

so can tou check by fiddler or any network analyzer or debbuger the communication between you client and the server ?

answered Apr 15, 2019 at 9:25

tschmit

7,8402 gold badges38 silver badges45 bronze badges

Collectives™ on Stack Overflow

Implementing DataTables using EF. No results showing, no errors

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related