Feed ajax datatable with json from Asp.net MVC Action method

Question

I am using Ajax Datatable, I want to feed the table with Json data, which i am returning from MVC Action method. This is what i have tried so far,

My Controller action method

public ActionResult Index()
{
    _dbcontext = new dbContext();
    List<Employee> employees = new List<Employee>();
    var query = (from e in _dbcontext.Employees select new { e.FirstName, e.LastName, e.Username, e.Password }).ToList();
    return Json(query,JsonRequestBehavior.AllowGet);
}

And here is my Javascript on Index page

</script>
    $(document).ready(function () {
        $('#example').dataTable({
            "processing": true,
            "serverSide": true,
            "ajax": {
                "url": "Home/Index",
                "dataType": "jsonp"
            }
        });
    });
</script>

But on page load only the plain Json data can be seen and no data table, I think It is not even rendering the Html on the index page and hence neither Datatable as I can not see anything in chrome debugger.

Also, Html and script referencing is all Ok as I can see the results when I feed the datatable from a .txt file having array of data.

I don't know what is the right way to do that, If somebody has the solution for that, then please help, Thanks.

Answer 1

One possible way is to define your "regular" table markup in a razor view/partial/ view - id your table, ie, id="DataTables-Employee". Your controller code looks just fine but with an ActionResult return a View with the model data. This let you control the data by using razor syntax in your view, which I have found much easier than complex JSON results. (Also, I like to use AutoMapper to project my model data to - give it a try!)

Note: this is only one of several ways to do this. Server-side processing helps when working with huge datasets, but give the client-side a try. I use ajax but flatten your model result. More likely than not you will need to define your columns and map them to your JSON. If you want to post your table results, add a form tag. I use ajax for this and serialize my form before sending. I hope this helps!

    public ActionResult Index()
    {
        _dbcontext = new dbContext();

        List<Employee> employees = new List<Employee>();

        var query = (from e in _dbcontext.Employees 
            select new {e.FirstName, e.LastName, e.Username, e.Password};

        //I would recommend using AutoMapper and project your model to this
         return View(query.ToArray());
    }

Say you are working with an Index view:

    @model dynamic

    ... //typical HTML table
    <table class="display compact table table-striped table-hover dataTables_sizing table-condensed" id="dataTables-Employee">
        <thead>
            <tr>
                <th>First Name</th>
                <th>Last Name</th>
                <th>User Name</th>
                <th class="no-sort">Actions</th>
            </tr>
        </thead>
        @foreach (var emp in Model)
        {
            <tr>
                <td>@emp.FirstName</td>
                <td>@emp.LastName</td>
                <td>@UserName</td>
                <td style="width: 100px;">
                    <i class="fa fa-search"></i>
                    @Ajax.ActionLink("View", "Index", new { id = @app.EmployeeId }, 
                                    new AjaxOptions
                                   {
                                     dateTargetId = "resultSection",
                                     HttpMethod = "GET",
                                InsertionMode = InsertionMode.Replace
                            })
                </td>
            </tr>
        }

    </table>

//Know make it a DataTable

    $('#dataTables-Employee').DataTable({
      scrollY: '330px',
      scrollCollapse: true,
      paging: true,
      scrollX: false,
      "columnDefs": [{
        "orderable": false,
        "targets": 2
      }, {
        className: "dt-center",
        "targets": [3]
      }],
      "aria": {
        "sortAscending": ": activate to sort column ascending",
        "sortDescending": ": activate to sort column descending"
      },
      "options": {
        "emptyTable": "No records to display..."
      }
    });​

Example of results with Bootstrap styles:

Answer 2

I think what you need to do is simply the following

</script>
    $(document).ready(function () {
        $('#example').dataTable({
            "ajax": "/Home/Index",
             "columns": [
                        { "data": "FirstName" },
                        { "data": "LastName" },
                        { "data": "Username" },
                        { "data": "Password" },
                        ]
        });
    });
</script>

No need for the processing and serverside and they are used when you have large data and would like to have paging and sorting done on the server side which is not the case in your code