Home / Questions / 123320
Accepted
Pedro Ávila
Asked: 2020-12-08 11:02:51 +0800 CST 2020-12-08 11:02:51 +0800 CST

Populate a datatable with data from a JSON

  • 772

I work with ASP.NET MVC, I am using the DataTable.net library

I am bringing data from the database through a JsonResult that returns the information in plain text.

Method

// GET: Cliente
    public ActionResult Index()
    {
        List<Cliente> _cliente = clienteService.GetAll().ToList();
        config = new MapperConfiguration(cfg => cfg.CreateMap<Cliente, ClienteViewModel>());
        List<ClienteViewModel> list = config.CreateMapper().Map<List<ClienteViewModel>>(_cliente);
        return Json(list, JsonRequestBehavior.AllowGet);
    }

from the HTML

<script type="text/javascript" language="javascript">
$(document).ready(function() {
    $("#clientes").DataTable({
        paging: true,
        searching: true,
        bProcessing: true,
        "ajax": {
            "url": "Cliente/Index",
            "columns": [
                { "data": "ClienteId" },
                { "data": "RazonSocial" },
                { "data": "NumeroDocumento" },
                { "data": "Direccion" },
                { "data": "Fijo" },
                { "data": "Email" },
                { "data": "Estado" }
            ],
            "dataType": "json"
        },
        "language": {
            "sProcessing": "Procesando...",
            "sLengthMenu": "Mostrar _MENU_ registros",
            "sZeroRecords": "No se encontraron resultados",
            "sEmptyTable": "Ningún dato disponible en esta tabla",
            "sInfo": "Mostrando registros del _START_ al _END_ de un total de _TOTAL_ registros",
            "sInfoEmpty": "Mostrando registros del 0 al 0 de un total de 0 registros",
            "sInfoFiltered": "(filtrado de un total de _MAX_ registros)",
            "sInfoPostFix": "",
            "sSearch": "Buscar:",
            "sUrl": "",
            "sInfoThousands": ",",
            "sLoadingRecords": "Cargando...",
            "oPaginate": {
                "sFirst": "Primero",
                "sLast": "Último",
                "sNext": "Siguiente",
                "sPrevious": "Anterior"
            },
            "oAria": {
                "sSortAscending": ": Activar para ordenar la columna de manera ascendente",
                "sSortDescending": ": Activar para ordenar la columna de manera descendente"
            }
        }
    });
});

The problem is that it shows me a page with the following information JSON

I think the issue is to leave the Index as ActionResult and create a JsonResult method that loads the table.

Updating as I have my methods. Controller

 // GET: Cliente
    public ActionResult Index()
    {
        return View();
    }

    public JsonResult ListaClientes()
    {
        List<Cliente> _cliente = clienteService.GetAll().ToList();
        config = new MapperConfiguration(cfg => cfg.CreateMap<Cliente, ClienteViewModel>());
        List<ClienteViewModel> list = config.CreateMapper().Map<List<ClienteViewModel>>(_cliente);
        return Json(list, JsonRequestBehavior.AllowGet);
    }

HTML

<script type="text/javascript" language="javascript">
$(document).ready(function() {
    $("#clientes").DataTable({
        //paging: true,
        //searching: true,
        //bProcessing: true,
        "ajax": {
            "url": "@Url.Action("ListaClientes")",
            "dataSrc": '',
            "type": "GET",
            "columns": [
                { "data": "ClienteId" },
                { "data": "RazonSocial" },
                { "data": "NumeroDocumento" },
                { "data": "Direccion" },
                { "data": "Fijo" },
                { "data": "Email" },
                { "data": "Estado" }
            ],
            //"dataType": "json"
        },
        "language": {
            "sProcessing": "Procesando...",
            "sLengthMenu": "Mostrar _MENU_ registros",
            "sZeroRecords": "No se encontraron resultados",
            "sEmptyTable": "Ningún dato disponible en esta tabla",
            "sInfo": "Mostrando registros del _START_ al _END_ de un total de _TOTAL_ registros",
            "sInfoEmpty": "Mostrando registros del 0 al 0 de un total de 0 registros",
            "sInfoFiltered": "(filtrado de un total de _MAX_ registros)",
            "sInfoPostFix": "",
            "sSearch": "Buscar:",
            "sUrl": "",
            "sInfoThousands": ",",
            "sLoadingRecords": "Cargando...",
            "oPaginate": {
                "sFirst": "Primero",
                "sLast": "Último",
                "sNext": "Siguiente",
                "sPrevious": "Anterior"
            },
            "oAria": {
                "sSortAscending": ": Activar para ordenar la columna de manera ascendente",
                "sSortDescending": ": Activar para ordenar la columna de manera descendente"
            }
        }
    });
});

Error DataTables warning: table id=customers - Requested unknown parameter '0' for row 0, column 0. For more information about this error, please see

ajax

1 Answers

  1. Best Answer

    My doubt is the same as that of JuankGlezz since if your Index method returns a Json you cannot call the Index view at the same time. But I am going to give you a clear example of your question.

    We have the Index method which calls the Index view that contains a Table(DataTable) where the clients are displayed.

        public ActionResult Index()
    {
        return View();
    }

    The view has the following:

        @*ESTO YA NO LO NECESITAS YA QUE LOS CLIENTES VIENEN EN UN JSON*@
    @*@model IEnumerable<WebApp1.Models.Cliente>*@

    @{
       ViewBag.Title = "Index";
    }

    <h2>Index</h2>

    <p>
      @Html.ActionLink("Create New", "Create")
    </p>
<table id="clientes" class="display">
    <thead>
        <tr>
            <th>
                Id
            </th>
            <th>
                RazonSocial
            </th>
            <th>
                NumeroDocumento
            </th>
            <th>
                Direccion
            </th>
            <th>
                Fijo
            </th>
            <th>
                Email
            </th>
            <th>
                Estado
            </th>
        </tr>
    </thead>
</table>
@section scripts
{
    <script>
        $(document).ready(function () {
            $("#clientes").DataTable({
                ajax: {
                    "url": "@Url.Action("ListadoClientes")",
                    "dataSrc": ''
                },
                columns: [
                    { "data": "ClienteId" },
                    { "data": "RazonSocial" },
                    { "data": "NumeroDocumento" },
                    { "data": "Direccion" },
                    { "data": "Fijo" },
                    { "data": "Email" },
                    { "data": "Estado" }
                ]
            });
        });
    </script>
}

    If you look closely, the script was shorter when removing parameters that are called by default at the end, such as busqueda, paginaciónand filtrado. The interesting part is where the option is defined, urlwhich I define with the Helper @Url.Actionthat ASP brings. This by default is passed to our controller as that is GETwhy I create a method which returns a Json with a list of Clients. Being this way:

        public class ClienteController : Controller
    {
        private ApplicationDbContext db = new ApplicationDbContext();

        // GET: /Cliente/
        public ActionResult Index()
        {
            ListadoClientes();
            return View();
        }

        public JsonResult ListadoClientes()
        {
            var clientes = db.Clientes.ToList();
            return Json(clientes, JsonRequestBehavior.AllowGet);
        }
        .......
}

    JsonRequestBehavior.AllowGetIT ALLOWS YOU TO RESPOND WITH JSON TO GET TYPE REQUESTS, IF YOU DON'T PUT IT, IT WOULD GIVE YOU A 500 ERROR. Another important part is to declare "dataSrc": ''in your script to work with a Json array. This way it works perfectly I hope it helps you

    • 1