How to list data with inner join with mvc 5, c #, dapper, and sql server?

1

I have the following difficulties:

1 - View data from a select with inner join. The fields in the other tables do not all appear in the select, and those that appear have a value of zero (0).

2 - Write the data in the database. Even if you apply "[Bind (Exclude=" Id ")]", the "Id" field continues to appear when creating new. Next I will post the codes and some images for better understanding.

MODEL

 public class VendaModel
{

    [Display(Name = "ID")]
    public int id_vd { get; set; }
     [Display(Name = "PRODUTO")]
    public int id_pd { get; set; }
     [Display(Name = "CLIENTE")]
    public int id_cl { get; set; }
    [Display(Name = "DATA")]
    public DateTime data_vd { get; set; }
    [Display(Name = "QTD")]
    [Required(ErrorMessage = "A quantidade é obrigatória!")]
    public int qtd_vd { get; set; }

}

BD DIAGRAM REPOSITORY

publicclassVendaRepositorio{privateconststringBuscaTodosSQL="select id_vd,nome_pd,preco_pd,qtd_vd,nome_cl,telef_cl from VENDA inner join CLIENTE on VENDA.id_cl=CLIENTE.id_cl inner join PRODUTO on VENDA.id_pd=PRODUTO.id_pd";
    private const string GravaVendaSQL = "INSERT INTO VENDA (id_pd,id_cl,data_vd,qtd_vd) VALUES(@idpd,@idcl,@data,@qtd)";

    public SqlConnection con;

    //para a conexão com a bd
    private void Conexao()
    {
        string strConexao = ConfigurationManager.ConnectionStrings["LojaMVC5Dapper.Properties.Settings.SqlConexao"].ConnectionString.ToString();
        con = new SqlConnection(strConexao);
    }

    //para gravar a venda
    public void GravaVendaR(VendaModel vendM)
    {
        try
        {
            Conexao();
            con.Open();
            con.Execute(GravaVendaSQL, vendM, commandType: CommandType.Text);
            con.Close();
        }
        catch (Exception erro)
        { throw erro; }
    }

    //para buscar as vendas
    public List<VendaModel> BuscaVendaR()
    {
        try
        {
            Conexao();
            con.Open();
            IList<VendaModel> listar = SqlMapper.Query<VendaModel>(con, BuscaTodosSQL).ToList();
            return listar.ToList();
        }
        catch (Exception erro)
        { throw erro; }
    }
}

CONTROLLER

public class VendaController : Controller
{
    //
    // GET: /Venda/
    public ActionResult Index()
    {
        VendaRepositorio vendaRepo = new VendaRepositorio();
        return View(vendaRepo.BuscaVendaR());
    }

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

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

    //
    // POST: /Venda/Create
    [HttpPost]
    public ActionResult Create(VendaModel objVenda)
    {
        try
        {
            // TODO: Add insert logic here
            if (ModelState.IsValid)
            {
                VendaRepositorio vendaRepo = new VendaRepositorio();
                vendaRepo.GravaVendaR(objVenda);
                ViewBag.Messaga = "Dados gravados com sucesso!";
            }

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

    //
    // GET: /Venda/Edit/5
    public ActionResult Edit(int id)
    {
        return View();
    }

    //
    // POST: /Venda/Edit/5
    [HttpPost]
    public ActionResult Edit(int id, FormCollection collection)
    {
        try
        {
            // TODO: Add update logic here

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

    //
    // GET: /Venda/Delete/5
    public ActionResult Delete(int id)
    {
        return View();
    }

    //
    // POST: /Venda/Delete/5
    [HttpPost]
    public ActionResult Delete(int id, FormCollection collection)
    {
        try
        {
            // TODO: Add delete logic here

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

VIEW INDEX

@model IEnumerable<LojaMVC5Dapper.Models.VendaModel>

@{
    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.id_vd)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.id_pd)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.id_cl)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.data_vd)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.qtd_vd)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.id_vd)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.id_pd)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.id_cl)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.data_vd)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.qtd_vd)
        </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>

VIEW CREATE

@model LojaMVC5Dapper.Models.VendaModel



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

<h2>Create</h2>




@using (Html.BeginForm()) 
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>VendaModel</h4>
        <hr />
        @Html.ValidationSummary(true)

        <div class="form-group">
            @Html.LabelFor(model => model.id_vd, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.id_vd)
                @Html.ValidationMessageFor(model => model.id_vd)
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.id_pd, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.id_pd)
                @Html.ValidationMessageFor(model => model.id_pd)
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.id_cl, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.id_cl)
                @Html.ValidationMessageFor(model => model.id_cl)
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.data_vd, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.data_vd)
                @Html.ValidationMessageFor(model => model.data_vd)
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.qtd_vd, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.qtd_vd)
                @Html.ValidationMessageFor(model => model.qtd_vd)
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>

RESULTS [! [insert description of image here] [2]] [2]

    
asked by anonymous 19.11.2016 / 01:13

0 answers