public void AddRole(RoleDO form)
{
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
try
{
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("ROLE_ADD", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
storedProcedure.Parameters.AddWithValue("@Name", form.Name);
connectionToSql.Open();
storedProcedure.ExecuteNonQuery();
}
catch (Exception ex)
{
Logger.Log("Fatal", ex.TargetSite.ToString(), ex.Message, ex.StackTrace);
throw ex;
}
finally
{
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
}
public List <RoleDO> GetRoleList()
{
List <RoleDO> Roles = new List <RoleDO>();
try
{
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
using (SqlCommand PullAllRoles = new SqlCommand("ROLES_VIEW_ALL", sqlConnection))
{
PullAllRoles.CommandType = System.Data.CommandType.StoredProcedure;
sqlConnection.Open();
using (SqlDataReader reader = PullAllRoles.ExecuteReader())
{
while (reader.Read())
{
RoleDO temp = new RoleDO();
temp = Mapper.MapSingleRole(reader);
Roles.Add(temp);
}
}
}
}
catch (SqlException sqlEx)
{
Logger.LogSqlException(sqlEx);
}
return(Roles);
}
public RoleDO ViewRoleById(int Id)
{
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
SqlDataReader reader = null;
RoleDO roleDO = new RoleDO();
try
{
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("ROLE_VIEW_BY_ID", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
storedProcedure.Parameters.AddWithValue("@RoleID", Id);
connectionToSql.Open();
reader = storedProcedure.ExecuteReader();
while (reader.Read())
{
roleDO.RoleId = (int)reader["RoleID"];
roleDO.Name = reader["Name"] as string;
}
}
catch (Exception ex)
{
Logger.Log("Fatal", ex.TargetSite.ToString(), ex.Message, ex.StackTrace);
throw ex;
}
return(roleDO);
}
public ActionResult AddRole(RolePO form)
{
ActionResult response = null;
if (ModelState.IsValid)
{
try
{
//sets user inputted role name to a variable and checks to see if the name exists in the database
string name = form.Name;
bool roleExists = _dataAccess.RoleExists(name);
if (!roleExists)
{
RoleDO dataObject = RoleMapper.RolePOToDO(form);
_dataAccess.AddRole(dataObject);
response = RedirectToAction("Index", "Role");
}
else
{
ModelState.AddModelError("Name", "Role name already in use, please choose another.");
response = View();
}
}
catch (Exception ex)
{
Logger.Log("Fatal", ex.TargetSite.ToString(), ex.Message, ex.StackTrace);
}
}
else
{
response = View(form);
}
return(response);
}
public List <RoleDO> ReadRole()
{
List <RoleDO> roleList = new List <RoleDO>();
try
{
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
SqlCommand sqlCommand = new SqlCommand("READ_ROLE", sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlConnection.Open();
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
RoleDO roleObject = new RoleDO();
roleObject.RoleID = sqlDataReader.GetInt32(0);
roleObject.Name = sqlDataReader.GetString(1);
roleObject.Description = sqlDataReader.GetString(2);
roleList.Add(roleObject);
}
sqlDataReader.Close();
sqlDataReader.Dispose();
sqlCommand.Dispose();
}
}
catch (Exception exc)
{
LoggingDataAccessLayer.LogReadRole(exc);
}
return(roleList);
}
public RolePO MapDOtoPO(RoleDO roleDO)
{
RolePO rolePO = new RolePO();
rolePO.RoleID = roleDO.RoleID;
rolePO.RoleName = roleDO.RoleName;
return(rolePO);
}
public static RolePO RoleDOToPO(RoleDO from)
{
RolePO to = new RolePO();
to.RoleId = from.RoleId;
to.Name = from.Name;
return(to);
}
public static RolePO MapRoleDOtoPO(RoleDO frmRoleDO)
{
RolePO toRolePO = new RolePO();
toRolePO.RoleID = frmRoleDO.RoleID;
toRolePO.Name = frmRoleDO.Name;
toRolePO.Description = frmRoleDO.Description;
return(toRolePO);
}
//mapper to map a single roles data from the server to a RoleDO object
public static RoleDO MapSingleRole(SqlDataReader from)
{
RoleDO to = new RoleDO();
to.RoleID = (int)from["RoleID"];
to.Name = from["RoleName"] as string;
return(to);
}
public RoleBO GetRoleByID(int _roleID)
{
// Instaniate a new Data Layer object using a Data Layer method
RoleDO retval = dataContext.GetRoleDOByID(_roleID);
if (retval == null)
{
return(null);
}
return(new RoleBO(retval));
}
public string FetchRole(RoleDO _RoleDO)
{
string role = "";
try
{
role = (from c in context.tbl_ROLE where c.ROLE_ID == _RoleDO.ROLE_ID select c.ROLE_NAME).FirstOrDefault();
}
catch (Exception e)
{
role = "";
}
return(role);
}
/// <summary>
/// Displays a list of all users in the database.
/// </summary>
/// <returns>Returns a result based on status</returns>
public ActionResult Index()
{
ActionResult response;
//check for admin
if (Session["RoleID"] != null && (int)Session["RoleID"] == 6)
{
//if the user is an admin, access the database
try
{
//access database, instantiate list
List <UserDO> allUsers = _UserDataAccess.ViewUsers();
List <UserPO> mappedUsers = new List <UserPO>();
//map to presentation layer
foreach (UserDO user in allUsers)
{
RoleDO role = _RoleDataAccess.ViewRoleByID(user.RoleID);
UserPO mappedUser = _Mapper.MapDOtoPO(user);
mappedUser.RoleName = role.RoleName;
mappedUsers.Add(mappedUser);
}
response = View(mappedUsers);
}
catch (Exception ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
response = RedirectToAction("Error", "Home");
}
finally { }
}
//check for user or mod
else if (Session["RoleID"] != null && (int)Session["RoleID"] == 1 || Session["RoleID"] != null && (int)Session["RoleID"] == 4)
{
//if user or mod, redirect to home page
response = RedirectToAction("Index", "Home");
}
//if not logged in
else
{
//if not logged in, redirect to login page
response = RedirectToAction("Login", "Account");
}
return(response);
}
/// <summary>
/// Filters data while reading from the database
/// </summary>
/// <param name="reader">SqlDataReader to read from the database</param>
/// <returns>Returns a RoleDO with no null values</returns>
public RoleDO MapReaderToSingle(SqlDataReader reader)
{
RoleDO roleDO = new RoleDO();
if (reader["RoleID"] != DBNull.Value)
{
roleDO.RoleID = (int)reader["RoleID"];
}
if (reader["RoleName"] != DBNull.Value)
{
roleDO.RoleName = (string)reader["RoleName"];
}
return(roleDO);
}
/// <summary>
/// Pull the information for one record in the Role table in the GAMEGROOVE database. Runs the VIEW_ROLE_BY_ID stored procedure.
/// </summary>
/// <param name="roleID">ID of the role needing to be retrieved</param>
/// <returns>Returns a RoleDO filled with information retrieved from the database</returns>
public RoleDO ViewRoleByID(int roleID)
{
RoleDO role = new RoleDO();
//catch errors while accessing the database
try
{
//connect to sql server database, run VIEW_ROLE_BY_ID
using (SqlConnection connection = new SqlConnection(_ConnectionString))
using (SqlCommand command = new SqlCommand("VIEW_ROLE_BY_ID", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 60;
//set parameter for stored procedure
command.Parameters.AddWithValue("@RoleID", roleID);
connection.Open();
//use SqlDataReader to pull one record from the database, [if] statement only selects one record from database
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
role = _RoleMapper.MapReaderToSingle(reader);
}
}
}
}
//catch SQL Exceptions for accurate error logging
catch (SqlException ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
throw ex;
}
//catch general exceptions other than errors thrown while accessing SQL
catch (Exception ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
throw ex;
}
finally { }
return(role);
}
public ActionResult UpdateRole(int Id)
{
ActionResult response = null;
try
{
//views the update page for a specific role, using the role ID
RoleDO dataObject = _dataAccess.ViewRoleById(Id);
RolePO displayObject = RoleMapper.RoleDOToPO(dataObject);
response = View(displayObject);
}
catch (Exception ex)
{
Logger.Log("Fatal", ex.TargetSite.ToString(), ex.Message, ex.StackTrace);
}
return(response);
}
public List <RoleDO> ViewAllRoles()
{
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
SqlDataReader reader = null;
List <RoleDO> roleList = new List <RoleDO>();
try
{
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("ROLE_VIEW_ALL", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
connectionToSql.Open();
reader = storedProcedure.ExecuteReader();
while (reader.Read())
{
RoleDO roleDO = new RoleDO();
roleDO.RoleId = (int)reader["RoleID"];
roleDO.Name = reader["Name"] as string;
roleList.Add(roleDO);
}
}
catch (SqlException ex)
{
Logger.Log("Fatal", ex.TargetSite.ToString(), ex.Message, ex.StackTrace);
throw ex;
}
catch (Exception baseEx)
{
Logger.Log("Fatal", baseEx.TargetSite.ToString(), baseEx.Message, baseEx.StackTrace);
throw baseEx;
}
finally
{
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
return(roleList);
}
public static RoleDO ToDataObject(this Role role)
{
if (role == null)
{
throw new ArgumentNullException(nameof(role));
}
var entity = new RoleDO
{
Id = role.Id,
TenantId = role.TenantId,
Name = role.Name,
Description = role.Description,
CreatedBy = role.CreatedBy,
CreatedTime = role.CreatedTime,
UpdatedBy = role.UpdatedBy,
UpdatedTime = role.UpdatedTime
};
return(entity);
}
public string FetchRole(RoleDO _RoleDO)
{
string role = "";
RoleDH _RoleDH;
try
{
_RoleDH = new RoleDH();
role = _RoleDH.FetchRole(_RoleDO);
}
catch (Exception e)
{
throw;
}
finally
{
_RoleDH = null;
}
return(role);
}
public List <RoleDO> GetAllRoles()
{
List <RoleDO> listOfRoles = new List <RoleDO>();
SqlCommand getComm = null;
try
{
using (SqlConnection conn = new SqlConnection(ConnectionParms))
{
using (getComm = new SqlCommand("sp_GetRoles"))
{
getComm.CommandType = CommandType.StoredProcedure;
getComm.CommandTimeout = 35;
getComm.Connection = conn;
conn.ConnectionString = ConnectionParms;
conn.Open();
using (SqlDataReader reader = getComm.ExecuteReader())
{
while (reader.Read())
{
RoleDO role = new RoleDO();
role.RoleID = reader.GetInt32(reader.GetOrdinal("RoleID"));
role.RoleNameShort = (string)reader["RoleNameShort"];
role.RoleNameLong = (string)reader["RoleNameLong"];
role.Comment = (string)reader["Comment"];
listOfRoles.Add(role);
}
}
}
}
}
catch (Exception ex)
{
ErrorLogger.LogError(ex, "GetAllUsers", "nothing");
}
return(listOfRoles);
}
public ActionResult UpdateRole(RolePO form)
{
ActionResult response = null;
if (ModelState.IsValid)
{
try
{
//sets the user inputted role name to a variable to be used in the code
string name = form.Name;
//sends the user inputted variable to RoleExists methods to see if it already exists
bool roleExists = _dataAccess.RoleExists(name);
if (!roleExists)
{
//if the role name does not exist, continue with the update procedure
RoleDO dataObject = RoleMapper.RolePOToDO(form);
_dataAccess.UpdateRole(dataObject);
response = RedirectToAction("Index", "Role");
}
else
{
//if the role name does exist, show error message
ModelState.AddModelError("Name", "Role name already in use, please choose another.");
response = View();
}
}
catch (Exception ex)
{
Logger.Log("Fatal", ex.TargetSite.ToString(), ex.Message, ex.StackTrace);
}
}
else
{
response = View(form);
}
return(response);
}
/// <summary>
/// Displays the information for one user.
/// </summary>
/// <param name="id">ID of user needing to be displayed</param>
/// <returns>Returns a result based on status</returns>
public ActionResult UserDetails(int id)
{
ActionResult response;
ReviewVM viewModel = new ReviewVM();
//check for permissions
if (Session["UserID"] != null && (int)Session["UserID"] == id || Session["RoleID"] != null && (int)Session["RoleID"] == 6)
{
//if allowed, access the database
try
{
//access database, map to presentation, add to view model
//get user information
UserDO user = _UserDataAccess.ViewUserByID(id);
viewModel.User = _Mapper.MapDOtoPO(user);
//get role information for role name
RoleDO role = _RoleDataAccess.ViewRoleByID(user.RoleID);
viewModel.User.RoleName = role.RoleName;
//get user's most frequent category
viewModel.UserFavCategory = _reviewMapper.MapDOtoPO(_ReviewDataAccess.UserFavoriteCategory(viewModel.User.UserID));
//check id
if (viewModel.User.UserID > 0)
{
//if id is valid, access database
List <ReviewDO> gameReviews = _ReviewDataAccess.ViewReviews();
//instantiate new lists
List <ReviewPO> mappedReviews = new List <ReviewPO>();
viewModel.ReviewsList = new List <ReviewPO>();
//map to presenation, add to view model
foreach (ReviewDO reviewDO in gameReviews)
{
//map review information
ReviewPO reviewPO = _reviewMapper.MapDOtoPO(reviewDO);
//get game information for title
GameDO gameDO = _GameDataAccess.ViewGameByID(reviewPO.GameID);
reviewPO.GameTitle = gameDO.Title;
//get user information for username
UserDO userDO = _UserDataAccess.ViewUserByID(reviewPO.UserID);
reviewPO.Username = userDO.Username;
mappedReviews.Add(reviewPO);
}
//filter list by user, add to view model
viewModel.ReviewsList = mappedReviews.Where(n => n.UserID == viewModel.User.UserID).ToList();
response = View(viewModel);
}
else
{
//if model state is not valid, display error screen
response = RedirectToAction("Error", "Home");
}
}
catch (Exception ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
//display error page
response = RedirectToAction("Error", "Home");
}
finally { }
}
else
{
//if not logged in, redirect to login page
response = RedirectToAction("Login", "Account");
}
return(response);
}
public int CreateRole(int tenantId, int operatorId, RoleDO role)
{
return(ExecuteScalar("INSERT INTO public.role (tenantid, name, description, createdby, createdtime, updatedby, updatedtime) VALUES(@tenantid,@name, @description, @createdby, @createdtime, @updatedby, @updatedtime) RETURNING id", role));
}
public ActionResult destroyRole([DataSourceRequest] DataSourceRequest request, RoleDO model)
{
using (var Db = new ProjectWebEntities())
{
var r = Db.Tbl_Role.SingleOrDefault(w => w.Id == model.Id);
var p = Db.Tbl_Role.SingleOrDefault(w => w.Id == r.ParentId);
try
{
Db.Tbl_Role.Remove(r);
if (!Db.Tbl_Role.Any(t => t.ParentId == p.Id && t.Id != r.Id))
{
Db.Tbl_Role.Remove(p);
}
Db.SaveChanges();
}
catch (Exception ex)
{
logger.Error("An Error Happened! ", ex);
}
return(Json(new[] { model }.ToDataSourceResult(request, ModelState)));
}
}
/// <summary>
/// Updates the role.
/// </summary>
/// <returns><c>true</c>, if role was updated, <c>false</c> otherwise.</returns>
/// <param name="tenantId">Tenant identifier.</param>
/// <param name="operatorId">Operator identifier.</param>
/// <param name="role">Org.</param>
public bool UpdateRole(int tenantId, int operatorId, RoleDO role)
{
Execute("UPDATE public.role SET name = @name, updatedby=@updatedby, updatedtime=@updatedtime WHERE tenantid=@tenantid and id = @Id", role);
return(true);
}
// Constructor to map a DataAccessLayer object to a BusinessLogicLayer object
public RoleBO(RoleDO _roleDO)
{
RoleID = _roleDO.RoleID;
Role = _roleDO.Role;
}
