ASP NET MVC 5 Dapper with SQL Server?

1

I'm creating an application with ASP.NET MVC5 , DAPPER , C# E SQL SERVER . The problem is that in the data listing, you are bringing the empty fields, except the ID field, which comes to zero (0).

If anyone knows the solution to this problem, thank you very much.

ClientClass Class

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'mabeginnerinwebprogramming,sothankyouforyourunderstanding.

Clienttable:

  

Note: The table name is CLIENT .

Procedure Customer Search :

    ---para buscar os clientes-----
create procedure BuscaCliente
as
begin 
select id_cl,nome_cl,telef_cl from CLIENTE order by nome_cl asc
end
    
asked by anonymous 06.11.2016 / 18:50

1 answer

1

Edit the Stored Procedure and rename the fields as the name of their respective fields in the ClienteModel class. Dapper uses the list of names of the results obtained with those of the class, that is, the names need to be equal

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 the fields equal to this in class ClienteModel the results will be loaded.

References:

07.11.2016 / 02:27