public static ObservableCollection <Dept> getLlistaDepartaments() { try { using (EmpresaDBContext context = new EmpresaDBContext()) { using (var connexio = context.Database.GetDbConnection()) { connexio.Open(); using (var consulta = connexio.CreateCommand()) { consulta.CommandText = $@" select * from dept "; var reader = consulta.ExecuteReader(); ObservableCollection <Dept> departaments = new ObservableCollection <Dept>(); while (reader.Read()) { Dept d = new Dept(); DBUtils.Llegeix(reader, out d.deptNo, "DEPT_NO"); DBUtils.Llegeix(reader, out d.dNom, "DNOM"); DBUtils.Llegeix(reader, out d.loc, "LOC", ""); departaments.Add(d); } return(departaments); } } } } catch (Exception ex) { ILogger log = LogManagerFactory.DefaultLogManager.GetLogger <EmpDB>(); log.Fatal("error durant la select dels departaments"); return(new ObservableCollection <Dept>()); } }
public static ObservableCollection <Emp> getLlistaEmpleats( string cognom, DateTime dataLimit, int numDept) { try { using (EmpresaDBContext context = new EmpresaDBContext()) { using (var connexio = context.Database.GetDbConnection()) { connexio.Open(); using (var consulta = connexio.CreateCommand()) { /* * consulta.CommandText = * $@" select e.* , cap.cognom as cognom_cap * from emp e left join emp cap on e.cap = cap.emp_no * where e.cognom like '%{cognom}%' and * e.data_alta >= '{dataLimit.ToString("yyyy-MM-dd")}' * and ( {numDept}=-1 OR e.dept_no={numDept} ) * "; * */ consulta.CommandText = $@" select e.* , cap.cognom as cognom_cap from emp e left join emp cap on e.cap = cap.emp_no where e.cognom like @cognom and e.data_alta >= @dataLimit and ( @numDept=-1 OR e.dept_no=@numDept ) "; DBUtils.createParameter(consulta, "cognom", $"%{cognom}%", DbType.String); DBUtils.createParameter(consulta, "dataLimit", dataLimit, DbType.DateTime); DBUtils.createParameter(consulta, "numDept", numDept, DbType.Int32); /*DbParameter paramCognom = consulta.CreateParameter(); * paramCognom.ParameterName = "cognom"; * paramCognom.Value = $"%{cognom}%"; * paramCognom.DbType = System.Data.DbType.String; * consulta.Parameters.Add(paramCognom);*/ var reader = consulta.ExecuteReader(); ObservableCollection <Emp> empleats = new ObservableCollection <Emp>(); while (reader.Read()) { Emp e = new Emp(); DBUtils.Llegeix(reader, out e.empNo, "EMP_NO"); DBUtils.Llegeix(reader, out e.cognom, "COGNOM"); DBUtils.Llegeix(reader, out e.ofici, "OFICI", ""); DBUtils.Llegeix(reader, out e.cap, "CAP", SENSE_DEPT); DBUtils.Llegeix(reader, out e.nomCap, "cognom_cap", ""); DBUtils.Llegeix(reader, out e.data_alta, "DATA_ALTA", DateTime.Today); DBUtils.Llegeix(reader, out e.salari, "SALARI", 0); DBUtils.Llegeix(reader, out e.comissio, "COMISSIO", 0); DBUtils.Llegeix(reader, out e.deptNo, "DEPT_NO"); /*e.EmpNo = reader.GetInt32(reader.GetOrdinal("EMP_NO")); * e.Cognom = reader.GetString(reader.GetOrdinal("COGNOM")); * e.Ofici = reader.IsDBNull(reader.GetOrdinal("OFICI")) ? "" : reader.GetString(reader.GetOrdinal("OFICI")); * e.Cap = reader.GetInt32(reader.GetOrdinal("CAP")); * e.Data_alta = reader.GetDateTime(reader.GetOrdinal("DATA_ALTA")); * e.Salari = reader.GetDecimal(reader.GetOrdinal("SALARI")); * e.Comissio = reader.GetDecimal(reader.GetOrdinal("COMISSIO")); * e.DeptNo = reader.GetInt32(reader.GetOrdinal("DEPT_NO"));*/ empleats.Add(e); } return(empleats); } } } } catch (Exception ex) { ILogger log = LogManagerFactory.DefaultLogManager.GetLogger <EmpDB>(); log.Fatal("error durant la select dels empleats"); return(new ObservableCollection <Emp>()); } }