Showing the data that brings the stored procedure to an ASP.NET MVC 5 view

Question

Showing the data that brings the stored procedure to an ASP.NET MVC 5 view

Navigation

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

-1

I'm trying to show what my stored procedure brings and I want to send it to view:

This is how I sent it:

 var Fechainicio = new SqlParameter
        {
            ParameterName = "inicio" , 
            Value = FechaDesde

        };

        var FechaFinal = new SqlParameter
        {
            ParameterName = "final",
            Value = FechaHasta

        };

        var empleadoid = new SqlParameter
        {
            ParameterName = "emp",
            Value = 2393

        };

        var deduc = db.Database.SqlQuery<PlanillaController>("exec sp_DetalleDeduccionesEmpleado @inicio, @final, @emp", Fechainicio, FechaFinal, empleadoid).ToList<PlanillaController>();
        ViewBag.deduc = deduc.ToList();

Already in the view it comes like this:

foreach (var Deduccionees in (IEnumerable<RecursosHumanos.Models.sp_DetalleDeduccionesEmpleado_Result>)ViewBag.Deduc)
  {
    <th>@Deduccionees</th>
  }

This is the error

{"No se puede convertir un objeto de tipo 'System.Collections.Generic.List'1[RecursosHumanos.Controllers.PlanillaController]' al tipo 'System.Collections.Generic.IEnumerable'1[RecursosHumanos.Models.sp_DetalleDeduccionesEmpleado_Result]'."}

this is the data that the stored procedure should bring me

this is the sp:

create procedure sp_DetalleDeduccionesEmpleado @inicio datetime, @final datetime, @emp int
as
select 
ded.DedId,
sum(ded.DetDedEmpValor),
emp.EmpId


from Tbl_DetalleDeduccionesEmpleado ded
inner join Tbl_Deducciones tip
 on tip.DedId = ded.DedId
 inner join Tbl_Empleado emp
 on emp.EmpId = ded.EmpId
 where ded.DetDedEmpFecha >=  @inicio and 
 ded.DetDedEmpFecha <= @final and 
 ded.EmpId = @emp
 group by ded.DedId, emp.EmpId

c# asp.net asp.net-mvc asp.net-mvc-5 asp.net-mvc-4

asked by Marco Eufragio 24.10.2018 в 15:47

source

2 answers

2

You should define the same type of data in the action and the view

@var lista = (List<RecursosHumanos.Models.PlanillaController>)ViewBag.Deduc;
@foreach (var Deduccionees in lista)
{
    <th>@Deduccionees</th>
}

If in the action you define PlanillaController in the view you must define the same, you will see in the cast that I make the same class is used

I do not know where you got in the view the use of sp_DetalleDeduccionesEmpleado_Result if you make the SqlQuery do not kill this type of data

answered by 24.10.2018 в 19:48

Function of 2 variables of different data types how to solve it? I do not understand the behavior of the variables

score 2 · Accepted Answer

Good day.

First, we need to know a little about your stored procedure and the data it returns. As for the moment we do not have that, I suggest the following since I see you have several bad points in the code:

Assign a generic class with the result format of your stored procedure. Assuming that your stored procedure ends with a Select * from @table or that from the beginning you declare the result as a table. Ex:

 public class ClaseGenerica
   {
     public int ID_columnaEntero {get;set;}
     public DateTime columnaFecha {get;set;}
     public string columnaString {get;set;}
     //Y sigues colocando propiedades(columnas) con su tipo correspondiente de datos en 
     //base al resultado de tu procedimiento almacenado
  }

Based on the result of your SP, your class could be like this:

public class DetalleDeduccionesEmpleado
{
  public int DedId {get;set;} //O si es string el codigo, lo cambias
  public decimal valor {get;set;} //como es una suma a ti te muestra valor, ya que no le asignaste nombre a la columna
  public string EmpId {get;set;} //O en su defecto el tipo de datos que es
}

Then you do everything I wrote you before, hoping to solve your problem.

Assign the necessary parameters for the SP and then save the result in a list of the type claseGenerica:

In your case, I edit how it should be

SqlParameter @Fechainicio = new SqlParameter(){
  ParameterName = "@Fechainicio",
  DbType = DbType.Datetime, //Tipo de datos
  Value = FechaDesde
};    

 SqlParameter @FechaFinal = new SqlParameter(){
  ParameterName = "@FechaFinal",
  DbType = DbType.Datetime, //Tipo de datos
  Value = FechaHasta
};

 SqlParameter @empleadoid = new SqlParameter(){
  ParameterName = "@empleadoid",
  DbType = DbType.String, //Tipo de datos o Int32 si es entero y le quitas las comillas al valor en Value
  Value = "2393"
};

object[] parametros = new object[] { @Fechainicio,@FechaFinal, @empleadoid};

List<ClaseGenerica> resultado = db.Database.SqlQuery<ClaseGenerica>("exec sp_DetalleDeduccionesEmpleado @Fechainicio,@FechaFinal, @empleadoid", parametros).ToList();'

ViewBag.deduc = resultado;

You show the result in your view:

foreach(var Deduccionees in Viewbag.deduc){
 <th>@Deduccionees.nombredePropiedad</th>
}

Sorry for the comment format, I'm new here. Greetings