/// <summary> /// Description: This method removes board comment with id from database /// </summary> /// <param name="id">A unique board comment</param> /// <returns>If board comment is removed successfully, return true. Otherwise, false</returns> public bool RemoveBoardCommentByBoardCommentID(int id) { // return value bool lResult = false; try { // establish connection using (SqlConnection lConn = new SqlConnection(lConnectionString)) { // use stored procedure using (SqlCommand lComm = new SqlCommand("sp_RemoveBoardCommentByBoardCommentID", lConn)) { lComm.CommandType = CommandType.StoredProcedure; lComm.CommandTimeout = 10; // set parameters for stored procedure lComm.Parameters.AddWithValue("@parm_comment_id", SqlDbType.Int).Value = id; // open connection lConn.Open(); lComm.ExecuteNonQuery(); lResult = true; } } }catch (Exception ex) { // handle exception ExceptionDAL lExceptionDAL = new ExceptionDAL(); lExceptionDAL.CreateExceptionLog(ex); } return(lResult); }
/// <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 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); }
/// <summary> /// Description: This method returns list of board comments written by a user with id /// </summary> /// <param name="id"> A unique User ID</param> /// <returns>List of board comments written by a specific user</returns> public List <BoardCommentDBO> GetAllCommentsByUserID(int id) { // List to be returned List <BoardCommentDBO> lCommentList = new List <BoardCommentDBO>(); try { // establish connection using (SqlConnection lConn = new SqlConnection(lConnectionString)) { // use stored procedure using (SqlCommand lComm = new SqlCommand("sp_GetAllCommentsByUserID", 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(); using (SqlDataReader lReader = lComm.ExecuteReader()) { // Retrieve all data bout board comments while (lReader.Read()) { BoardCommentDBO lComment = new BoardCommentDBO(); lComment.BoardCommentIDPK = (int)lReader["comment_id"]; lComment.BoardIDFK = (int)lReader["board_id_FK"]; lComment.UserIDFK = (int)lReader["user_id_FK"]; lComment.UserName = (string)lReader["user_name"]; lComment.UserRoleName = (string)lReader["role_name"]; // Replace unnecessary string string lContent = (string)lReader["content"]; string lFilteredContent = lContent.Replace(" ", string.Empty); lComment.Content = lContent; lComment.DateCreated = (DateTime)lReader["date_created"]; lComment.DateModified = (DateTime)lReader["date_modified"]; lCommentList.Add(lComment); } } } } }catch (Exception ex) { // handle exception ExceptionDAL lExceptionDAL = new ExceptionDAL(); lExceptionDAL.CreateExceptionLog(ex); } return(lCommentList); }
/// <summary> /// Description: This method returns a list of board comments related to the post with id /// </summary> /// <param name="id"> A unique Board ID</param> /// <returns> List of board comments </returns> public List <BoardCommentDBO> GetAllCommentsByBoardID(int id) { // List to store comments List <BoardCommentDBO> lBoardCommentDBOList = new List <BoardCommentDBO>(); try { // Establish connection using (SqlConnection lConn = new SqlConnection(lConnectionString)) { // use stored procedure using (SqlCommand lComm = new SqlCommand("sp_GetAllCommentsByBoardID", 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(); using (SqlDataReader lReader = lComm.ExecuteReader()) { // Retrieve data about all board comments while (lReader.Read()) { // Board commented to be added into the list to be returned BoardCommentDBO lBoardCommentDBO = new BoardCommentDBO(); // set values lBoardCommentDBO.BoardCommentIDPK = (int)lReader["comment_id"]; lBoardCommentDBO.BoardIDFK = (int)lReader["board_id_FK"]; lBoardCommentDBO.UserIDFK = (int)lReader["user_id_FK"]; lBoardCommentDBO.UserName = lReader["user_name"] == DBNull.Value ? "Deleted User" : (string)lReader["user_name"];; lBoardCommentDBO.UserRoleName = lReader["role_name"] == DBNull.Value ? "" : (string)lReader["role_name"]; lBoardCommentDBO.Content = ((string)lReader["content"]); lBoardCommentDBO.DateCreated = (DateTime)lReader["date_created"]; lBoardCommentDBO.DateModified = (DateTime)lReader["date_modified"]; lBoardCommentDBOList.Add(lBoardCommentDBO); } } } } } catch (Exception ex) { // Handle Exception ExceptionDAL lExceptionDAL = new ExceptionDAL(); lExceptionDAL.CreateExceptionLog(ex); } return(lBoardCommentDBOList); }
/// <summary> /// Description: This method returns BoardComment object with all data /// </summary> /// <param name="id">A unique BoardComment ID</param> /// <returns>If there is BoardComment with the id, return BoardComment object with data. Otherwise, null</returns> public BoardCommentDBO FindBoardCommentByBoardCommentID(int id) { // return value BoardCommentDBO lBoardCommentDBO = null; try { // establish connection using (SqlConnection lConn = new SqlConnection(lConnectionString)) { // use stored procedure using (SqlCommand lComm = new SqlCommand("sp_FindBoardCommentByBoardCommentID", lConn)) { lComm.CommandType = CommandType.StoredProcedure; lComm.CommandTimeout = 10; // set parameter for stored procedure lComm.Parameters.AddWithValue("@parm_comment_id", SqlDbType.Int).Value = id; // open connection lConn.Open(); using (SqlDataReader lReader = lComm.ExecuteReader()) { // if there is comment with the id, retrieve data while (lReader.Read()) { lBoardCommentDBO = new BoardCommentDBO(); lBoardCommentDBO.BoardCommentIDPK = (int)lReader["comment_id"]; lBoardCommentDBO.BoardIDFK = (int)lReader["board_id_FK"]; lBoardCommentDBO.UserIDFK = (int)lReader["user_id_FK"]; lBoardCommentDBO.Content = (string)lReader["content"]; lBoardCommentDBO.UserRoleName = (string)lReader["role_name"]; lBoardCommentDBO.UserName = (string)lReader["user_name"]; lBoardCommentDBO.DateCreated = (DateTime)lReader["board_date_created"]; lBoardCommentDBO.DateModified = (DateTime)lReader["board_date_modified"]; } } } } } catch (Exception ex) { // handle exception ExceptionDAL lExceptionDAL = new ExceptionDAL(); lExceptionDAL.CreateExceptionLog(ex); } return(lBoardCommentDBO); }
/// <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 retrieves all the list of categories for board from database /// </summary> /// <returns>List of Categories for board</returns> public List <CategoryDBO> GetAllBoardCategories() { // List to store all the categories List <CategoryDBO> lCategoryList = new List <CategoryDBO>(); try { using (SqlConnection lConn = new SqlConnection(lConnectionString)) { using (SqlCommand lComm = new SqlCommand("sp_GetAllBoardCategories", lConn)) { lComm.CommandType = CommandType.StoredProcedure; lComm.CommandTimeout = 10; lConn.Open(); using (SqlDataReader lReader = lComm.ExecuteReader()) { // Add all (all the categories) lCategoryList.Add(new CategoryDBO(0, "All")); // Retrieve all categories and add to the list while (lReader.Read()) { int lCategoryID = (int)lReader["category_id"]; string lCategoryName = (string)lReader["category_name"]; lCategoryList.Add(new CategoryDBO(lCategoryID, lCategoryName)); } } } } } catch (Exception ex) { // Handle Exception ExceptionDAL lExceptionDAL = new ExceptionDAL(); lExceptionDAL.CreateExceptionLog(ex); } return(lCategoryList); }
/// <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); }
/// <summary> /// Description: This method inserts comment data into database and return integer value /// </summary> /// <param name="iBoardCommentDBO">BoardComment object with data</param> /// <returns>A unique BoardComment ID on success. Otherwise, 0</returns> public int CreateBoardComment(BoardCommentDBO iBoardCommentDBO) { // return value int lResult = 0; try { // Establish connection using (SqlConnection lConn = new SqlConnection(lConnectionString)) { // use stored procedure using (SqlCommand lComm = new SqlCommand("sp_CreateBoardComment", lConn)) { lComm.CommandType = CommandType.StoredProcedure; lComm.CommandTimeout = 10; //set parameters for stored procedure lComm.Parameters.AddWithValue("@parm_board_id_FK", SqlDbType.Int).Value = iBoardCommentDBO.BoardIDFK; lComm.Parameters.AddWithValue("@parm_user_id_FK", SqlDbType.Int).Value = iBoardCommentDBO.UserIDFK; lComm.Parameters.AddWithValue("@parm_content", SqlDbType.VarChar).Value = iBoardCommentDBO.Content; // open connection lConn.Open(); // Get Inserted.BoardIDPK as return value lResult = (int)lComm.ExecuteScalar(); } } } catch (Exception ex) { // handle exception ExceptionDAL lExceptionDAL = new ExceptionDAL(); lExceptionDAL.CreateExceptionLog(ex); } return(lResult); }
/// <summary> /// Description: This methods checks if the user who resquested to edit a post has the same user id. /// </summary> /// <param name="iBoardIDPK"> A unique Board ID </param> /// <param name="iUserIDPK"> A unique User ID</param> /// <returns>If it is the same user, return true. Otherwise, false </returns> public bool FindBoolSameUserByUserIDAndBoardID(int iBoardIDPK, int iUserIDPK) { // bool value to be returned bool lResult = false; try{ // Establish connecction using (SqlConnection lConn = new SqlConnection(lConnectionString)) { // User stored procedure using (SqlCommand lComm = new SqlCommand("sp_FindBoolSameUserByUserIDAndBoardID", lConn)) { lComm.CommandType = CommandType.StoredProcedure; lComm.CommandTimeout = 10; // Set paratemers for stored procedure lComm.Parameters.AddWithValue("@parm_user_id", SqlDbType.Int).Value = iUserIDPK; lComm.Parameters.AddWithValue("@parm_board_id", SqlDbType.Int).Value = iBoardIDPK; // Open connection lConn.Open(); // if there is a board with same UserID and BoardID, stored procedure returns 1. Otherwise, 0. // if returned value is 1, the post is written by the user who requested to edit. lResult = Convert.ToInt32(lComm.ExecuteScalar()) == 0? false : true; } } }catch (Exception ex) { // Handle Exception ExceptionDAL lExeceptionDAL = new ExceptionDAL(); lExeceptionDAL.CreateExceptionLog(ex); } return(lResult); }
/// <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); }