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