public ActionResult BoardUpdate(Board iBoard)
{
if (ModelState.IsValid)
{
// Instantiate BoardMapper object
BoardMapper lBoardMapper = new BoardMapper();
BoardDBO lBoardDBO = lBoardMapper.MapBoardTOBoardDBO(iBoard);
// Instantiate BoardBLL object
BoardBLL lBoardBLL = new BoardBLL();
// Update the board in database
bool lResult = lBoardBLL.UpdateBoardByBoardID(lBoardDBO);
if (lResult)
{
// success
iBoard.type = 1;
iBoard.message = "Successfully Updated!";
}
else
{
// fail
iBoard.type = -1;
iBoard.message = "Failed to Update!";
}
}
else
{
iBoard.type = -1;
iBoard.message = "Please Fill all required fields.";
}
return(RedirectToAction("BoardView", "Board", new { @id = iBoard.BoardIDPK }));
}
public ActionResult BoardUpdate(int id)
{
// Instantiate BoardBLL object
BoardBLL lBoardBLL = new BoardBLL();
// check if the user requesting to edit the view is same user by boardID and UserID
bool lIsSameUser = lBoardBLL.FindBoolSameUserByUserIDAndBoardID(id, Convert.ToInt32(Session["AUTHUserIDPK"]));
// if it is different user who is requesting to edit the post, redirect to the view with error message
if (!lIsSameUser)
{
int lBoardID = id;
TempData["msg"] = "<script>alert('You are not authorized to edit.');</script>";
return(RedirectToAction("BoardView", "Board", new { id = lBoardID }));
}
// Find the board with id as primary key
BoardDBO lBoardDBO = lBoardBLL.FindBoardByBoardID(id);
// Instantiate Model.Board object
BoardMapper lBoardMapper = new BoardMapper();
// Map to Model.Board
Board lBoard = lBoardMapper.MapBoardDBOToBoard(lBoardDBO);
return(View(lBoard));
}
/// <summary>
/// Description: This method calls method in DAL layer to insert data into database and return integer value.
/// </summary>
/// <param name="iBoard"> A BoardDBO object with all the information to be inserted into database </param>
/// <returns>If the parameter iBoard is successfully inserted into database, return Inserted.IDPK. Otherwise, 0</returns>
public int CreateBoard(BoardDBO lBoardDBO)
{
// Instantiate BoardDAL object
BoardDAL lBoardDAL = new BoardDAL();
int lResult = lBoardDAL.CreateBoard(lBoardDBO);
return(lResult);
}
/// <summary>
/// Description: This method calls method in DAL layer to find and returns the post with specific id
/// </summary>
/// <param name="id">A unique Board Id</param>
/// <returns>BoardDBO object with the specific id</returns>
public BoardDBO FindBoardByBoardID(int id)
{
// Instantiate BoardDAL object
BoardDAL lBoardDAL = new BoardDAL();
BoardDBO oBoard = lBoardDAL.FindBoardByBoardID(id);
return(oBoard);
}
/// <summary>
/// Description: This method retrieves all the posts written by specific user by a unique UserID
/// </summary>
/// <param name="id">A unique ID for user</param>
/// <returns>List of posts that the user has written.</returns>
public List <BoardDBO> GetAllBoardsByUserID(int id)
{
// List to be returned
List <BoardDBO> lBoardList = new List <BoardDBO>();
try
{
// Establish connection
using (SqlConnection lConn = new SqlConnection(lConnectionString))
{
// Use stored procedure
using (SqlCommand lComm = new SqlCommand("sp_GetAllBoardsByUserID", lConn))
{
lComm.CommandType = CommandType.StoredProcedure;
lComm.CommandTimeout = 10;
// set parameter for stored procedure
lComm.Parameters.AddWithValue("@parm_user_id", SqlDbType.Int).Value = id;
// Open Connection
lConn.Open();
// Retrieves all the data about posts written by user with the id(parameter)
using (SqlDataReader lReader = lComm.ExecuteReader())
{
while (lReader.Read())
{
BoardDBO lBoard = new BoardDBO();
// set values
lBoard.BoardIDPK = (int)lReader["board_id"];
lBoard.UserIDFK = (int)lReader["user_id_FK"];
lBoard.UserName = (string)lReader["user_name"];
lBoard.Title = (string)lReader["title"];
lBoard.Content = (string)lReader["content"];
lBoard.DateCreated = (DateTime)lReader["date_created"];
lBoard.DateModified = (DateTime)lReader["date_modified"];
lBoard.IsFixed = Convert.ToInt32(lReader["is_fixed"]);
lBoard.CategoryIDFK = (int)lReader["category_id"];
lBoard.CategoryName = (string)lReader["category_name"];
// add to the list
lBoardList.Add(lBoard);
}
}
}
}
}
catch (Exception ex)
{
// handle exception
ExceptionDAL lExceptionDAL = new ExceptionDAL();
lExceptionDAL.CreateExceptionLog(ex);
}
return(lBoardList);
}
/// <summary>
/// Description: This method calls method in DAL layer to update a post in the database by BoardIDPK and returns bool value.
/// </summary>
/// <param name="iBoardDBO">A post to be updated.</param>
/// <returns>If the post is successfully updated, return True. Otherwise, false</returns>
public bool UpdateBoardByBoardID(BoardDBO iBoardDBO)
{
// Instantiate BoardDAL object
BoardDAL lBoardDAL = new BoardDAL();
bool lResult = lBoardDAL.UpdateBoardByBoardID(iBoardDBO);
return(lResult);
}
/// <summary>
/// Description: This method finds and returns the post with specific id
/// </summary>
/// <param name="id">A unique Board Id</param>
/// <returns>BoardDBO object with the specific id</returns>
public BoardDBO FindBoardByBoardID(int id)
{
// object to be returned
BoardDBO iBoardDBO = new BoardDBO();
try
{
// Establish connection
using (SqlConnection lConn = new SqlConnection(lConnectionString))
{
// Use stored procedure
using (SqlCommand lComm = new SqlCommand("sp_FindBoardByBoardID", lConn))
{
lComm.CommandType = CommandType.StoredProcedure;
lComm.CommandTimeout = 10;
// set parameter for stored procedure
lComm.Parameters.AddWithValue("@parm_board_id", SqlDbType.Int).Value = id;
// open connection
lConn.Open();
// Retrieves all that about the post with the id
using (SqlDataReader lReader = lComm.ExecuteReader())
{
while (lReader.Read())
{
// Assign values from database to an object of BoardDBO
iBoardDBO.BoardIDPK = (int)lReader["board_id"];
iBoardDBO.UserIDFK = (int)lReader["user_id_FK"];
iBoardDBO.UserName = (string)lReader["user_fname"] + " " + (string)lReader["user_lname"];
iBoardDBO.UserRoleName = (string)lReader["role_name"];
iBoardDBO.Title = (string)lReader["title"];
iBoardDBO.Content = (string)lReader["content"];
iBoardDBO.DateCreated = (DateTime)lReader["date_created"];
iBoardDBO.DateModified = (DateTime)lReader["date_modified"];
iBoardDBO.IsFixed = Convert.ToInt32(lReader["is_fixed"]);
iBoardDBO.CategoryIDFK = (int)lReader["category_id_FK"];
iBoardDBO.CategoryName = (string)lReader["category_name"];
}
return(iBoardDBO);
}
}
}
}
catch (Exception ex)
{
// Handle Exception
ExceptionDAL lExeceptionDAL = new ExceptionDAL();
lExeceptionDAL.CreateExceptionLog(ex);
}
return(null);
}
public ActionResult BoardCreate(Board iBoard)
{
// check if every input is valid
if (ModelState.IsValid)
{
//get value from executing query
int lResult = 0;
// Instantiate Mapper object
BoardMapper lBoardMapper = new BoardMapper();
// Map Model.Board to BoardDBO
BoardDBO lBoardDBO = lBoardMapper.MapBoardTOBoardDBO(iBoard);
// Instantiate DAL object
BoardBLL lBoardBLL = new BoardBLL();
// Change the UserIDFK to Session["AUTHUserIDPK"]
lBoardDBO.UserIDFK = Convert.ToInt32(Session["AUTHUserIDPK"]);
// insert into the database
lResult = lBoardBLL.CreateBoard(lBoardDBO);
// Success
if (lResult > 0)
{
// message on success
TempData["msg"] = "<script>alert('Successfully Written!');</script>";
return(RedirectToAction("BoardList", "Board"));
}
// Failed to insert
else
{
// message on failure
iBoard.type = -1;
iBoard.message = "Failed. Please try again.";
}
}
// ModelState is not valid
else
{
iBoard.type = -1;
iBoard.message = "Please Fill all Required Information.";
}
return(View(iBoard));
}
/// <summary>
/// Description: This method maps Board Model object to Board database object
/// </summary>
/// <param name="iBoard">Board Model object</param>
/// <returns>Board database object</returns>
public BoardDBO MapBoardTOBoardDBO(Board iBoard)
{
BoardDBO oBoard = new BoardDBO();
// set values
oBoard.BoardIDPK = iBoard.BoardIDPK;
oBoard.UserIDFK = iBoard.UserIDFK;
oBoard.UserName = iBoard.UserName;
oBoard.Title = iBoard.Title;
oBoard.Content = iBoard.Content;
oBoard.DateCreated = iBoard.DateCreated;
oBoard.DateModified = iBoard.DateModified;
oBoard.IsFixed = iBoard.IsFixed;
oBoard.CategoryIDFK = iBoard.CategoryIDFK;
oBoard.CategoryName = iBoard.CategoryName;
return(oBoard);
}
/// <summary>
/// Description: This method updates a post in the database by BoardIDPK and returns bool value.
/// </summary>
/// <param name="iBoardDBO">A post to be updated.</param>
/// <returns>If the post is successfully updated, return True. Otherwise, false</returns>
public bool UpdateBoardByBoardID(BoardDBO iBoardDBO)
{
// variable to be returned
bool lResult = false;
try
{
// Establish connection
using (SqlConnection lConn = new SqlConnection(lConnectionString))
{
// Use stored procedure
using (SqlCommand lComm = new SqlCommand("sp_UpdateBoardByBoardID", lConn))
{
lComm.CommandType = CommandType.StoredProcedure;
lComm.CommandTimeout = 10;
// set values for parameters needed for stored procedure
lComm.Parameters.AddWithValue("@parm_board_id", SqlDbType.Int).Value = iBoardDBO.BoardIDPK;
lComm.Parameters.AddWithValue("@parm_title", SqlDbType.VarChar).Value = iBoardDBO.Title;
lComm.Parameters.AddWithValue("@parm_content", SqlDbType.VarChar).Value = iBoardDBO.Content;
lComm.Parameters.AddWithValue("@parm_is_fixed", SqlDbType.Bit).Value = iBoardDBO.IsFixed;
lComm.Parameters.AddWithValue("@parm_category_id_FK", SqlDbType.Int).Value = iBoardDBO.CategoryIDFK;
// Open connection
lConn.Open();
// Execute query
lComm.ExecuteNonQuery();
// change return value
lResult = true;
}
}
}
catch (Exception ex)
{
// Handle exception
ExceptionDAL lExceptionDAL = new ExceptionDAL();
lExceptionDAL.CreateExceptionLog(ex);
}
return(lResult);
}
/// <summary>
/// Description: This method inserts Board data into database and return integer value.
/// </summary>
/// <param name="iBoard"> A BoardDBO object with all the information to be inserted into database </param>
/// <returns>If the parameter iBoard is successfully inserted into database, return Inserted.IDPK. Otherwise, 0</returns>
public int CreateBoard(BoardDBO iBoard)
{
// variable to be returned
int lResult = 0;
try
{
// Establish conneciton
using (SqlConnection lConn = new SqlConnection(lConnectionString))
{
// Use stored procedure
using (SqlCommand lComm = new SqlCommand("sp_CreateBoard", lConn))
{
lComm.CommandType = CommandType.StoredProcedure;
lComm.CommandTimeout = 10;
// set values to be inserted as parameters for stored procedure
lComm.Parameters.AddWithValue("@parm_user_id_FK", SqlDbType.Int).Value = iBoard.UserIDFK;
lComm.Parameters.AddWithValue("@parm_title", SqlDbType.VarChar).Value = iBoard.Title;
lComm.Parameters.AddWithValue("@parm_content", SqlDbType.Text).Value = iBoard.Content;
lComm.Parameters.AddWithValue("@parm_is_fixed", SqlDbType.Bit).Value = iBoard.IsFixed;
lComm.Parameters.AddWithValue("@parm_category_id_FK", SqlDbType.Int).Value = iBoard.CategoryIDFK;
// Open Connection
lConn.Open();
// Execute Query and get Inserted.IDPK as return value
lResult = Convert.ToInt32(lComm.ExecuteScalar());
}
}
}
catch (Exception ex)
{
// Handle exception
ExceptionDAL lExceptionDAL = new ExceptionDAL();
lExceptionDAL.CreateExceptionLog(ex);
}
return(lResult);
}
public ActionResult BoardView(int id)
{
// Instantiate BCViewModel to pass three models
BoardAndCommentsViewModel lBCViewModel = new BoardAndCommentsViewModel();
// Instantiate Mapper Objects
BoardCommentMapper lBCMapper = new BoardCommentMapper();
BoardMapper lBoardMapper = new BoardMapper();
// Instantiate BLL Objects
BoardBLL lBoardBLL = new BoardBLL();
BoardCommentBLL lBoardCommentBLL = new BoardCommentBLL();
// Retreive data for post from database based on BoardIDPK
BoardDBO lBoardDBO = lBoardBLL.FindBoardByBoardID(id);
// if there is no post with the id, redirect to board list with error message
if (lBoardDBO == null)
{
TempData["msg"] = "<script>alert('Error occured while processing your request.')</script>";
return(RedirectToAction("BoardList", "Board"));
}
// Retreive data for comments from database based on BoardIDPK
List <BoardCommentDBO> lBoardCommentDBOList = lBoardCommentBLL.GetAllCommentsByBoardID(id);
// Map DB objects to Model.BoardComment
lBCViewModel.BoardCommentList = lBCMapper.MapBoardCommentDBOToBoardComment(lBoardCommentDBOList);
lBCViewModel.Board = lBoardMapper.MapBoardDBOToBoard(lBoardDBO);
// set values for board comment
lBCViewModel.BoardComment = new BoardComment();
lBCViewModel.BoardComment.BoardIDFK = lBCViewModel.Board.BoardIDPK;
lBCViewModel.BoardComment.UserIDFK = Convert.ToInt32(Session["AUTHUserIDPK"]);
return(View(lBCViewModel));
}
/// <summary>
/// Description: This method finds and returns the list of specific posts required by user
/// </summary>
/// <param name="category">category name that a user specified </param>
/// <param name="searchString">search string that a user specified </param>
/// <returns>List of posts that are in the same category and contains searchString that user specified.</returns>
public List <BoardDBO> GetAllBoards(string category, string searchString)
{
// List to be returned
List <BoardDBO> lBoardList = new List <BoardDBO>();
try
{
// establish connection
using (SqlConnection lConn = new SqlConnection(lConnectionString))
{
// use stored procedure
using (SqlCommand lComm = new SqlCommand("sp_GetAllBoards", lConn))
{
lComm.CommandType = CommandType.StoredProcedure;
lComm.CommandTimeout = 10;
// set parameters for stroed procedure
lComm.Parameters.AddWithValue("@parm_category_id", SqlDbType.Int).Value = Convert.ToInt32(category);
// if searchString is not null, pass the variable for parameter.
// If it is, pass DB null value.
if (!string.IsNullOrEmpty(searchString))
{
lComm.Parameters.AddWithValue("@parm_search_string", SqlDbType.VarChar).Value = searchString;
}
else
{
lComm.Parameters.AddWithValue("@parm_search_string", SqlDbType.VarChar).Value = DBNull.Value;
}
// Open connection
lConn.Open();
// Retrieve all the data of posts that meet user's requirements and add to list
using (SqlDataReader lReader = lComm.ExecuteReader())
{
while (lReader.Read())
{
BoardDBO lBoard = new BoardDBO();
// set values
lBoard.BoardIDPK = (int)lReader["board_id"];
lBoard.UserIDFK = (int)lReader["user_id_FK"];
lBoard.UserName = (string)lReader["user_fname"] + " " + (string)lReader["user_lname"];
lBoard.Title = (string)lReader["title"];
lBoard.Content = (string)lReader["content"];
lBoard.DateCreated = (DateTime)lReader["date_created"];
lBoard.DateModified = (DateTime)lReader["date_modified"];
lBoard.IsFixed = Convert.ToInt32(lReader["is_fixed"]);
lBoard.CategoryIDFK = (int)lReader["category_id_FK"];
lBoard.CategoryName = (string)lReader["category_name"];
lBoardList.Add(lBoard);
}
}
}
}
}
catch (Exception ex)
{
// Handle exception
ExceptionDAL lExceptionDAL = new ExceptionDAL();
lExceptionDAL.CreateExceptionLog(ex);
}
return(lBoardList);
}
