Mount a Datatable ASP.NET MVC

Question

I need a help on how to mount my datatable inside my Controller, from a search.

First is a filter with an Optionlist that chooses csv base name, for example Base01.csv, Base02.csv and a Num_cli text field where we put a 5 digit number.

Then comes my JS Importacaobase.js

$(document).ready(function () {


FiltrarDados();
//debugger;

$('body').on('change', '#id_arquivo', function () {
    FiltrarDados();
});

$('body').on('click', '#btnFiltrar', function () {
    FiltrarDados();
});

//Botões de paginação
$('body').on('click', '.pagination li a', function () {
    FiltrarDados($(this).attr('pagina'));
});

});

Filtered Function(numeroPagina) { Messaging lock("Loading...");

$.ajax({
    type: 'POST',
    url: 'ImportacaoBase/Filtrar',
    data: {
        "Id_Arquivo": $('#id_arquivo').val(),
        "Num_Cli": $('#num_cli').val(),
        "NumPagina": numeroPagina
    },
    dataType: 'html',
    cache: false,
    async: true,
    success: function (resultado) {
        $('#divResultadoPesquisa').slideUp("", function () {
            $('#divResultadoPesquisa').html(resultado);
            $('#divResultadoPesquisa').slideDown("");
        });

        DesbloquearPagina();
    },
    fail: function (resultado) {
        ExibirMensagem(resultado.responseText);
        DesbloquearPagina();
    }
});

}

My Controller Importacaobasecontroller.Cs contains the Filter method

 public ActionResult Filtrar(int Id_Arquivo, string Num_Cli, int? NumPagina)
    {
        try
        {

            PaginacaoManualVO paginacao = new PaginacaoManualVO() { PageNumber = (NumPagina ?? 1), RowspPage = 10 };

            BaseIC baseic = new BaseIC();

            var resultado = new List<TB_Base_IC>();

            switch (Id_Arquivo)
            {
                case (int)ETipoBaseImportacao.IC:
                    resultado = baseic.ListarBase(Num_Cli, paginacao);
                    break;



                default:
                    break;
            }

            ViewBag.PaginacaoManual = paginacao;

            return PartialView("PartialResultadoPesquisa", resultado);

        }
        catch (Exception ex)
        {
            ExibirMensagem(ex.Message, ETipoMensagem.Erro, 99);
            return PartialView("_ControleMensagem");
        }
    }

This method List calls where I am trying to mount the Datatable in the other method called Listarbase, I can not mount the query to bring the data, I wonder how best to do below:

        public List<TB_Base_IC> ListarBase(string Num_Cli, PaginacaoManualVO paginacao)
    {
        using (DB_MesaPrecosContext context = new DB_MesaPrecosContext())
        {
            List<TB_Base_IC> listaBaseIc = new List<TB_Base_IC>();


            DataTable dt = new DataTable();



            paginacao.TotalRegistros = Convert.ToInt32(_recebeLogQtdLinhas.Qtd_Linhas);

            if (!string.IsNullOrEmpty(Num_Cli))
            {
                listaBaseIc = context.TB_Base_IC.Where(busca => busca.NUM_CLI == Num_Cli.Trim().ToUpper())
                                                    .OrderBy(busca => busca.NUM_CLI)
                                                    .Skip(paginacao.PageNumber * paginacao.RowspPage - paginacao.RowspPage).Take(paginacao.RowspPage).ToList();
            }
            else
                listaBaseIc = context.TB_Base_IC.OrderBy(busca => busca.NUM_CLI).Skip(paginacao.PageNumber * paginacao.RowspPage - paginacao.RowspPage).Take(paginacao.RowspPage).ToList();

            return listaBaseIc;
        }
    }

The error below occurs: The Parameters Dictionary contains a null entry for Parameter 'Id_file' of non-nullable type 'System.Int32' for method 'System.Web.Mvc.Actionresult Filter(Int32, System.String, System.Nullable`1[System.Int32])' in 'Itau.DPE.Mesaprecos.Web.Controllers.Importacaobasecontroller'. An optional Parameter must be a Reference type, a nullable type, or be declared as an optional Parameter. Name of the parameter: Parameters Description: An untreated exception occurred during the execution of the current web request. Examine stack tracking for more information about the error and where it originated in the code.

The filtering method in debug brings the error, the result variable always comes with 0, below the result of my Partialresultsearch:

@using Empresa.DCR.Comum.Utils.Utilitarios;
@using Empresa.DCR.Comum.Utils.Enumeradores;
@using Empresa.DPE.Projeto.DAO.Enumeradores;
@model System.Data.DataTable
@using System.Data

<div class="table-responsive">
    @{
        if (Model != null)
        {
        <table id="gridSquad" class="table table-striped Exp-Table table-hover table-vcenter pad-no">

            @*//inclusao do if*@
            
            <tr class="table-tr">
                @foreach (System.Data.DataColumn col in Model.Columns)
                {
                    <th>@col.Caption</th>
                } 
            </tr>

            @foreach (System.Data.DataRow row in Model.Rows)
            { 
                <tr>
                    @foreach (var cell in row.ItemArray)
                    {
                        if (cell != null)
                        {
                            <td>@cell.ToString()</td>
                        }else
                        {
                            <td></td>
                        }
                    } 
                </tr>               
                       
            }
        </table>
            Html.RenderPartial("_Paginacao");
        }
        else
        {
        <table class="table table-striped Exp-Table">
            <tr class="table-tr">
                <th colspan="100%">Não houve resultados para a pesquisa. </th>
            </tr>
        </table>
        }
    }
</div>

I did not understand right why the parameter, thank you.

Answer 1

Hello again folks,

The goal was to assemble a result DATATABLE according to the choice of a name in an Htmlrazor optionlist, after clicking the filter button, a method would assemble a DATATABLE with a query searching the table in SQL base and paging on screen, as below:

public DataTable ListarBase(string Num_Cli, ref PaginacaoManualVO paginacao)
    {
        using (var con = Utilitario.RecuperaConexaoToken())
        {
            if (con.State != System.Data.ConnectionState.Open)
                con.Open();


            //Total Linhas
            string query = "SELECT COUNT(ID_Importacao) FROM [dbo].[TABELA] WITH (NOLOCK)";

            if (!string.IsNullOrEmpty(Num_Cli))
                query += string.Format(" WHERE NUM_CLI = '{0}'", Num_Cli);

            using (SqlCommand cmd = new SqlCommand(query, con))
                paginacao.TotalRegistros = Int32.Parse(cmd.ExecuteScalar().ToString());


            //Linhas
            DataTable dt = new DataTable();

            query = "SELECT * FROM [dbo].[TB_Base_IC] WITH (NOLOCK)";

            if (!string.IsNullOrEmpty(Num_Cli))
                query += string.Format(" WHERE NUM_CLI = '{0}'", Num_Cli);

            query += string.Format(" ORDER BY CURRENT_TIMESTAMP OFFSET {0} ROWS FETCH NEXT 10 ROWS ONLY;", (paginacao.PageNumber * paginacao.RowspPage - paginacao.RowspPage));

            SqlDataAdapter adp = new SqlDataAdapter(query, con);
            adp.Fill(dt);

            if (con.State == System.Data.ConnectionState.Open)
                con.Close();

            return dt; 
        }
    }

This method returns to the other Filter method by entering the switch case.

Thank you.