Esempio n. 1
0
        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>());
            }
        }
Esempio n. 2
0
        public static bool Delete(Emp empleat, out EmpDB_Update_Error_Codes errorCode)
        {
            try
            {
                using (EmpresaDBContext context = new EmpresaDBContext())
                {
                    using (var connexio = context.Database.GetDbConnection())
                    {
                        connexio.Open();

                        using (var consulta = connexio.CreateCommand())
                        {
                            DbTransaction transaction = null;
                            try
                            {
                                // Creem transacció
                                transaction          = connexio.BeginTransaction();
                                consulta.Transaction = transaction; // Ara si que la consulta usa la transacció

                                //
                                consulta.CommandText =
                                    $@"select count(1) from emp where cap=@empNo";
                                DBUtils.createParameter(consulta, "empNo", empleat.EmpNo, DbType.Int32);
                                object o = consulta.ExecuteScalar();
                                int    numSubordinats = (int)((long)o);
                                if (numSubordinats > 0)
                                {
                                    // El cognom ja existeix, i al avisar a l'usuari.

                                    errorCode = EmpDB_Update_Error_Codes.ERR_EMPLEAT_AMB_SUBORDINATS;
                                    return(false);
                                }
                                else
                                {
                                    //string cognom, int salari, int deptNo
                                    consulta.CommandText =
                                        $@"delete from emp where emp_no=@empNo";

                                    int filesModificades = consulta.ExecuteNonQuery();
                                    if (filesModificades == 1)
                                    {
                                        transaction.Commit();
                                        errorCode = EmpDB_Update_Error_Codes.ERR_NO_ERROR;
                                        return(true);
                                    }
                                    else
                                    {
                                        // OMG!
                                        // rollback !!!!!!!!
                                        transaction.Rollback();

                                        ILogger log = LogManagerFactory.DefaultLogManager.GetLogger <EmpDB>();

                                        log.Fatal("error durant la inserció de l'empleat , filesModificades=" + filesModificades);

                                        //-----------------------------------------------------
                                        // El log es troba a la carpeta següent
                                        // (el número llarg en hexadecimal és el Package name
                                        // que està a l'arxiu "Package.appmanifest"
                                        // en aquest cas és 727b014c-873f-493e-b051-4dd21cf18dae_n82rqfc3nm07y
                                        //C:\Users\Usuari\AppData\Local\Packages\727b014c-873f-493e-b051-4dd21cf18dae_n82rqfc3nm07y\LocalState\MetroLogs
                                        //-----------------------------------------------------
                                        errorCode = EmpDB_Update_Error_Codes.ERR_INESPERAT;
                                        return(false);
                                    }
                                }
                            }
                            catch (Exception e)
                            {
                                if (transaction != null)
                                {
                                    transaction.Rollback();
                                }
                                errorCode = EmpDB_Update_Error_Codes.ERR_INESPERAT;
                                return(false);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                errorCode = EmpDB_Update_Error_Codes.ERR_INESPERAT;
                ILogger log = LogManagerFactory.DefaultLogManager.GetLogger <EmpDB>();
                log.Error("Error inesperat a l'actualització de dades", ex);
                return(false);
            }
        }