My datatable is not picking the json result

Question

I am using datatables and my get handler returns a json result but the datatable is not displaying it. What am I missing? Here's my code. I am using Visual Studio 2019 3.1 .net core with razor pages. I am trying to call my datatable in the OnGet handler. I tried the post but that did not work either.

My cust class:

public class Cust
{
        public int Id { get; set; }
        public string Name { get; set; }
        public string PhoneNumber { get; set; }
        public string Address { get; set; }
        public string PostalCode { get; set; }
}

_layout.cshtml:

<div class="container">
        <main role="main" class="pb-3">
            @RenderBody()
        </main>
    </div>

    <footer class="border-top footer text-muted">
        <div class="container">
            &copy; 2022 - testApp - <a asp-area="" asp-page="/Privacy">Privacy</a>
        </div>
    </footer>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.12.1/datatables.min.css" />

    <script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.12.1/datatables.min.js"></script>


    @RenderSection("Scripts", required: false)

My Index.cshtml.cs model code:

public class IndexModel : PageModel
{
        [BindProperty]
        public int Draw { get; set; }

        // public IEnumerable<Column> Columns { get; set; }
        // public IEnumerable<Order> Order { get; set; }

        public int Start { get; set; }
        public int Length { get; set; }

        public List<Cust> Customers = new List<Cust>();

        public JsonResult OnGet()
        {
            var recordsTotal = 3;
            Cust cs1 = new Cust();
            cs1.Id = 1;
            cs1.Name = "test1";
            cs1.Address = "address1";
            cs1.PhoneNumber = "11111";
            cs1.PostalCode = "1111";

            Cust cs2 = new Cust();
            cs2.Id = 1;
            cs2.Name = "test2";
            cs2.Address = "address1";
            cs2.PhoneNumber = "11111";
            cs2.PostalCode = "1111";

            Cust cs3 = new Cust();
            cs3.Id = 1;
            cs3.Name = "test3";
            cs3.Address = "address1";
            cs3.PhoneNumber = "11111";
            cs3.PostalCode = "1111";

            Customers.Add(cs1);
            Customers.Add(cs2);
            Customers.Add(cs3);

            var recordsFiltered = Customers.Count();

            var data = Customers;

            return new JsonResult(new
            {
                Draw = Draw,
                RecordsTotal = recordsTotal,
                RecordsFiltered = recordsFiltered,
                Data = data
            });
 }

My Razor page -- Pages/customers/Index.cshtml:

@page
@model testApp.Pages.Customer.IndexModel
@{
}


<h1>Index</h1>

<p>
    
</p>
<table id="myTable" class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.Customers[0].Id)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Customers[0].Name)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Customers[0].PhoneNumber)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Customers[0].Address)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Customers[0].PostalCode)
            </th>
            <th></th>
        </tr>
    </thead>
    <tbody>
    </tbody>
</table>

@section Scripts {
    <script>
        $(document).ready(function () {
            $('#myTable').DataTable({
                "processing": true,
                "serverSide": true,
                "ajax": {
                    url: "/customers/Index?handler=OnGet",
                    "type": "GET",
                    "dataType": "json"
                },
                "columns": [
                    { "data": "id", "autowidth": true },
                    { "data": "name", "autowidth": true },
                    { "data": "phoneNumber", "autowidth": true },
                    { "data": "address", "autowidth": true },
                    { "data": "postalCode", "autowidth": true }

                ],
                "order": [[0, "desc"]]
            });
        });
    </script>
}

Here's my json response.

{
    "draw": 0,
    "recordsTotal": 3,
    "recordsFiltered": 3,
    "data": [
                {  
                    "id": 1,
                    "name": "test1",
                    "phoneNumber": "11111",
                    "address": "address1",
                    "postalCode": "1111"
                 },
                 {
                    "id": 1,
                    "name": "test2",
                    "phoneNumber": "11111",
                    "address": "address1",
                    "postalCode": "1111"
                 },
                 {
                    "id": 1,
                    "name": "test3",
                    "phoneNumber": "11111",
                    "address": "address1",
                    "postalCode": "1111"
                 }
            ]
}

I am just testing it with this project to see if I can get the datatable to work. I will be eventually getting the data from the database. Yes, the volume will be big, thousands of rows.

Answer 1

Here is a whole working demo you could follow:

Page(Pages/customers/Index.cshtml)

@page 
@model IndexModel

<h1>Index</h1>

<p>
    
</p>
<table id="myTable" class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.Customers[0].Id)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Customers[0].Name)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Customers[0].PhoneNumber)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Customers[0].Address)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Customers[0].PostalCode)
            </th>
            <th></th>
        </tr>
    </thead>
    <tbody>
    </tbody>
</table>

JS in page:

@section Scripts {
    <script>
        $(document).ready(function () {
            $.ajax({
                type: "GET",
                url: "/customers/Index?handler=Json",      //change here...
                dataType: "json",
                success: OnSuccess,
            });
        });
        function OnSuccess(response) {    
            console.log(response.data)
            $('#myTable').DataTable(
                {
                    data: response.data,
                    columns: [
                        { "data": "id" },
                        { "data": "name" },
                        { "data": "phoneNumber" },
                        { "data": "address" },
                        { "data": "postalCode" }
                    ],
                });
        };
    </script>
}

PageModel(Pages/customers/Index.cshtml.cs):

public class IndexModel : PageModel
{

    [BindProperty]
    public int Draw { get; set; }

    // public IEnumerable<Column> Columns { get; set; }

    // public IEnumerable<Order> Order { get; set; }

    public int Start { get; set; }

    public int Length { get; set; }

    public List<Cust> Customers = new List<Cust>();

    public void OnGet()             //add this.....
    {

    }

    public JsonResult OnGetJson()   //change handler name..
    {
        var recordsTotal = 3;
        Cust cs1 = new Cust();
        cs1.Id = 1;
        cs1.Name = "test1";
        cs1.Address = "address1";
        cs1.PhoneNumber = "11111";
        cs1.PostalCode = "1111";

        Cust cs2 = new Cust();
        cs2.Id = 1;
        cs2.Name = "test2";
        cs2.Address = "address1";
        cs2.PhoneNumber = "11111";
        cs2.PostalCode = "1111";

        Cust cs3 = new Cust();
        cs3.Id = 1;
        cs3.Name = "test3";
        cs3.Address = "address1";
        cs3.PhoneNumber = "11111";
        cs3.PostalCode = "1111";

        Customers.Add(cs1);
        Customers.Add(cs2);
        Customers.Add(cs3);
        var recordsFiltered = Customers.Count();
        var data = Customers;

        return new JsonResult(new
        {
            Draw = Draw,
            RecordsTotal = recordsTotal,
            RecordsFiltered = recordsFiltered,
            Data = data
        });

    }
}

_Layout.cshtml:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>@ViewData["Title"] - RazorPagesProj3_1</title>
    <link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.min.css" />
    <link rel="stylesheet" href="~/css/site.css" />
</head>
<body>
    <header>
        //....
    </header>
    <div class="container">
        <main role="main" class="pb-3">
            @RenderBody()
        </main>
    </div>

    <footer class="border-top footer text-muted">
        <div class="container">
            &copy; 2022 - RazorPagesProj3_1 - <a asp-area="" asp-page="/Privacy">Privacy</a>
        </div>
    </footer>

    <script src="~/lib/jquery/dist/jquery.min.js"></script>
    <script src="~/lib/bootstrap/dist/js/bootstrap.bundle.min.js"></script>
    <script src="~/js/site.js" asp-append-version="true"></script>
     <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.12.1/datatables.min.css" />

    <script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.12.1/datatables.min.js"></script>
    @RenderSection("Scripts", required: false)
</body>
</html>