Esempio n. 1
0
        /// <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);
        }
Esempio n. 2
0
        /// <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);
        }
Esempio n. 3
0
        /// <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);
        }
Esempio n. 4
0
        /// <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);
        }
Esempio n. 5
0
        /// <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);
        }
Esempio n. 6
0
        /// <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("&nbsp;", 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);
        }
Esempio n. 7
0
        /// <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);
        }
Esempio n. 8
0
        /// <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);
        }
Esempio n. 9
0
        /// <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);
        }
Esempio n. 10
0
        /// <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);
        }
Esempio n. 11
0
        /// <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);
        }
Esempio n. 12
0
        /// <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);
        }
Esempio n. 13
0
        /// <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);
        }
Esempio n. 14
0
        /// <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);
        }
Esempio n. 15
0
        /// <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);
        }
Esempio n. 16
0
        /// <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);
        }
Esempio n. 17
0
        /// <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);
        }
Esempio n. 18
0
        /// <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);
        }
Esempio n. 19
0
        /// <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);
        }
Esempio n. 20
0
        /// <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);
        }
Esempio n. 21
0
        /// <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);
        }
Esempio n. 22
0
        /// <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);
        }