/// <summary>
/// Filters data while reading from the database
/// </summary>
/// <param name="reader">SqlDataReader to read from the database</param>
/// <returns>Returns a ReviewDO with no null values</returns>
public ReviewDO MapReaderToSingle(SqlDataReader reader)
{
ReviewDO reviewDO = new ReviewDO();
if (reader["ReviewID"] != DBNull.Value)
{
reviewDO.ReviewID = (int)reader["ReviewID"];
}
if (reader["ReviewText"] != DBNull.Value)
{
reviewDO.ReviewText = (string)reader["ReviewText"];
}
if (reader["DatePosted"] != DBNull.Value)
{
reviewDO.DatePosted = (string)reader["DatePosted"];
}
if (reader["Category"] != DBNull.Value)
{
reviewDO.Category = (string)reader["Category"];
}
if (reader["UserID"] != DBNull.Value)
{
reviewDO.UserID = (int)reader["UserID"];
}
if (reader["GameID"] != DBNull.Value)
{
reviewDO.GameID = (int)reader["GameID"];
}
return(reviewDO);
}
public ReviewBO MapDOtoBO(ReviewDO reviewDO)
{
ReviewBO reviewBO = new ReviewBO();
reviewBO.ReviewID = reviewDO.ReviewID;
reviewBO.ReviewText = reviewDO.ReviewText;
reviewBO.DatePosted = reviewDO.DatePosted;
reviewBO.Category = reviewDO.Category;
reviewBO.UserID = reviewDO.UserID;
reviewBO.GameID = reviewDO.GameID;
return(reviewBO);
}
/// <summary>
/// Deletes a record from the database.
/// </summary>
/// <param name="id">ID of review needing to be deleted</param>
/// <returns>Returns a result based on status</returns>
public ActionResult DeleteReview(int id)
{
ActionResult response;
//check if logged in
if (Session["RoleID"] != null)
{
//if logged in, check id
if (id > 0)
{
//if id is valid, access database
try
{
//pull review data
ReviewDO reviewDO = _ReviewDataAccess.ViewReviewByID(id);
//check permissions
if (Session["UserID"] != null && (int)Session["UserID"] == reviewDO.UserID || (int)Session["RoleID"] == 6)
{
//if allowed, map, then run data access method
ReviewPO reviewPO = _ReviewMapper.MapDOtoPO(reviewDO);
_ReviewDataAccess.DeleteReview(reviewPO.ReviewID);
response = RedirectToAction("Index", "Review");
}
else
{
//if not allowed, redirect to login page
response = RedirectToAction("Login", "Account");
}
}
catch (Exception ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
response = RedirectToAction("Error", "Home");
}
finally { }
}
else
{
//if id is invalid, redirect to details
response = RedirectToAction("ReviewDetails", "Review");
}
}
else
{
//if not logged in, redirect to login page
response = RedirectToAction("Login", "Account");
}
return(response);
}
public ReviewPO MapDOtoPO(ReviewDO ReviewDO)
{
ReviewPO reviewPO = new ReviewPO();
reviewPO.ReviewID = ReviewDO.ReviewID;
reviewPO.ReviewText = ReviewDO.ReviewText;
reviewPO.DatePosted = ReviewDO.DatePosted;
reviewPO.Category = ReviewDO.Category;
reviewPO.UserID = ReviewDO.UserID;
reviewPO.GameID = ReviewDO.GameID;
return(reviewPO);
}
public ReviewDO MapPOtoDO(ReviewPO reviewPO)
{
ReviewDO reviewDO = new ReviewDO();
reviewDO.ReviewID = reviewPO.ReviewID;
reviewDO.ReviewText = reviewPO.ReviewText;
reviewDO.DatePosted = reviewPO.DatePosted;
reviewDO.Category = reviewPO.Category;
reviewDO.UserID = reviewPO.UserID;
reviewDO.GameID = reviewPO.GameID;
return(reviewDO);
}
public ActionResult CreateReview(ReviewPO reviewPO)
{
ActionResult response;
//check if logged in
if (Session["RoleID"] != null)
{
//if logged in, check model, then write to database
try
{
//check model
if (ModelState.IsValid)
{
//if model is valid, map from model to a DO
ReviewDO review = new ReviewDO()
{
ReviewText = reviewPO.ReviewText,
DatePosted = DateTime.Now.ToString(),
Category = reviewPO.Category,
UserID = (int)Session["UserID"],
GameID = reviewPO.GameID,
};
//access database, write to the Reviews table
_ReviewDataAccess.CreateReview(review);
response = RedirectToAction("GameDetails", "Game", new { id = review.GameID });
}
else
{
//if model is not valid, return to the form
response = View(reviewPO);
}
}
catch (Exception ex)
{
//log errors
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
response = View(reviewPO);
}
finally { }
}
else
{
//if not logged in, redirect to login page
response = RedirectToAction("Login", "Account");
}
return(response);
}
public ActionResult UpdateReview(ReviewVM viewModel)
{
ActionResult response;
//check to see if logged in
if (Session["RoleID"] != null)
{
//if logged in, check the users permissions
if (Session["UserID"] != null && (int)Session["UserID"] == viewModel.Review.UserID || (int)Session["RoleID"] == 4 || (int)Session["RoleID"] == 6)
{
//if allowed, check the model state
if (ModelState.IsValid)
{
//if model is valid, map review, then access database
try
{
//map form, send to database
ReviewDO reviewDO = _ReviewMapper.MapPOtoDO(viewModel.Review);
_ReviewDataAccess.UpdateReview(reviewDO);
//redirect to review details to show changes
response = RedirectToAction("ReviewDetails", "Review", new { id = viewModel.Review.ReviewID });
}
catch (Exception ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
response = RedirectToAction("Error", "Home");
}
finally { }
}
else
{
//if model is not valid, return to the form
response = View(viewModel);
}
}
else
{
//if not allowed, redirect to login page
response = RedirectToAction("Login", "Account");
}
}
else
{
//if not logged in, redirect to login page
response = RedirectToAction("Login", "Account");
}
return(response);
}
/// <summary>
/// Calculates the most frequent category based on user. Displays on a user's profile. Runs the CALCULATE_TOP_CATEGORY_FOR_USER stored procedure.
/// </summary>
/// <param name="userID">ID of user whose profile is being displayed</param>
/// <returns>Returns a ReviewDO filled with information retrieved from the database</returns>
public ReviewDO UserFavoriteCategory(int userID)
{
ReviewDO userFavCategory = new ReviewDO();
//catch errors while accessing the database
try
{
//connect to sql, run stored procedure
using (SqlConnection connection = new SqlConnection(_ConnectionString))
using (SqlCommand command = new SqlCommand("CALCULATE_TOP_CATEGORY_FOR_USER", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 60;
//set parameter for the stored procedure
command.Parameters.AddWithValue("@UserID", userID);
connection.Open();
//read from the database, [if] statement only reads one record
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
userFavCategory = _CalcMapper.MapCategoryToSingle(reader);
}
}
}
}
//catch SqlExceptions for accurate logging
catch (SqlException ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
throw ex;
}
//catch further exceptions
catch (Exception ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
throw ex;
}
finally { }
return(userFavCategory);
}
/// <summary>
/// Pulls the information from one record in the Reviews table in the GAMEGROOVE database. Runs the VIEW_REVIEW_BY_ID stored procedure.
/// </summary>
/// <param name="reviewID">ID of the review needing to be read</param>
/// <returns>Returns a ReviewDO filled with information retrieved from the database</returns>
public ReviewDO ViewReviewByID(int reviewID)
{
ReviewDO review = new ReviewDO();
//catch errors while accessing the database
try
{
//connect to sql, run stored procedure
using (SqlConnection connection = new SqlConnection(_ConnectionString))
using (SqlCommand command = new SqlCommand("VIEW_REVIEW_BY_ID", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 60;
//set parameter for the stored procedure
command.Parameters.AddWithValue("@ReviewID", reviewID);
connection.Open();
//read from the database, uses [if] statement to pull one record
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
review = _ReviewMapper.MapReaderToSingle(reader);
}
}
}
}
//catch SqlExceptions for accurate logging
catch (SqlException ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
throw ex;
}
//catch general exceptions
catch (Exception ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
throw ex;
}
finally { }
return(review);
}
/// <summary>
/// Checks for data while reading from the database. Maps the GameID column/property. Displays on the homepage.
/// </summary>
/// <param name="reader">SqlDataReader to read from the database</param>
/// <returns>Returns ReviewDO with the top game</returns>
public ReviewDO MapGameToSingle(SqlDataReader reader)
{
ReviewDO reviewDO = new ReviewDO();
if (reader["GameID"] != DBNull.Value)
{
reviewDO.GameID = (int)reader["GameID"];
}
reviewDO.ReviewID = 0;
reviewDO.ReviewText = null;
reviewDO.DatePosted = null;
reviewDO.UserID = 0;
reviewDO.Category = null;
return(reviewDO);
}
/// <summary>
/// Method to pull every record from the Reviews table in the GAMEGROOVE database and put them in a list. Runs the VIEW_ALL_REVIEWS stored procedure.
/// </summary>
/// <returns>Returns a list of ReviewDOs filled with data retrieved from the database.</returns>
public List <ReviewDO> ViewReviews()
{
List <ReviewDO> reviews = new List <ReviewDO>();
//catch errors while accessing the database
try
{
//connect to sql, run stored procedure
using (SqlConnection connection = new SqlConnection(_ConnectionString))
using (SqlCommand command = new SqlCommand("VIEW_ALL_REVIEWS", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 60;
connection.Open();
//SqlDataReader to get information from the Reviews table. Uses [while] statement to read multiple records in the table.
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
ReviewDO review = _ReviewMapper.MapReaderToSingle(reader);
reviews.Add(review);
}
}
}
}
//catch SqlExceptions for accurate logging
catch (SqlException ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
throw ex;
}
//catch general exceptions for further error handling
catch (Exception ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
throw ex;
}
finally { }
return(reviews);
}
/// <summary>
/// Accesses the database and calculates the top category accross all reviews in the Reviews table in the GAMEGROOVE database.
/// Runs the CALCULATE_TOP_CATEGORY stored procedure.
/// </summary>
/// <returns>Returns a ReviewDO filled with information retrieved from the database</returns>
public ReviewDO TopCategory()
{
ReviewDO topCategory = new ReviewDO();
//catch errors while accessing the database
try
{
//connect to sql, run stored procedure
using (SqlConnection connection = new SqlConnection(_ConnectionString))
using (SqlCommand command = new SqlCommand("CALCULATE_TOP_CATEGORY", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 60;
connection.Open();
//read from the database, [if] statment pulls one record
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
topCategory = _CalcMapper.MapCategoryToSingle(reader);
}
}
}
}
//catch SqlExceptions for accurate logging
catch (SqlException ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
throw ex;
}
//catch further exceptions
catch (Exception ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
throw ex;
}
finally { }
return(topCategory);
}
/// <summary>
/// Create review is a method that writes a record to the Reviews table in the GAMEGROOVE database. Uses a form filled out by the user.
/// Runs the CREATE_REVIEW stored procedure.
/// </summary>
/// <param name="review">ReviewDO filled out with information provided by a user</param>
public void CreateReview(ReviewDO review)
{
//catch errors while accessing the database
try
{
//connect to SQL, run the stored procedure
using (SqlConnection connection = new SqlConnection(_ConnectionString))
using (SqlCommand command = new SqlCommand("CREATE_REVIEW", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 60;
//set the parameters for the stored procedure
command.Parameters.AddWithValue("@ReviewText", review.ReviewText);
command.Parameters.AddWithValue("@DatePosted", review.DatePosted);
command.Parameters.AddWithValue("@Category", review.Category);
command.Parameters.AddWithValue("@UserID", review.UserID);
command.Parameters.AddWithValue("@GameID", review.GameID);
connection.Open();
command.ExecuteNonQuery();
}
}
//catch SQL exceptions for accurate logging
catch (SqlException ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
throw ex;
}
//catch general exceptions other than sql
catch (Exception ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
throw ex;
}
finally { }
}
/// <summary>
/// Display unique information for a specific review.
/// </summary>
/// <param name="id">ID of Review needing to be displayed</param>
/// <returns></returns>
public ActionResult ReviewDetails(int id)
{
ActionResult response;
ReviewVM viewModel = new ReviewVM();
//check id
if (id > 0)
{
//if id is valid, retrieve information from the database
try
{
//access database, map to view model
ReviewDO reviewDO = _ReviewDataAccess.ViewReviewByID(id);
viewModel.Review = _ReviewMapper.MapDOtoPO(reviewDO);
viewModel.User = _UserMapper.MapDOtoPO(_UserDataAccess.ViewUserByID(viewModel.Review.UserID));
viewModel.Game = _GameMapper.MapDOtoPO(_GameDataAccess.ViewGameByID(viewModel.Review.GameID));
response = View(viewModel);
}
catch (Exception ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
response = RedirectToAction("Error", "Home");
}
finally { }
}
else
{
//if id is not valid, return to the list
response = RedirectToAction("Index", "Review");
}
return(response);
}
public ActionResult UpdateReview(int id)
{
ActionResult response;
ReviewVM viewModel = new ReviewVM();
//check if logged in
if (Session["RoleID"] != null)
{
//if logged in, check id
if (id > 0)
{
//if id is valid, access the database
try
{
//access database, map retrieved information
ReviewDO reviewDO = _ReviewDataAccess.ViewReviewByID(id);
//check permissions
if (Session["UserID"] != null && (int)Session["UserID"] == reviewDO.UserID || (int)Session["RoleID"] == 4 || (int)Session["RoleID"] == 6)
{
//if allowed, set information to the view model
//set review
viewModel.Review = _ReviewMapper.MapDOtoPO(reviewDO);
//set user
viewModel.User = _UserMapper.MapDOtoPO(_UserDataAccess.ViewUserByID(viewModel.Review.UserID));
//set game
viewModel.Game = _GameMapper.MapDOtoPO(_GameDataAccess.ViewGameByID(viewModel.Review.GameID));
response = View(viewModel);
}
else
{
//if not allowed, redirect to login
response = RedirectToAction("Login", "Account");
}
}
catch (Exception ex)
{
//log error
_Logger.ErrorLog(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, ex);
response = RedirectToAction("Error", "Home");
}
finally { }
}
else
{
//if id is not valid, return to list
response = RedirectToAction("Index", "Review");
}
}
else
{
//if not logged in, redirect to login page
response = RedirectToAction("Login", "Account");
}
return(response);
}
