private List <AppRole> GetAllRolesForCache()
{
OracleConnection con = new OracleConnection(_connectionString);
OracleCommand com = con.CreateCommand(SchemaOwner + PackageName + "get_all_roles");
com.Parameters.Add("i_app_name", OracleDbType.Varchar2, ApplicationName, ParameterDirection.Input);
com.Parameters.Add("o_data", OracleDbType.RefCursor, ParameterDirection.Output);
OracleDataReader dr = null;
List <AppRole> items = new List <AppRole>();
try
{
con.Open();
dr = com.ExecuteReaderEx();
while (dr.Read())
{
AppRole item = new AppRole();
item.RoleId = dr.GetValue <int>("role_id");
item.RoleName = dr.GetValue <string>("role_name");
items.Add(item);
}
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(Dal.ExtractOracleMessage(oex.Message), oex);
}
else
{
throw;
}
}
finally
{
Dal.Cleanup(con, com, dr);
con = null;
com = null;
dr = null;
}
return(items);
}
public List <AppRole> GetRoles(string username)
{
OracleConnection con = new OracleConnection(_connectionString);
OracleCommand com = con.CreateCommand(SchemaOwner + PackageName + "get_roles_for_user");
//com.Parameters.Add("i_app_name", OracleDbType.Varchar2, ApplicationName, ParameterDirection.Input);
com.Parameters.Add("i_bsc_employee_number", OracleDbType.Varchar2, username.Trim(), ParameterDirection.Input);
//com.Parameters.Add("i_employee_number", OracleDbType.Varchar2, username.Trim().ToUpper(), ParameterDirection.Input);
com.Parameters.Add("i_employee_number", OracleDbType.Varchar2, null, ParameterDirection.Input);
com.Parameters.Add("o_data", OracleDbType.RefCursor, ParameterDirection.Output);
OracleDataReader dr = null;
List <AppRole> items;
items = new List <AppRole>();
try
{
con.Open();
dr = com.ExecuteReaderEx();
while (dr.Read())
{
int roleId = dr.GetValue <int>("role_id");
AppRole item = items.Find(r => r.RoleId == roleId);
if (item == null)
{
item = new AppRole();
item.RoleId = roleId;
item.RoleName = dr.GetValue <string>("role_name");
items.Add(item);
}
}
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(Dal.ExtractOracleMessage(oex.Message), oex);
}
else
{
throw;
}
}
finally
{
Dal.Cleanup(con, com, dr);
con = null;
com = null;
dr = null;
}
return(items);
}