Send stored procedure result of the controller to the view

Question

Send stored procedure result of the controller to the view

Navigation

#1 by (3 votes)

1

I need to send the result of my stored procedure from the controller to the view.

This is on my controller:

using (SqlConnection con = new SqlConnection("data source=DESKTOP-99IPRRD;initial catalog=RecursosHumanos;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot"))
{

    SqlCommand cmd = new SqlCommand("sp_DeduccionesNombre", con);
    cmd.CommandType = CommandType.StoredProcedure;

    SqlParameter paraminicio = new SqlParameter();
    paraminicio.ParameterName = "@inicio";
    paraminicio.Value = FechaDesde;

    SqlParameter paramfinal = new SqlParameter();
    paramfinal.ParameterName = "@final";
    paramfinal.Value = FechaHasta;

    SqlParameter paramareID = new SqlParameter();
    paramareID.ParameterName = "@areID";
    paramareID.Value = Area.AreId;

    SqlParameter paramarageID = new SqlParameter();
    paramarageID.ParameterName = "@ageID";
    paramarageID.Value = Agencia.AgeId;


    cmd.Parameters.Add(paraminicio);
    cmd.Parameters.Add(paramfinal);
    cmd.Parameters.Add(paramareID);
    cmd.Parameters.Add(paramarageID);

    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
}

This is my stored procedure:

drop procedure sp_DeduccionesNombre
go

create procedure sp_DeduccionesNombre @inicio datetime, @final datetime, @areID int, @ageID int
as

    select distinct d.DedId, d.DedDescripcion
     from Tbl_HistorialLaboral hl
     inner join Tbl_Empleado e on e.empid = hl.EmpId
     inner join Tbl_DetalleDeduccionesEmpleado de on de.EmpId = e.EmpId
     inner join Tbl_Deducciones d on d.DedId = de.DedId
     where hl.areID = @areID and hl.AgeId = @ageID
     and (de.DetDedEmpFecha between @inicio and @final)

This is the result of my stored procedure that I try to show:

sql sql-server ado.net

asked by Marco Eufragio 30.10.2018 в 00:29

source

1 answer

Eliminating cross repeated elements in an array of objects Table hours JavaScript

score 3 · Accepted Answer

I see that you are using the ExecuteNonQuery method. This method is used when you do not need to review the results of an SQL statement. If you need to review the result, then you must use the ExecuteReader method, which returns a DataReader. This DataReader will allow you to navigate through the records / fields and return them to your view through a model.

Suppose you defined a Deduction class

public class Deduccion
{
     public int DedId {get; set;}
     public string DedDescription {get; set; }
}

Then you can use the following code ...

List<Deduccion> deducciones=new List<Deduccion>(); 
using (SqlConnection con = new SqlConnection("data source=DESKTOP-99IPRRD;initial catalog=RecursosHumanos;integrated 

security=True;MultipleActiveResultSets=True;App=EntityFramework&quot"))


       {

            SqlCommand cmd = new SqlCommand("sp_DeduccionesNombre", con);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter paraminicio = new SqlParameter();
            paraminicio.ParameterName = "@inicio";
            paraminicio.Value = FechaDesde;

            SqlParameter paramfinal = new SqlParameter();
            paramfinal.ParameterName = "@final";
            paramfinal.Value = FechaDesde;

            SqlParameter paramareID = new SqlParameter();
            paramareID.ParameterName = "@areID";
            paramareID.Value = Area.AreId;

            SqlParameter paramarageID = new SqlParameter();
            paramarageID.ParameterName = "@ageID";
            paramarageID.Value = Agencia.AgeId;

            cmd.Parameters.Add(paraminicio);
            cmd.Parameters.Add(paramfinal);
            cmd.Parameters.Add(paramareID);
            cmd.Parameters.Add(paramarageID);    

            con.Open();
            var reader = cmd.ExecuteReader();
            while(reader.read()) //Hay datos, entonces armamos el modelo para regresarlo a la vista
            {
                  deducciones.Add( 
                     new Deduccion { 
                           DedId = reader.GetInt32(reader.GetOrdinal("DedId")),                 
                           DedDescripcion = reader.GetString(reader.GetOrdinal("DedDescripcion"))
                     });
            }
            con.Close();

        }
   return View(deducciones); //Regresamos el modelo a la vista.