sending parameters from asp.net mvc to sql server

Navigation
0

I need to send these parameters to Sql Server to be used in my stored procedure:

(int[] DedudccionesId, DateTime FechaDesde, DateTime FechaHasta) 

 //POST: Detalle Planilla Empleado
    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult DetallePlanillaEmpleado(Tbl_DetalleDeduccionesEmpleado tbl_DetalleDeduccionesEmpleado,Tbl_Empleado tbl_Empleado, Tbl_Deducciones tbl_Deducciones, Tbl_Puesto tbl_Puesto,Tbl_Planilla tbl_Planilla, Tbl_Agencia tbl_Agencia, Tbl_Area tbl_Area, int[] DedudccionesId, int[] otrosBeneficios, DateTime FechaDesde, DateTime FechaHasta)
    {

        IList<PlanillaViewModel> planillaViewModelLista = new List<PlanillaViewModel>();
        PlanillaViewModel planilaViewModel = new PlanillaViewModel();
        IEnumerable<Tbl_HistorialLaboral> EmpleadosPlanilla = new List<Tbl_HistorialLaboral>();
        IEnumerable<Tbl_Deducciones> DeduccionConValor = new List<Tbl_Deducciones>();
        // Deducciones
        if (DedudccionesId != null)
        {
            IList<string> Deducciones = new List<string>();


            for (int i = 0; i < DedudccionesId.Length; i++)
            {
                string cadena = "";
                cadena += DedudccionesId.ToString() + ",";

                int idDeduccion = DedudccionesId[i];
                var Deduccion = db.Tbl_Deducciones.FirstOrDefault(t => t.DedId == idDeduccion);
                Deducciones.Add(Deduccion.DedDescripcion);
                if (Deduccion.DedTipo == "No Frecuente")
                {

                    var DeduccionesEmpleados = db.Con_DetalleDeduccionesEmpleado.Where(t =>  t.AgeId == tbl_Agencia.AgeId && t.AreId == tbl_Area.AreId && DbFunctions.TruncateTime(t.DetDedEmpFecha) >= FechaDesde && DbFunctions.TruncateTime(t.DetDedEmpFecha) <= FechaHasta && t.HisLabEstadoEmpleado == true);

                    ViewBag.DeduccionesEmpleados = DeduccionesEmpleados.ToList();  // muestra las deducciones asignadas a los empleados

                                            string[] arreglo = Deduccion.DedDescripcion.Split();
                    //string arreglo = DeduccionesEmpleados.Contains();
                }
                if (Deduccion.DedTipo == "Frecuente")
                {
                    // var DeducionesFijasEmpleado = db.Con_DeduccionesFijasEmpleado.Where(t => t.DedId == tbl_Deducciones.DedId && t.AgeId == tbl_Agencia.AgeId && t.AreId == tbl_Area.AreId && t.HisLabEstadoEmpleado == true);
                    var DeducionesFijasEmpleado = db.Con_DeduccionesFijasEmpleado.Where(t => t.AgeId == tbl_Agencia.AgeId && t.AreId == tbl_Area.AreId && t.HisLabEstadoEmpleado == true);

                    ViewBag.DeducionesFijasEmpleado = DeducionesFijasEmpleado.ToList();   // muestra las deducciones No frecuentes asignadas a los empleados
                }

            }


            ViewBag.Deducciones = Deducciones.ToList();
        }

This is my stored procedure already created in sql 

drop procedure sp_DetalleDeducciones
go

create procedure sp_DetalleDeducciones @inicio datetime, @final datetime, @areID int, @ageID int, @Cadena varchar(100)
as
    select * into #cad from dbo.DescomponesCadena(@cadena)
    select * into #his from Tbl_HistorialLaboral where AgeId = @ageID and AreId = @areID
    select * into #det from Tbl_DetalleDeduccionesEmpleado where DetDedEmpFecha between @inicio and @final
    select * into #emp from Tbl_Empleado where EmpId in (select EmpId from #det ) and EmpId in (select empID from #his)
    select * into #ded from Tbl_Deducciones where DedId in (select ID from #cad)


    select e.EmpId, e.EmpNombre, e.EmpApellido, d.DedId, d.DedDescripcion into #empl
     from #ded d
     cross join #emp e

     select distinct e.*, ISNULL(d.DetDedEmpValor,0) DetDedEmpValor
      into #out
     from #empl e 
     left join #det d on e.empID = d.EmpId and d.DedId = e.DedId 
     order by e.EmpNombre

     select EmpId, sum(DetDedEmpValor) Total into #Total from #out group by EmpId

     select o.*, t.Total 
      from #out o 
      inner join #total t on t.EmpId =o.EmpId


go

execute sp_DetalleDeducciones '2018-05-01', '2018-05-08', 5, 1, '8,2,5,'
    
asked by Marco Eufragio 23.10.2018 в 05:38
source

1 answer

0

To invoke a procedure using ado.net you must follow the following structure 

using(SqlConnection cn = new SqlConnection("connection string"))
{
    cn.Open();

    SqlCommand cmd= new SqlCommand("sp_DetalleDeducciones", cn);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@inicio", Convert.ToDateTime(Textbox1.Text));
    cmd.Parameters.AddWithValue("@final", Convert.ToDateTime(Textbox2.Text));
    cmd.Parameters.AddWithValue("@areID", Convert.ToInt32(Textbox3.Text));
    //resto parametros

    SqlDataReader dr = cmd.ExecuteReader();

    while(dr.Read())
    {
       //aqui cargas la lista
    }
}

Of course this is just an example, you must adapt it to your code, but the idea of how it should be

Note that the procedure executes multiple SELECT , but they all use INTO , which assigns the result to a temporary table, at least one of the select should return a response to have something in reader

    
answered by 27.10.2018 / 12:34
source
Problems when calling a Fragment from an Activity getJavaScriptResult returns null