Help with Datatable Server-side

Question

Hi wanted to know the best way to create a Datatable Server-side. Some questions I have:

• The next button doesn’t work, and the record counter doesn’t work. How do I tidy up?
• I am using Start and Length in SQL code, there is a simpler way?
• How do I make the search work?
• And how can I do the stylization of this table (mainly take the 3 dots of the upper left corner)?

Thank you in advance

Follow the codes below:

Model

    public class Guia
    {
        public string Login_ { get; set; }

        public string Sexo { get; set; }

        public int Id { get; set; }

        public Endereco Endereco { get; set; }

        public string Senha { get; set; }

        public char Ativo { get; set; }

        public int IdEndereco { get; set; }

        public string Nome { get; set; }

        public string Sobrenome { get; set; }

        public string CarteiraTrabalho { get; set; }

        public string CategoriaHabilitacao { get; set; }

        public double Salario { get; set; }

        public string Cpf { get; set; }

        public string Rg { get; set; }

        public DateTime DataNascimento { get; set; }

        public byte Rank { get; set; }

        public List<Pacote> Pacotes { get; set; }
    }

Controller

public class GuiaController : Controller
{
    // GET: Guia

    [HttpGet]
    public ActionResult Index()
    {
        return View();
    }

    [HttpGet]
    public ActionResult Cadastro()
    {

        ViewBag.Guia = new Guia();
        return View();
    }

    [HttpGet]
    public ActionResult Editar(int id)
    {
        Guia guia = new GuiaRepository().ObterPeloId(id);
        ViewBag.Guia = guia;

        return Content(JsonConvert.SerializeObject(guia));
    }

    [HttpGet]
    public ActionResult Excluir(int id)
    {
        bool apagado = new GuiaRepository().Excluir(id);
        return Content(JsonConvert.SerializeObject(apagado));
    }

    [HttpPost]
    public ActionResult Store(GuiaString guia, EnderecoString endereco)
    {

        Endereco enderecoModel = new Endereco()
        {
            Cep = endereco.Cep.ToString(),
            Logradouro = endereco.Logradouro.ToString(),
            Numero = Convert.ToInt16(endereco.Numero.ToString()),
            Referencia = endereco.Referencia.ToString(),
            Complemento = endereco.Complemento.ToString()
        };

        int codigoEndereco = new EnderecoRepository().Cadastrar(enderecoModel);

        Guia guiaModel = new Guia();
        guiaModel.IdEndereco = codigoEndereco;
        guiaModel.Nome = guia.Nome.ToString();
        guiaModel.Sobrenome = guia.Sobrenome.ToString();
        guiaModel.DataNascimento = Convert.ToDateTime(guia.DataNascimento.Replace("/", "-").ToString());
        guiaModel.Sexo = guia.Sexo.ToString();
        guiaModel.Rg = guia.Rg.ToString();
        guiaModel.Cpf = guia.Cpf.ToString();
        guiaModel.CarteiraTrabalho = guia.CarteiraTrabalho.ToString();
        guiaModel.CategoriaHabilitacao = guia.CategoriaHabilitacao.ToString();
        guiaModel.Salario = Convert.ToDouble(guia.Salario.ToString());
        guiaModel.Rank = Convert.ToByte(guia.Rank.ToString());

        int identificador = new GuiaRepository().Cadastrar(guiaModel);
        return Content(JsonConvert.SerializeObject(new { id = identificador }));
    }

    [HttpPost]
    public ActionResult Update(Guia guia)
    {
        bool alterado = new GuiaRepository().Alterar(guia);
        return Content(JsonConvert.SerializeObject(new {id = alterado }));
    }

    [HttpGet]
    public ActionResult ObterTodosPorJSON()
    {
        string start = Request.QueryString["start"];
        string length = Request.QueryString["length"];

        List<Guia> guias = new GuiaRepository().ObterTodosParaJSON(start, length);

        return Content(JsonConvert.SerializeObject(new
        {
            data = guias
        }));

    }

    [HttpGet]
    public ActionResult ModalCadastro()
    {
        return View();
    }

    [HttpGet]
    public ActionResult ModalEditar()
    {
        return View();
    }

Javascript

$(function () {
    $('#guia-tabela').DataTable({
        processing: true,
        serverSide: true,
        ajax: "/Guia/ObterTodosPorJSON",
        columns: [
            { data: "Id" },
            { data: "Nome" },
            { data: "Sobrenome" },
            { data: "Cpf" },
            { data: "Rank" },
            {
                data: null,
                render: function (data, type, row) {
                    return "<a class='btn btn-outline-info' id='botao-editar-guia' data-id='" + row.Id + "'>Editar</a>" +
                        "<a class='btn btn-outline-danger ml-1' id='botao-excluir-guia' data-id='" + row.Id + "' href='#' >Excluir</a>";

                }
            }
        ]
    });
});


$('table').on('click', '#botao-excluir-guia', function () {
    var id = $(this).data('id');
    $.ajax({
        ulr: 'Guia/Excluir?id=' + id,
        method: 'get',
        success: function (result) {
            if (result === 1) {
                new PNotify({
                    title: 'Desativado!',
                    text: 'Usuário desativado com sucesso',
                    type: 'success'
                });

                $('#guia-tabela').DataTable().ajax.reload();

            } else {
                new PNotify({
                    title: 'Erro!',
                    text: 'Erro ao desativar usuário',
                    type: 'error'
                });
            }
        }
        });
});

$("#botao-modal-cadastrar-guia").on("click", function () {
    limparCampos();
    $("#guia-modal-cadastro").modal('show');
});

$('table').on("click", "#botao-editar-guia", function () {
    var id = $(this).data('id');
    $.ajax({
        url: '/Guia/Editar?id=' + id,
        success: function (result) {
            var data = JSON.parse(result);
            $("#campo-editar-guia-nome").val(data.Nome);
            $("#campo-editar-guia-sobrenome").val(data.Sobrenome);
            $("#campo-editar-guia-rg").val(data.Rg);
            $("#campo-editar-guia-cpf").val(data.Cpf);
            $("#campo-editar-guia-data-nascimento").val(data.DataNascimento);
            $("#campo-editar-guia-sexo").val(data.Sexo);
            $("#campo-numero-carteira-trabalho").val(data.CarteiraTrabalho);
            $("#campo-editar-guia-salario").val(data.Salario);
            $("#campo-editar-guia-categoria-habilitacao").val(data.CategoriaHabilitacao);
            $("#campo-editar-guia-rank").val(data.Rank);

            $("#guia-modal-editar").modal("show");
        }
    });
});

$("#botao-acao-editar-guia").on("click", function () {

    $.ajax({
        url: '/Guia/Update',
        method: 'post',
        dataType: 'json',
        data: {
            nome: $("#campo-editar-guia-nome").val(),
            sobrenome: $("#campo-editar-guia-sobrenome").val(),
            datanascimento: $("#campo-editar-guia-data-nascimento").val(),
            sexo: $("#campo-editar-guia-sexo").val(),
            rg: $("#campo-editar-guia-rg").val(),
            cpf: $("#campo-editar-guia-cpf").val(),
            carteiratrabalho: $("#campo-editar-guia-numero-carteira-trabalho").val(),
            categoriahabilitacao: $("#campo-editar-guia-categoria-habilitacao").val(),
            salario: $("#campo-editar-guia-salario").val(),
            rank: $("#campo-editar-guia-rank").val()
        },
        success: function (data) {
            var resultado = JSON.parse(data);
            limparCampos();
            $("#guia-modal-editar").modal('hide');
            $(function () {
                new PNotify({
                    title: 'Sucesso!',
                    text: nomeVar + ' cadastrado com sucesso',
                    type: 'success'
                });
            });
            $('#guia-tabela').DataTable().ajax.reload();
        }
    });
    limparCampos();
});

$("#botao-salvar-modal-cadastrar-guia").on("click", function () {
    var nomeVar = $("#campo-cadastro-guia-nome").val();
    $.ajax({
        url: '/Guia/Store',
        method: 'post',
        data: {
            nome: $("#campo-cadastro-guia-nome").val(),
            sobrenome: $("#campo-cadastro-guia-sobrenome").val(),
            datanascimento: $("#campo-cadastro-guia-data-nascimento").val(),
            sexo: $("#campo-cadastro-guia-sexo").val(),
            rg: $("#campo-cadastro-guia-rg").val(),
            cpf: $("#campo-cadastro-guia-cpf").val(),
            carteiratrabalho: $("#campo-cadastro-guia-numero-carteira-trabalho").val(),
            categoriahabilitacao: $("#campo-cadastro-guia-categoria-habilitacao").val(),
            salario: $("#campo-cadastro-guia-salario").val(),
            rank: $("#campo-cadastro-guia-rank").val()
        },
        success: function (data) {
            var resultado = JSON.parse(data);
            limparCampos();
            $("#guia-modal-cadastro").modal('hide');
            $(function () {
                new PNotify({
                    title: 'Sucesso!',
                    text: nomeVar + ' cadastrado com sucesso',
                    type: 'success'
                });
            });
            $('#guia-tabela').DataTable().ajax.reload();
        }
    });
});

$("#botao-update-modal-editar-guia").on("click", function () {
    var nomeVar = $("#campo-editar-guia-nome").val();
    $.ajax({
        url: '/Guia/Update',
        method: 'post',
        data: {
            nome: $("#campo-editar-guia-nome").val(),
            sobrenome: $("#campo-editar-guia-sobrenome").val(),
            datanascimento: $("#campo-editar-guia-data-nascimento").val(),
            sexo: $("#campo-editar-guia-sexo").val(),
            rg: $("#campo-editar-guia-rg").val(),
            cpf: $("#campo-editar-guia-cpf").val(),
            carteiratrabalho: $("#campo-editar-guia-numero-carteira-trabalho").val(),
            categoriahabilitacao: $("#campo-editar-guia-categoria-habilitacao").val(),
            salario: $("#campo-editar-guia-salario").val(),
            rank: $("#campo-editar-guia-rank").val()
        },
        success: function (data) {
            var resultado = JSON.parse(data);
            limparCampos();
            $("#guia-modal-editar").modal('hide');
            $(function () {
                new PNotify({
                    title: 'Sucesso!',
                    text: nomeVar + ' cadastrado com sucesso',
                    type: 'success'
                });
            });
            $('#guia-tabela').DataTable().ajax.reload();
        }
    });
})

function limparCampos() {
    $("#campo-cadastro-guia-nome").val(""),
        $("#campo-cadastro-guia-sobrenome").val(""),
        $("#campo-cadastro-guia-rg").val(""),
        $("#campo-cadastro-guia-cpf").val(""),
        $("#campo-cadastro-guia-data-nascimento").val(""),
        $("#campo-cadastro-guia-sexo").val(""),
        $("#campo-cadastro-guia-numero-carteira-trabalho").val(""),
        $("#campo-cadastro-guia-salario").val(""),
        $("#campo-cadastro-guia-categoria-habilitacao").val(""),
        $("#campo-cadastro-guia-rank").val("")
}

HTML

<button type="button" class="btn btn-primary waves-effect" id="botao-modal-cadastrar-guia">Cadastrar</button>

<div class="card">
    <div class="card-header">
        <h5>Tabela Guia</h5>
        <div class="card-header-right">    <ul class="list-unstyled card-option">        <li><i class="icofont icofont-simple-left "></i></li>        <li><i class="icofont icofont-maximize full-card"></i></li>        <li><i class="icofont icofont-minus minimize-card"></i></li>        <li><i class="icofont icofont-refresh reload-card"></i></li></ul></div>
    </div>
    <div class="card-block">
        <div class="table-responsive">
                <table id="guia-tabela" class="table table-striped table-bordered nowrap" style="width:100%">
                    <thead>
                        <tr>
                            <th>Código</th>
                            <th>Nome</th>
                            <th>Sobrenome</th>
                            <th>CPF</th>
                            <th>Rank</th>
                            <th>Ação</th>
                        </tr>
                    </thead>
                    <tbody></tbody>
                    <tfoot>
                        <tr>
                            <th>Código</th>
                            <th>Nome</th>
                            <th>Sobrenome</th>
                            <th>CPF</th>
                            <th>Rank</th>
                            <th>Ação</th>
                        </tr>
                    </tfoot>
                </table>
</div>
    </div>
</div>

@Html.Partial("~/Views/Guia/ModalCadastro.cshtml")
@Html.Partial("~/Views/Guia/ModalEditar.cshtml")

@section script{
    <script src="~/Scripts/Guia/guia-tabela.js"></script>
}

Repository

public class GuiaRepository
    {
        public List<Guia> ObterTodos()
        {
            List<Guia> guias = new List<Guia>();
            SqlCommand command = new Conexao().ObterConexao();
            command.CommandText = @"SELECT id_endereco,id, login_, sexo, senha, nome, sobrenome, numero_carteira_trabalho, 
            categoria_habilitacao, salario, cpf, rg, data_nascimento, rank_ FROM guias";
            DataTable tabela = new DataTable();
            tabela.Load(command.ExecuteReader());
            foreach (DataRow linha in tabela.Rows)
            {
                Guia guia = new Guia()
                {
                    Id = Convert.ToInt32(linha[0].ToString()),
                    Login_ = linha[1].ToString(),
                    Sexo = linha[2].ToString(),
                    Senha = linha[3].ToString(),
                    Nome = linha[4].ToString(),
                    Sobrenome = linha[5].ToString(),
                    CarteiraTrabalho = linha[6].ToString(),
                    CategoriaHabilitacao = linha[7].ToString(),
                    Salario = Convert.ToSingle(linha[8].ToString()),
                    Cpf = linha[9].ToString(),
                    Rg = linha[10].ToString(),
                    DataNascimento = Convert.ToDateTime(linha[11].ToString()),
                    Rank = Convert.ToByte(linha[12].ToString()),
                    IdEndereco = Convert.ToInt32(linha[13].ToString())

                };
                guias.Add(guia);
            }
            return guias;
        }

        public List<Guia> ObterTodosParaJSON(string start, string length)
        {
            List<Guia> guias = new List<Guia>();
            SqlCommand command = new Conexao().ObterConexao();
            command.CommandText = @"SELECT id, nome, sobrenome, cpf, rank_ FROM guias WHERE ativo = 1 ORDER BY nome OFFSET " +
                start + " ROWS FETCH NEXT "
                + length + " ROWS ONLY ";
            DataTable tabela = new DataTable();
            tabela.Load(command.ExecuteReader());
            foreach (DataRow linha in tabela.Rows)
            {
                Guia guia = new Guia()
                {
                    Id = Convert.ToInt32(linha[0].ToString()),                    
                    Nome = linha[1].ToString(),
                    Sobrenome = linha[2].ToString(),                    
                    Cpf = linha[3].ToString(),
                    Rank = Convert.ToByte(linha[4].ToString())

                };
                guias.Add(guia);
            }
            return guias;
        }

        public int Cadastrar(Guia guia)
        {
            SqlCommand command = new Conexao().ObterConexao();

            command.CommandText = @"INSERT INTO guias (sexo, nome, sobrenome, numero_carteira_trabalho, categoria_habilitacao, salario, cpf, rg, data_nascimento, rank_)
            OUTPUT INSERTED.ID
            VALUES (@SEXO, @NOME, @SOBRENOME, @NUMERO_CARTEIRA_TRABALHO, @CATEGORIA_HABILITACAO, @SALARIO, @CPF, @RG, @DATA_NASCIMENTO, @RANK_)";            
            command.Parameters.AddWithValue("@SEXO", guia.Sexo);            
            command.Parameters.AddWithValue("@NOME", guia.Nome);
            command.Parameters.AddWithValue("@SOBRENOME", guia.Sobrenome);
            command.Parameters.AddWithValue("@NUMERO_CARTEIRA_TRABALHO", guia.CarteiraTrabalho);
            command.Parameters.AddWithValue("@CATEGORIA_HABILITACAO", guia.CategoriaHabilitacao);
            command.Parameters.AddWithValue("@SALARIO", guia.Salario);
            command.Parameters.AddWithValue("@CPF", guia.Cpf);
            command.Parameters.AddWithValue("@RG", guia.Rg);
            command.Parameters.AddWithValue("@DATA_NASCIMENTO", guia.DataNascimento);
            command.Parameters.AddWithValue("@RANK_", guia.Rank);
            int id = Convert.ToInt32(command.ExecuteScalar().ToString());

            return id;

        }

        public bool Alterar(Guia guia)
        {
            SqlCommand command = new Conexao().ObterConexao();
            command.CommandText = @"UPDATE guias
            SET id_endereco = @ID_ENDERECO, login_ = @LOGIN_, sexo = @SEXO, senha = @SENHA, nome = @NOME, sobrenome = @SOBRENOME, numero_carteira_trabalho = @NUMERO_CARTEIRA_TRABALHO, categoria_habilitacao = @CATEGORIA_HABILITACAO
            salario = @SALARIO, cpf = @CPF, rg = @RG, data_nascimento = @DATA_NASCIMENTO
            WHERE id = @ID";
            command.Parameters.AddWithValue("@LOGIN_", guia.Login_);
            command.Parameters.AddWithValue("@SEXO", guia.Sexo);
            command.Parameters.AddWithValue("@SENHA", guia.Senha);
            command.Parameters.AddWithValue("@NOME", guia.Nome);
            command.Parameters.AddWithValue("@SOBRENOME", guia.Sobrenome);
            command.Parameters.AddWithValue("@NUMERO_CARTEIRA_TRABALHO", guia.CarteiraTrabalho);
            command.Parameters.AddWithValue("@CATEGORIA_HABILITACAO", guia.CategoriaHabilitacao);
            command.Parameters.AddWithValue("@SALARIO", guia.Salario);
            command.Parameters.AddWithValue("@CPF", guia.Cpf);
            command.Parameters.AddWithValue("@RG", guia.Rg);
            command.Parameters.AddWithValue("@DATA_NASCIMENTO", guia.DataNascimento);
            command.Parameters.AddWithValue("@RANK_", guia.Rank);
            command.Parameters.AddWithValue("@ID_ENDERECO", guia.IdEndereco);
            return command.ExecuteNonQuery() == 1;
        }

        public bool Excluir(int id)
        {
            SqlCommand command = new Conexao().ObterConexao();
            command.CommandText = @"UPDATE guias SET ativo = 0 WHERE id = @ID";
            command.Parameters.AddWithValue("@ID", id);
            return command.ExecuteNonQuery() == 1;

        }

        public Guia ObterPeloId(int id)
        {
            Guia guia = null;
            SqlCommand command = new Conexao().ObterConexao();
            command.CommandText = @"SELECT login_, sexo, senha, nome, sobrenome, numero_carteira_trabalho, categoria_habilitacao, salario, cpf, rg, data_nascimento, rank_, id_endereco
            FROM guias JOIN enderecos ON(guias.id_endereco = enderecos.id) WHERE guias.id = @ID";
            command.Parameters.AddWithValue("@ID", id);
            DataTable table = new DataTable();
            table.Load(command.ExecuteReader());

            if(table.Rows.Count == 1)
            {
                guia = new Guia();
                guia.Id = id;
                guia.Login_ = table.Rows[0][0].ToString();
                guia.Sexo = table.Rows[0][1].ToString();
                guia.Senha = table.Rows[0][2].ToString();
                guia.Nome = table.Rows[0][3].ToString();
                guia.Sobrenome = table.Rows[0][4].ToString();
                guia.CarteiraTrabalho = table.Rows[0][5].ToString();
                guia.CategoriaHabilitacao = table.Rows[0][6].ToString();
                guia.Salario = Convert.ToSingle(table.Rows[0][7]);
                guia.Cpf = table.Rows[0][8].ToString();
                guia.Rg = table.Rows[0][9].ToString();
                guia.DataNascimento = Convert.ToDateTime(table.Rows[0][10]);
                guia.Rank = Convert.ToByte(table.Rows[0][11].ToString());
                guia.IdEndereco = Convert.ToInt32(table.Rows[0][12].ToString());
            }
            return guia;
        }
    }

Answer 1

The next button doesn’t work, and the record counter doesn’t work. How do I tidy up?

You need to return all the items that the dataTables plugin expects. To documentation plugin details the expected items as return: draw, recordsTotal, recordsFiltered... In this way, button actions should work.

The record counter uses the recordsTotal property that must be returned in the Ajax call.

I am using Start and Length in SQL code, there is a simpler way?

Depends on how many records you have. If there are few records, I believe that returning everything at once and letting Datatables do the automatic paging is more advantageous. Otherwise, I like your approach.

How do I make the search work?

The search is also server-side. In this case, within your method you need to do the treatment to return the search.

public ActionResult ObterTodosPorJSON()
{
  string start = Request.QueryString["start"];
  string length = Request.QueryString["length"];

  //Use este parâmetro para realizar a busca na sua tabela
  string search = Request.QueryString["search[value]"];

  //Restante do seu código....
 }

And how can I do the stylization of this table (mainly take the 3 dots of the upper left corner)?

To documentation has the style examples you can customize.