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; }
}
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]