/// <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);
}