jQuery Datatables in ASP.NET MVC applications

Question

I'm trying to fetch my users list data into jQuery Datatable, but instead of data getting populated in my Datatable, it just returns a raw JSON format page with my data.

I have searched all over the internet mostly what is written in their solution I'm applying the same technique.

Here is my ASP.NET MVC action method:

[Route("Admin/Users")]
public ActionResult GetAllUsers()
{
    List<UsersModel> datas = new List<UsersModel>();
    Dt = _helper.GetAllUsers();

    if (Dt.Rows.Count > 0)
    {
        foreach (DataRow dr in Dt.Rows)
        {
            UsersModel users = new UsersModel
            {
                UserID = Convert.ToInt32(dr["USERID"]),
                UserName = dr["USERNAME"].ToString(),
                Password = dr["PASSWORD"].ToString(),
                SchoolCode = dr["SCHOOLCODE"].ToString(),
                BranchCode = dr["MAINACCOUNTID"].ToString(),
                IsActive = Convert.ToBoolean(dr["ISACTIVE"])
            };
            datas.Add(users);
        }
    }
    else
    {
        ViewBag.ErrorMessage = "No Users Found";
    }

    return Json(new { data = datas }, JsonRequestBehavior.AllowGet) ;
}

Here is my jQuery Datatable being used in view GetAllUsers.cshtml:

@model SiteAdministrator1._1._0.Models.UsersModel

@{
    ViewBag.Title = "GetAllUsers";
    Layout = "~/Views/Shared/_adminLayout.cshtml";
}

<h2>GetAllUsers</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table" id="usersList">
    <thead>
        <tr>
            <th>
                UserID
            </th>
            <th>
                UserName
            </th>
            <th>
                Password
            </th>
            <th>
                SchoolCode
            </th>
            <th>
                BranchCode
            </th>
            <th>
                IsActive
            </th>
        </tr>
    </thead>
    <tbody></tbody>

</table>
@section scripts
{
    <script>
        $(document).ready(function () {
            $('#usersList').Datatable({
                "ajax": {
                    "url": "@Url.Action("GetAllUsers", "Admin")",
                    "type": "GET",
                    "datatype": "json"
                },
                "columns": [
                    { "data": "UserID" },
                    { "data": "UserName" },
                    { "data": "Password" },
                    { "data": "SchoolCode" },
                    { "data": "BranchCode" },
                    { "data": "IsActive" },

                    // Add other columns as per your model
                ]
            });
        });
</script>

Note:

1. I have integrated all the jQuery and Datatable libraries.
2. I have checked my Datatable connections, i.e. connection string) and queries written in the stored procedures
3. I have used debugger to see if I am able to fetch the data from the databases and it works perfectly fine
4. I am doing all this database fetching through ADO.NET (no entity data model, no Entity Framework)
5. If I just fetch it in a list template bind my model it works fine and shows the data in ASP.NET MVC list template

Answer 1

[Route("Admin/Users")]
public ActionResult GetAllUsers() 
{
    var initialData = _helper.GetAllUsers(); 

    var datas = new List<UsersModel>();
    if (initialData.Rows.Count > 0)
    {
        foreach (DataRow dr in initialData.Rows)
        {
            UsersModel user = new UsersModel
            {
                UserID = Convert.ToInt32(dr["USERID"]),
                UserName = dr["USERNAME"].ToString(),
                Password = dr["PASSWORD"].ToString(),
                SchoolCode = dr["SCHOOLCODE"].ToString(),
                BranchCode = dr["MAINACCOUNTID"].ToString(),
                IsActive = Convert.ToBoolean(dr["ISACTIVE"])
            };
            datas.Add(user);
        }
    }

    return View("GetAllUsers", datas); }

[Route("Admin/GetAllUsersData")]
public ActionResult GetAllUsersData()
{
    // Fetch and return data in JSON format
    var datas = _helper.GetAllUsers(); 

    return Json(new { data = datas }, JsonRequestBehavior.AllowGet);
}

this is a better approach as separating for rendering the view and fetching JSON data through different action methods is due to the way web page & client-server interactions function.