/// <summary> /// Description: This method checks if there is a user using same login ID /// </summary> /// <param name="iUserLogInID">LogIn ID to be checked</param> /// <returns>If there is no user with same id, returns 0. Otherwise, number of users with the same userid</returns> public int FindUserByUserLogInID(string iUserLogInID) { // return value int lResult = 0; try { // establish connection using (SqlConnection lConn = new SqlConnection(lConnectionString)) { // use stored procedure using (SqlCommand lComm = new SqlCommand("sp_FindUserByUserLogInID", lConn)) { lComm.CommandType = CommandType.StoredProcedure; lComm.CommandTimeout = 10; // set parameter for stored procedure lComm.Parameters.AddWithValue("parm_user_login_id", SqlDbType.VarChar).Value = iUserLogInID; // open connection lConn.Open(); lResult = Convert.ToInt32(lComm.ExecuteScalar()); } } } catch (Exception ex) { // handle exception ExceptionDAL lExceptionDAL = new ExceptionDAL(); lExceptionDAL.CreateExceptionLog(ex); } return(lResult); }
/// <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 inserts user into database /// </summary> /// <param name="iNewUser">User object with data</param> /// <returns>If successfully created, return UserID. Otherwise, 0</returns> public int CreateUser(UserDBO iNewUser) { // store number of rows affected in database int lResult = 0; try { // establish connection using (SqlConnection lConn = new SqlConnection(lConnectionString)) { // use stored procedure using (SqlCommand lComm = new SqlCommand("sp_CreateUser", lConn)) { lComm.CommandType = CommandType.StoredProcedure; lComm.CommandTimeout = 10; // set parameters for stored procedure lComm.Parameters.AddWithValue("@parm_user_fname", SqlDbType.VarChar).Value = iNewUser.UserFirstName; lComm.Parameters.AddWithValue("@parm_user_lname", SqlDbType.VarChar).Value = iNewUser.UserLastName; lComm.Parameters.AddWithValue("@parm_user_login_id", SqlDbType.VarChar).Value = iNewUser.UserLogInID; lComm.Parameters.AddWithValue("@parm_user_password", SqlDbType.VarChar).Value = iNewUser.UserPassword; lComm.Parameters.AddWithValue("@parm_user_birth", SqlDbType.VarChar).Value = iNewUser.UserBirth; // check if UserEmail is either null or empty. if (string.IsNullOrEmpty(iNewUser.UserEmail)) { lComm.Parameters.AddWithValue("@parm_user_email", SqlDbType.VarChar).Value = DBNull.Value; } else { lComm.Parameters.AddWithValue("@parm_user_email", SqlDbType.VarChar).Value = iNewUser.UserEmail; } // check if UserPhone is either null or empty. if (String.IsNullOrEmpty(iNewUser.UserPhone)) { lComm.Parameters.AddWithValue("@parm_user_phone", SqlDbType.VarChar).Value = DBNull.Value; } else { lComm.Parameters.AddWithValue("@parm_user_phone", SqlDbType.VarChar).Value = iNewUser.UserPhone; } // open connection lConn.Open(); // get Inserted.UserID lResult = Convert.ToInt32(lComm.ExecuteScalar()); } } } catch (Exception ex) { // handle exception ExceptionDAL lException = new ExceptionDAL(); lException.CreateExceptionLog(ex); } return(lResult); }
/// <summary> /// Description: This method finds a user with specifc id /// </summary> /// <param name="iUserIDPK"></param> /// <returns>User with the id</returns> public UserDBO FindUserByID(int iUserIDPK) { // User object to be returned UserDBO lUser = new UserDBO(); try { // establish connection using (SqlConnection lConn = new SqlConnection(lConnectionString)) { // use stored procedure using (SqlCommand lComm = new SqlCommand("sp_FindUserByUserID", lConn)) { lComm.CommandType = CommandType.StoredProcedure; lComm.CommandTimeout = 10; // set parameter for stored procedure lComm.Parameters.AddWithValue("@parm_user_id", SqlDbType.Int).Value = iUserIDPK; // open connection lConn.Open(); using (SqlDataReader lReader = lComm.ExecuteReader()) { // Get data about user with the id while (lReader.Read()) { // set values lUser.UserIDPK = (int)lReader["user_id"]; lUser.UserFirstName = (string)lReader["user_fname"]; lUser.UserLastName = (string)lReader["user_lname"]; lUser.UserLogInID = (string)lReader["user_login_id"]; lUser.UserPassword = (string)lReader["user_password"]; lUser.UserBirth = (DateTime)lReader["user_birth"]; lUser.UserIsActive = Convert.ToInt32(lReader["is_active"]); lUser.UserDateCreated = (DateTime)lReader["date_created"]; lUser.UserDateModified = (DateTime)lReader["date_modified"]; lUser.UserRoleIDFK = (int)lReader["role_id_FK"]; lUser.UserRoleName = (string)lReader["role_name"]; // check null values lUser.UserEmail = lReader["user_email"] == DBNull.Value ? "Unknown" : (string)lReader["user_email"]; lUser.UserPhone = lReader["user_phone"] == DBNull.Value ? "Unknown" : (string)lReader["user_phone"]; } } } } } catch (Exception ex) { // handle Exception ExceptionDAL lExceptionDAL = new ExceptionDAL(); lExceptionDAL.CreateExceptionLog(ex); } return(lUser); }
/// <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 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 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 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 checks if user submited valid id and password /// </summary> /// <param name="iUser">User object with data</param> /// <returns>User object with data on success or null on failure</returns> public UserDBO FindUserByLogInIDAndPassword(UserDBO iUser) { // return value UserDBO lUser = null; try { // establish connection using (SqlConnection lConn = new SqlConnection(lConnectionString)) { // use stored procedure using (SqlCommand lComm = new SqlCommand("sp_FindUserByLogInIDAndPassword", lConn)) { lComm.CommandType = CommandType.StoredProcedure; lComm.CommandTimeout = 10; // set parameters for stored procedure lComm.Parameters.AddWithValue("@parm_user_login_id", SqlDbType.VarChar).Value = iUser.UserLogInID; lComm.Parameters.AddWithValue("@parm_user_password", SqlDbType.VarChar).Value = iUser.UserPassword; // open connection lConn.Open(); using (SqlDataReader lReader = lComm.ExecuteReader()) { // retrieve data about the user while (lReader.Read()) { lUser = new UserDBO(); // set values lUser.UserIDPK = (int)lReader["user_id"]; lUser.UserLastName = (string)lReader["user_lname"]; lUser.UserFirstName = (string)lReader["user_fname"]; lUser.UserLogInID = (string)lReader["user_login_id"]; lUser.UserRoleName = (string)lReader["role_name"]; lUser.UserIsActive = Convert.ToInt32(lReader["is_active"]); } } } } } catch (Exception ex) { // handle exception ExceptionDAL lExceptionDAL = new ExceptionDAL(); lExceptionDAL.CreateExceptionLog(ex); } return(lUser); }
/// <summary> /// Description: This method returns list of possible roles /// </summary> /// <returns>List of roles</returns> public List <Role> GetAllRoles() { // list to be returned List <Role> lRoleList = new List <Role>(); try { // establish connection using (SqlConnection lConn = new SqlConnection(lConnectionString)) { // use stored procedure using (SqlCommand lComm = new SqlCommand("sp_GetAllRoleNames", lConn)) { lComm.CommandType = CommandType.StoredProcedure; lComm.CommandTimeout = 10; // open connection lConn.Open(); using (SqlDataReader lReader = lComm.ExecuteReader()) { // add each role to the list while (lReader.Read()) { Role eachRole = new Role(); // set values eachRole.RoleIDPK = (int)lReader["role_id"]; eachRole.RoleName = (string)lReader["role_name"]; lRoleList.Add(eachRole); } } } } } catch (Exception ex) { // handle exception ExceptionDAL lExceptionDAL = new ExceptionDAL(); lExceptionDAL.CreateExceptionLog(ex); } return(lRoleList); }
/// <summary> /// Description: This method inserts user's rating on food order /// </summary> /// <param name="iUserOrderRatingDBO">UserOrderRating object with data</param> /// <returns>If successfully inserted, returns a unique rating id. Otherwise, 0</returns> public int CreateUserOrderRating(UserOrderRatingDBO iUserOrderRatingDBO) { // return value int lResult = 0; try { // establish connection using (SqlConnection lConn = new SqlConnection(lConnectionString)) { // use stored procedure using (SqlCommand lComm = new SqlCommand("sp_CreateUserOrderRating", lConn)) { lComm.CommandType = CommandType.StoredProcedure; lComm.CommandTimeout = 10; // set parameters for stored procedure lComm.Parameters.AddWithValue("@parm_user_order_id_FK", SqlDbType.Int).Value = iUserOrderRatingDBO.UserOrderIDFK; lComm.Parameters.AddWithValue("@parm_score", SqlDbType.Float).Value = iUserOrderRatingDBO.Score; // check null value if (string.IsNullOrEmpty(iUserOrderRatingDBO.Content)) { lComm.Parameters.AddWithValue("@parm_content", SqlDbType.VarChar).Value = DBNull.Value; } else { lComm.Parameters.AddWithValue("@parm_content", SqlDbType.VarChar).Value = iUserOrderRatingDBO.Content; } // open connection lConn.Open(); lResult = Convert.ToInt32(lComm.ExecuteScalar()); } } } catch (Exception ex) { // handle exception ExceptionDAL lExceptionDAL = new ExceptionDAL(); lExceptionDAL.CreateExceptionLog(ex); } return(lResult); }
/// <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 updates a user's status /// </summary> /// <param name="iUserIDPK">A unique user id</param> /// <param name="iIsActive">user status (1: active, 0: inactive)</param> /// <returns>If successfully updated, return true. Otherwise, false</returns> public bool UpdateUserStatusByUserID(int iUserIDPK, int iIsActive) { // return false bool lResult = false; try { // establish connection using (SqlConnection lConn = new SqlConnection(lConnectionString)) { // use stored procedure using (SqlCommand lComm = new SqlCommand("sp_UpdateUserStatusByUserID", lConn)) { lComm.CommandType = CommandType.StoredProcedure; lComm.CommandTimeout = 10; // set parameters for stored procedure lComm.Parameters.AddWithValue("@parm_user_id", SqlDbType.Int).Value = iUserIDPK; // if user is active, change status to inactive. // if user is inactive, change status to active lComm.Parameters.AddWithValue("@parm_user_is_active", SqlDbType.Int).Value = iIsActive == 1 ? 0 : 1; // 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 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 removes the board in database by boardID and return bool value. /// </summary> /// <param name="iBoardID">A unique BoardIDPK to find the post to be removed </param> /// <returns>If the post is successfully removed, return True. Otherwise, false.</returns> public bool RemoveBoardByBoardID(int iBoardID) { // variable to be returend bool iResult = false; try { // Establish connection using (SqlConnection lConn = new SqlConnection(lConnectionString)) { // Use stored procudure using (SqlCommand lComm = new SqlCommand("sp_RemoveBoardByBoardID", lConn)) { lComm.CommandType = CommandType.StoredProcedure; lComm.CommandTimeout = 10; // Set value lComm.Parameters.AddWithValue("@parm_board_id", SqlDbType.Int).Value = iBoardID; // Open Conenction lConn.Open(); // Execute Query lComm.ExecuteNonQuery(); // Update return value iResult = true; } } } catch (Exception ex) { // Handle Exception ExceptionDAL lExceptionDAL = new ExceptionDAL(); lExceptionDAL.CreateExceptionLog(ex); } return(iResult); }
/// <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); }
/// <summary> /// Description: This method retrieves all users from database /// </summary> /// <param name="iSearchString">string to be searched in the list</param> /// <returns>List of Users in database</returns> public List <UserDBO> GetAllUsers(string iSearchString) { // list of users to be returneed List <UserDBO> lUserList = new List <UserDBO>(); try { // establish connection using (SqlConnection lConn = new SqlConnection(lConnectionString)) { // use stored procedure using (SqlCommand lComm = new SqlCommand("sp_GetAllUsers", lConn)) { lComm.CommandType = CommandType.StoredProcedure; lComm.CommandTimeout = 10; // if there is no search string, pass null if (!string.IsNullOrEmpty(iSearchString)) { lComm.Parameters.AddWithValue("@parm_search_string", SqlDbType.VarChar).Value = iSearchString; } else { lComm.Parameters.AddWithValue("@parm_search_string", SqlDbType.VarChar).Value = DBNull.Value; } // open connection lConn.Open(); using (SqlDataReader lReader = lComm.ExecuteReader()) { // retrieve data about all users, and add each user to the list while (lReader.Read()) { // Instantiate a user UserDBO lUser = new UserDBO(); // set values lUser.UserIDPK = (int)lReader["user_id"]; lUser.UserFirstName = (string)lReader["user_fname"]; lUser.UserLastName = (string)lReader["user_lname"]; lUser.UserLogInID = (string)lReader["user_login_id"]; lUser.UserPassword = (string)lReader["user_password"]; lUser.UserBirth = (DateTime)lReader["user_birth"]; lUser.UserRoleIDFK = (int)lReader["role_id_FK"]; lUser.UserRoleName = (string)lReader["role_name"]; lUser.UserDateCreated = (DateTime)lReader["date_created"]; lUser.UserDateModified = (DateTime)lReader["date_modified"]; lUser.UserIsActive = Convert.ToInt32(lReader["is_active"]); // check null DBNull values lUser.UserEmail = lReader["user_email"] == DBNull.Value ? "Unknown" : (string)lReader["user_email"]; lUser.UserPhone = lReader["user_phone"] == DBNull.Value ? "Unknown" : (string)lReader["user_phone"]; lUserList.Add(lUser); } } } } } catch (Exception ex) { // handle exception ExceptionDAL lException = new ExceptionDAL(); lException.CreateExceptionLog(ex); } return(lUserList); }
/// <summary> /// Description: This method updates data for a user by User id /// </summary> /// <param name="iUser">User with the data to be updated</param> /// <returns>If successfully updated, return true. Otherwise, false</returns> public bool UpdateUserByUserID(UserDBO iUser) { // return value bool lResult = false; try { // establish connection using (SqlConnection lConn = new SqlConnection(lConnectionString)) { // use stored procedure using (SqlCommand lComm = new SqlCommand("sp_UpdateUserByUserID", lConn)) { lComm.CommandType = CommandType.StoredProcedure; lComm.CommandTimeout = 10; // set parameters for stored procedure lComm.Parameters.AddWithValue("@parm_user_id", SqlDbType.Int).Value = iUser.UserIDPK; lComm.Parameters.AddWithValue("@parm_role_id", SqlDbType.Int).Value = iUser.UserRoleIDFK; lComm.Parameters.AddWithValue("@parm_user_fname", SqlDbType.VarChar).Value = iUser.UserFirstName; lComm.Parameters.AddWithValue("@parm_user_lname", SqlDbType.VarChar).Value = iUser.UserLastName; lComm.Parameters.AddWithValue("@parm_user_login_id", SqlDbType.VarChar).Value = iUser.UserLogInID; lComm.Parameters.AddWithValue("@parm_user_password", SqlDbType.VarChar).Value = iUser.UserPassword; lComm.Parameters.AddWithValue("@parm_user_birth", SqlDbType.DateTime).Value = iUser.UserBirth; lComm.Parameters.AddWithValue("@parm_is_active", SqlDbType.Bit).Value = iUser.UserIsActive; // check if null if (string.IsNullOrEmpty(iUser.UserEmail)) { lComm.Parameters.AddWithValue("@parm_user_email", SqlDbType.VarChar).Value = DBNull.Value; } else { lComm.Parameters.AddWithValue("@parm_user_email", SqlDbType.VarChar).Value = iUser.UserEmail; } // check if null if (string.IsNullOrEmpty(iUser.UserPhone)) { lComm.Parameters.AddWithValue("@parm_user_phone", SqlDbType.VarChar).Value = DBNull.Value; } else { lComm.Parameters.AddWithValue("@parm_user_phone", SqlDbType.VarChar).Value = iUser.UserPhone; } // Open connection lConn.Open(); lComm.ExecuteNonQuery(); lResult = true; } } } catch (Exception ex) { // handle Exception ExceptionDAL lExceptionDAL = new ExceptionDAL(); lExceptionDAL.CreateExceptionLog(ex); } return(lResult); }