ado.net stored procedures asp.net mvc 4

Question

ado.net stored procedures asp.net mvc 4

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I am working on n-layers asp.net mvc 4 c # web and I want to list an SP that contains inner join and that is listed when an ID is sent . the topic esque to list I do it in the following way in my DAL:

public List<Solicitudes> ListarSolicitudes()
    {
        var solicitudes = new List<Solicitudes>();
        try
        {
            con.Open();

            var query = new SqlCommand("select * from solicitudes", con);
            using (var dr = query.ExecuteReader())
            {
                while (dr.Read())
                {
                    Solicitudes objSolicitud = new Solicitudes();
                    objSolicitud.SolicitudesID = Convert.ToInt32(dr[0].ToString());
                    objSolicitud.FechaEmision = Convert.ToDateTime(dr[1].ToString());
                    objSolicitud.Observacion = dr[4].ToString();
                    solicitudes.Add(objSolicitud);
                }
            }
            con.Close();
        }
        catch (Exception)
        {

            throw;
        }
        return solicitudes;
    }

my problem is that to list I need a model like:

Solicitudes objSolicitud = new Solicitudes();

but being SP with inner join, some attributes of several models are required, I have read in forums that I have to make a generic list but I do not know how to do that, if you could help me please.

c# poo asp.net mvc

asked by Luis Vega 07.11.2017 в 15:56

source

2 answers

HTTP2 in Nginx as a proxy (proxy_pass + upstream) Error with RealmSearchView with my Realm Data Base

score 0 · Answer 1

Do a generic class where you have all the fields that you have in your sql query, because you can not return in your method 2 types of Objects.

public class ClaseGenerica
    {
 public int    SolicitudesID {get;set;}
 public DateTime FechaEmision{get;set;}
 public string Observacion {get;set;}
 // Y Aquí agregas también las propiedades de las clases que quieras unir con inner join
    }

public List<Solicitudes> ListarSolicitudes()
    {
        var solicitudes = new List<ClaseGenerica>();
        try
        {
            con.Open();

            var query = new SqlCommand("select * from solicitudes", con);
            using (var dr = query.ExecuteReader())
            {
                while (dr.Read())
                {
                    ClaseGenerica objSolicitud = new ClaseGenerica();
                    objSolicitud.SolicitudesID = Convert.ToInt32(dr[0].ToString());
                    objSolicitud.FechaEmision = Convert.ToDateTime(dr[1].ToString());
                    objSolicitud.Observacion = dr[4].ToString();
                    solicitudes.Add(objSolicitud);
                }
            }
            con.Close();
        }
        catch (Exception)
        {
            throw;
        }
        return solicitudes;
    }

score 0 · Answer 2

Within the model I have created another class with the attributes that I need "ListSubscriptions":

    public class Solicitudes
{
    [Key]
    public int SolicitudesID { get; set; }
    public DateTime FechaEmision { get; set; }
    public DateTime FechaInicio { get; set; }
    public DateTime FechaFin { get; set; }
    public string Observacion { get; set; }
    public int UsuariosID { get; set; }
    public int TipoSolicitudesID { get; set; }
    public int CondicionesID { get; set; }

    //public virtual Condiciones Condiciones { get; set; }
}
public class ListarSolicitudes
{
    public int SolicitudesID { get; set; }
    public DateTime FechaEmision { get; set; }
    public string NombreArea { get; set; }
    public string nombreTipoSolicitud { get; set; }
    public int Estado { get; set; }
}

}

in the DAL I did it in the following way:

public List<ListarSolicitudes> ListarSolicitudes(int id)
    {
        var solicitudes = new List<ListarSolicitudes>();
        try
        {


            var cmd = new SqlCommand("ListarSolicitudesxID @id", con);
            cmd.Parameters.AddWithValue("@id",id);
            con.Open();
            using (var dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    ListarSolicitudes objSolicitud = new ListarSolicitudes();
                    objSolicitud.SolicitudesID = Convert.ToInt32(dr[0].ToString());
                    objSolicitud.FechaEmision = Convert.ToDateTime(dr[1].ToString());
                    objSolicitud.NombreArea = dr[2].ToString();
                    objSolicitud.nombreTipoSolicitud = dr[3].ToString();
                    objSolicitud.Estado = Convert.ToInt32(dr[4].ToString());
                    solicitudes.Add(objSolicitud);
                }
            }
            con.Close();
        }
        catch (Exception)
        {

            throw;
        }
        return solicitudes;
    }

and finally call it in the view like this:

@model List<Intranet.BE.ListarSolicitudes>

@{
    Layout = "~/Views/Shared/_LayoutUsuario.cshtml";
    ViewBag.Title = "Index";
}
<br />
<h2><strong>Lista de Solicitudes</strong></h2>
@Html.ActionLink("Registrar Solicitud", "Create", "Format")
<br /><br />
<table class="table">
    <tr>
        <th>#</th>
        <th>FECHA EMISION</th>
        <th>AREA</th>
        <th>FORMATO</th>        
        <th>VER</th>
    </tr>
        @foreach (var item in Model)
    {
        <tr>
            <td>@item.SolicitudesID</td>
            <td>@item.FechaEmision</td>
            <td>@item.NombreArea</td>
            <td>@item.nombreTipoSolicitud</td>
            <td>@item.Estado</td>
        </tr>
    }
</table>