Home / Questions / 307629
Accepted
Fran
Asked: 2020-11-12 10:28:54 +0800 CST 2020-11-12 10:28:54 +0800 CST

Store procedure sql server with asp.net mvc 5

  • 772

I have a table in sql server called Brand, it contains four fields:

IIDMARCA int, 
NOMBRE varchar, 
DESCRIPCION varchar, 
BHABILITADO int

and I have a stored procedure that queries three of those four fields

CREATE PROCEDURE SP_LISTAR_MARCAS
AS
BEGIN
SELECT IIDMARCA, NOMBRE, DESCRIPCION
FROM Marca
WHERE BHABILITADO = 1
END

In my asp.net project I have the following drawback:

enter image description here

If in the procedure I place select * from marca, in my view I place the column item.b enabled, the problem is eliminated, but I am not interested in listing all the columns, and I do not know how to solve this, I appreciate in advance all the help you can give me, if you know requires more information I will be pending.

this would be my controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

using prototipo_03.Models;

namespace procedimientos_almacenados.Controllers
{
    public class MarcaController : Controller
    {
        BDPasajeEntities bd = new BDPasajeEntities();
        // GET: Marca
        public ActionResult Index()
        {
            ListarMarcas();
            return View();
        }

        public void ListarMarcas()
        {
            List<Marca> ListaMarca = new List<Marca>();

            using (bd)
            {
                var ListaMarca3 = bd.SP_LISTAR_MARCAS().ToList();
                foreach (var item in ListaMarca3)
                {
                    var asignar = new Marca
                    {
                        IIDMARCA = item.IIDMARCA,
                        NOMBRE = item.NOMBRE,
                        DESCRIPCION = item.DESCRIPCION,
                    };
                    ListaMarca.Add(asignar);
                }
                ViewBag.ListaMarca = ListaMarca;
            } 
    }
}

this is my view

@using prototipo_03.Models

@{
    ViewBag.Title = "Index";
    List<Marca> ListaMarca = (List<Marca>)ViewBag.ListaMarca;
}

<table class="table table-bordered table-hover">
    <thead>
        <tr>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in ListaMarca)
        {
        <tr>
            <td>@item.IIDMARCA</td>
            <td>@item.NOMBRE</td>
            <td>@item.DESCRIPCION</td>
        </tr>
        }
    </tbody>
</table>

I have tried to implement my own model that calls MarcaCLS to differentiate the Marca that comes from the model brought from the database, but I am limited in this matter, which I did in the same way but I don't know how to make it work

 public partial class Marca
    {
        public int IIDMARCA { get; set; }
        public string NOMBRE { get; set; }
        public string DESCRIPCION { get; set; }
        public Nullable<int> BHABILITADO { get; set; }
    }

enter image description here

I have to list all the columns in the procedure and list them all in the controller and in the view for it to run correctly, but I'm not interested in listing all the columns in the table.

sql-server

2 Answers

  1. Observations:

    • The ViewBags, The ViewData go in the same action, they will give you more control of what you see.
    • Where do you define MarcaCLS? Isn't it supposed to be just Marca?

    Stored Procedure:

    SELECT IIDMARCA, NOMBRE, DESCRIPCION, BHABILITADO FROM Marca WHERE BHABILITADO = 1

    Controller:

    using prototipo_03.Models;

namespace procedimientos_almacenados.Controllers
{
    public class MarcaController : Controller
    {
        BDPasajeEntities bd = new BDPasajeEntities();
        // GET: Marca
        public ActionResult Index()
        {
            //ListarMarcas();
            ViewBag.ListaMarca = ListaMarca();
            return View();
        }

        public List<Marca> ListarMarcas()
        {
            List<Marca> listaDeMarcas = new List<Marca>();

            using (bd)
            {
                var ListaMarca3 = bd.SP_LISTAR_MARCAS().ToList();
                foreach (var item in ListaMarca3)
                {
                    var asignar = new Marca
                    {
                        //Ojo con los nombres de tu clase
                        iidMarca = item.IIDMARCA,
                        nombreMarca = item.NOMBRE,
                        descripcionMarca = item.DESCRIPCION,
                        //Aqui viene el problema, falta especificar la propiedad 
                        bhabilitado = item.BHABILITADO 
                    };
                    listaDeMarcas.Add(asignar);
                }
                //Fin del foreach
            } //Fin del using
            return listaDeMarcas;
         } //Fin del método.
     }
 }
    • 0
  2. Best Answer

    In the "model explorer" => "Edit Function Import" I changed the option "Returns a collection of" from Entity which was where I had it to "Complex", and so it returns the columns I want, without bringing the entire model of the table

    enter image description here

    • 0