public List <Employee> FindAppUsers(string username,
                                            string firstName,
                                            string lastName,
                                            string jobDescription,
                                            string roleName,
                                            bool?active)
        {
            OracleConnection con = new OracleConnection(_connectionString);
            OracleCommand    com = con.CreateCommand(SchemaOwner + PackageName + "find_employees");

            com.Parameters.Add("i_app_name", OracleDbType.Varchar2, ApplicationName, ParameterDirection.Input);
            com.Parameters.Add("i_employee_number", OracleDbType.Varchar2, string.IsNullOrEmpty(username) ? null : username.Trim().ToUpper(), ParameterDirection.Input);
            com.Parameters.Add("i_first_name", OracleDbType.Varchar2, string.IsNullOrEmpty(firstName) ? null : firstName.Trim().ToUpper(), ParameterDirection.Input);
            com.Parameters.Add("i_last_name", OracleDbType.Varchar2, string.IsNullOrEmpty(lastName) ? null : lastName.Trim().ToUpper(), ParameterDirection.Input);
            com.Parameters.Add("i_job_descr", OracleDbType.Varchar2, string.IsNullOrEmpty(jobDescription) ? null : jobDescription.Trim().ToUpper(), ParameterDirection.Input);
            com.Parameters.Add("i_role_name", OracleDbType.Varchar2, string.IsNullOrEmpty(roleName) ? null : roleName, ParameterDirection.Input);

            if (active != null)
            {
                com.Parameters.Add("i_employee_status", OracleDbType.Int32, active.Value ? 1 : 0, ParameterDirection.Input);
            }

            com.Parameters.Add("o_data", OracleDbType.RefCursor, ParameterDirection.Output);

            OracleDataReader dr    = null;
            List <Employee>  items = new List <Employee>();

            try
            {
                con.Open();

                dr = com.ExecuteReader();

                while (dr.Read())
                {
                    Employee employee = new Employee();

                    PopulateEmployee(employee, dr);

                    items.Add(employee);
                }

                con.Close();
            }
            finally
            {
                BaseDal.Cleanup(con, com, dr);

                con = null;
                com = null;
                dr  = null;
            }

            return(items);
        }
        public void UpdateAppUser(string username,
                                  string bscEmployeeNumber,
                                  string lastName,
                                  string firstName,
                                  string middleName,
                                  string suffix,
                                  bool active,
                                  DateTime statusValidity,
                                  IEnumerable <string> roleName,
                                  string auditUser)
        {
            OracleConnection con = new OracleConnection(_connectionString);
            OracleCommand    com = con.CreateCommand(SchemaOwner + PackageName + "update_user");

            com.Parameters.Add("i_app_name", OracleDbType.Varchar2, ApplicationName, ParameterDirection.Input);
            com.Parameters.Add("i_employee_number", OracleDbType.Varchar2, username.Trim().ToUpper(), ParameterDirection.Input);
            com.Parameters.Add("i_bsc_employee_number", OracleDbType.Varchar2, bscEmployeeNumber.Trim().ToUpper(), ParameterDirection.Input);
            com.Parameters.Add("i_last_name", OracleDbType.Varchar2, lastName.Trim().ToUpper(), ParameterDirection.Input);
            com.Parameters.Add("i_first_name", OracleDbType.Varchar2, firstName.Trim().ToUpper(), ParameterDirection.Input);
            com.Parameters.Add("i_middle_name", OracleDbType.Varchar2, middleName.Trim().ToUpper(), ParameterDirection.Input);
            com.Parameters.Add("i_suffix", OracleDbType.Varchar2, suffix.Trim().ToUpper(), ParameterDirection.Input);
            com.Parameters.Add("i_employee_status", OracleDbType.Int32, (active ? 1 : 0), ParameterDirection.Input);
            com.Parameters.Add("i_status_eff_end_date", OracleDbType.Date, statusValidity, ParameterDirection.Input);
            com.Parameters.Add("i_role_list", OracleDbType.Varchar2, string.Join(",", roleName), ParameterDirection.Input);
            com.Parameters.Add("i_audit_user", OracleDbType.Varchar2, auditUser, ParameterDirection.Input);

            try
            {
                con.Open();

                com.ExecuteNonQuery();

                con.Close();
            }
            catch (OracleException oex)
            {
                // check if its a custom oracle exception
                if (oex.Number >= 20000 && oex.Number <= 20999)
                {
                    // throw a custom exception
                    throw new DalException(BaseDal.ExtractOracleMessage(oex.Message), oex);
                }
                else
                {
                    throw;
                }
            }
            finally
            {
                BaseDal.Cleanup(con, com);

                con = null;
                com = null;
            }
        }
        public Employee GetAppUser(string username, string bscEmployeeNumber)
        {
            OracleConnection con = new OracleConnection(_connectionString);
            OracleCommand    com = con.CreateCommand(SchemaOwner + PackageName + "get_employee_details");

            //com.Parameters.Add("i_app_name", OracleDbType.Varchar2, ApplicationName, ParameterDirection.Input);
            com.Parameters.Add("i_employee_number", OracleDbType.Varchar2, string.IsNullOrEmpty(username) ? null : username.Trim().ToUpper(), ParameterDirection.Input);
            com.Parameters.Add("i_bsc_employee_number", OracleDbType.Varchar2, string.IsNullOrEmpty(bscEmployeeNumber) ? null : bscEmployeeNumber.Trim().ToUpper(), ParameterDirection.Input);
            com.Parameters.Add("o_data", OracleDbType.RefCursor, ParameterDirection.Output);

            OracleDataReader dr       = null;
            Employee         employee = null;

            try
            {
                con.Open();

                dr = com.ExecuteReader();

                if (dr.Read())
                {
                    employee = new Employee();

                    PopulateEmployee(employee, dr);
                }

                con.Close();
            }
            catch (Exception e)
            {
                Debug.WriteLine(e.Message);
            }
            finally
            {
                BaseDal.Cleanup(con, com, dr);

                con = null;
                com = null;
                dr  = null;
            }

            return(employee);
        }