ASP NET MVC 5 Dapper with SQL Server?

Question

I’m creating an app with ASP.NET MVC5, DAPPER, C# And SQL SERVER. The problem, is that in the data listing, is bringing the empty fields, except the ID field, which comes zero (0).

If anyone knows the solution to this problem,.

Class Clientemodel

public class ClienteModel
{
    [Display(Name = "ID")]
    public int Idcl { get; set; }

    [Required(ErrorMessage = "O nome é obrigatório.")]
    [Display(Name="NOME")]
    public string Nome { get; set; }

    [Display(Name = "TELEFONE")]
    public string Telef { get; set; }
}

Repository:

public class ClienteRepositorio
{
    public SqlConnection con;

    private void Conexao()
    {
        string strConexao = ConfigurationManager.ConnectionStrings["LojaMVC5Dapper.Properties.Settings.SqlConexao"].ConnectionString.ToString();
        con = new SqlConnection(strConexao);
    }

    //para gravar o cliente
    public void GravaClienteR(ClienteModel cliM)
    {
        try
        {
            Conexao();
            con.Open();
            con.Execute("GravaCliente",cliM,commandType: CommandType.StoredProcedure);
            con.Close();
        }
        catch (Exception erro)
        { throw erro; }
    }

    //para buscar os clientes
    public List<ClienteModel> BuscaClienteR()
    {
        try
        {
            Conexao();
            con.Open();
            IList<ClienteModel> listaCliente = SqlMapper.Query<ClienteModel>(con, "BuscaCliente").ToList();
            con.Close();
            return listaCliente.ToList();
        }
        catch (Exception erro)
        { throw erro; }
    }

    //para actualizar o cliente
    public void ActualizaClienteR(ClienteModel clienteM)
    {
        try
        {
            Conexao();
            con.Open();
            con.Execute("ActulizaCliente", clienteM, commandType: CommandType.StoredProcedure);
            con.Close();
        }
        catch (Exception erro)
        { throw erro; }
    }

    //para eliminar o cliente
    public bool EliminaClienteR(int id)
    {
        try
        {
            DynamicParameters param=new DynamicParameters();
            param.Add("@id",id);

            Conexao();
            con.Open();
            con.Execute("EliminaCliente", param, commandType: CommandType.StoredProcedure);
            con.Close();

            return true;
        }
        catch (Exception erro)
        { throw erro; }
    }
}

Controller:

public class ClienteController : Controller
{
    //busca todos os clientes
    // GET: /Cliente/
    public ActionResult Index()
    {
        ClienteRepositorio cliRepo = new ClienteRepositorio();
        return View(cliRepo.BuscaClienteR());
    }

    //
    // GET: /Cliente/Details/5
    public ActionResult Details(int id)
    {
        return View();
    }

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

    //
    // POST: /Cliente/Create
    [HttpPost]
    public ActionResult GravaCiente(ClienteModel objCli)
    {
        try
        {
            if (ModelState.IsValid)
            {
                ClienteRepositorio cliRepo = new ClienteRepositorio();
                cliRepo.GravaClienteR(objCli);
                ViewBag.Message = "Dados gravados com sucesso!";
            }

            return RedirectToAction("BuscaTodosClientes");
        }
        catch
        {
            return View();
        }
    }

    // para editar os dados do cliente
    // GET: /Cliente/Edit/5
    public ActionResult EditarCliente(int id)
    {
        ClienteRepositorio cliRepo = new ClienteRepositorio();

        return View(cliRepo.BuscaClienteR().Find(cliente=>cliente.Idcl==id));
    }

    //pra actualizar os dados do cliente
    // POST: /Cliente/Edit/5
    [HttpPost]
    public ActionResult ActualizaCliente(int id, ClienteModel objCli)
    {
        try
        {
            ClienteRepositorio cliRepo = new ClienteRepositorio();
            cliRepo.ActualizaClienteR(objCli);

            return RedirectToAction("BuscaTodosClientes");
        }
        catch
        {
            return View();
        }
    }

    //pra eliminar o cliente
    // GET: /Cliente/Delete/5
    public ActionResult Delete(int id, FormCollection collection) 
    {
        return View();

    }

    //
    // POST: /Cliente/Delete/5
    [HttpPost]
    public ActionResult EliminaCliente(int id1)
    {
        try
        {
            ClienteRepositorio cliRepo = new ClienteRepositorio();
            if (cliRepo.EliminaClienteR(id1))
            { ViewBag.AlertMsg = "Dados eliminados com Sucesso!"; }
            return RedirectToAction("BuscaTodosClientes");
        }
        catch
        {
            return RedirectToAction("BuscaTodosClientes");
        }
    }
}

View:

@model IEnumerable<LojaMVC5Dapper.Models.ClienteModel>

@{
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Idcl)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Nome)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Telef)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Idcl)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Nome)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Telef)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { /* id=item.PrimaryKey */ }) |
            @Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
            @Html.ActionLink("Delete", "Delete", new { /* id=item.PrimaryKey */ })
        </td>
    </tr>
}

</table>

Presentation:

I am beginner in web programming, so thank you for your understanding.

Table Client:

Observing: the table name is CLIENT.

Procedure Search engine:

    ---para buscar os clientes-----
create procedure BuscaCliente
as
begin 
select id_cl,nome_cl,telef_cl from CLIENTE order by nome_cl asc
end

Answer 1

Edit the Stored Procedure and renamed the fields according to the name of the respective class fields ClienteModel. The Dapper uses the name relation of the results obtained with those of the class, i.e. names need to be the same

alter procedure BuscaCliente as
begin 
    select id_cl as Idcl, 
           nome_cl as Nome,
           telef_cl as Telef 
    from CLIENTE order by nome_cl asc
end

With this change renaming fields equal to this in the class ClienteModel the results will be loaded.

References:

• Dapper dot net
• Dapper . NET Tutorial I
• ADO . NET - Using Dapper . net Micro ORM
• Dapper and Anonymous Types
• Stored Procedure - Stored Procedure
• Github Stackexchange/Dapper-dot-net