Pomme De Terre
Pomme De Terre

Reputation: 149

Implementing DataTables using EF. No results showing, no errors

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.

Upvotes: 0

Views: 53

Answers (2)

Uxmaan Ali
Uxmaan Ali

Reputation: 349

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

Upvotes: 1

tschmit007
tschmit007

Reputation: 7800

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 ?

Upvotes: 1

Related Questions