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>());
}
}
