/// <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);
}
public ActionResult UserStatusUpdate(int iUserID, int status)
{
// Instantiate objects
UserBLL lUserBLL = new UserBLL();
// Find the user with the id to retreive first name and last name
UserDBO lUserUpdated = lUserBLL.FindUserByID(iUserID);
// Update the status
bool lResult = lUserBLL.UpdateUserStatusByUserID(iUserID, status);
// Success
if (lResult)
{
// Different messages for Activating/Deactivating
if (status == 1)
{
TempData["msg"] = "<script>alert('Successfully Deactivated!')</script>";
}
else
{
TempData["msg"] = "<script>alert('Successfully Activated!')</script>";
}
}
// failure
else
{
TempData["msg"] = "<script>alert('Error occured while processing your request. Please try later.')</script>";
}
return(RedirectToAction("UserProfile", "User", new { @id = iUserID }));
}
public int InsertUser(User u)
{
UserDBO dbc = new UserDBO();
int w = dbc.Insert(u);
return(w);
}
[MustBeLoggedIn] // same user
public ActionResult UserUpdate(int id)
{
// if UserIDPK is not proper, redirect to user list
if (id < 1)
{
TempData["msg"] = "<script>alert('Invalid User');</script>";
return(RedirectToAction("UserList", "User"));
}
// Valid UserIDPK
else
{
// instantiate objects
UserBLL lUserBLL = new UserBLL();
UserMapper lUserMapper = new UserMapper();
// Get the user from database
UserDBO lUserEdited = lUserBLL.FindUserByID(id);
// Map User database object to User Model object
User lUser = lUserMapper.MapUserDBOToUser(lUserEdited);
// Get the list of roles
List <Role> lRoleList = lUserBLL.GetAllRoles();
lUser.RoleList = lUserMapper.MapRoleDBOListToRoleModelList(lRoleList);
return(View(lUser));
}
}
public List <User> GetToken(string userName, string password)
{
if (userName == "testUser" && password == "testPassword")
{
// Authentication Successful
UserDBO dbc = new UserDBO();
User user = new User();
//dbc.Delete(6);
//user.name = "Ionut";
//user.email = "[email protected]";
//user.password = "******";
//user.admin = "N";
//int w = dbc.Insert(user);
//Console.WriteLine(w);
//User a = new User();
//a = dbc.GetUserById(2);
//Console.WriteLine(a.ToString() + a.email) ;
//int q = dbc.Update(a, 7);
//Console.WriteLine(q);
List <User> list = dbc.SelectAll();
return(list);
}
else
{
// Authentication Failed
return(null);
}
}
public List <User> GetAllUsers()
{
UserDBO dbc = new UserDBO();
List <User> list = dbc.SelectAll();
return(list);
}
/// <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 calls a method in DAL layer to insert 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)
{
// Instantiate UserDAL object
UserDAL lUserDAL = new UserDAL();
// Insert into Database using UserDAL
int lResult = lUserDAL.CreateUser(iNewUser);
return(lResult);
}
/// <summary>
/// Description: This method calls a method in DAL layer to update 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)
{
// Instantiate UserDAL object
UserDAL lUserDAL = new UserDAL();
// Update the user
bool lResult = lUserDAL.UpdateUserByUserID(iUser);
return(lResult);
}
/// <summary>
/// Description: This method calls a method in DAL layer to check 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)
{
// Instantiate UserDAL object
UserDAL lUserDAL = new UserDAL();
// find the user with user login id and password
UserDBO lUser = lUserDAL.FindUserByLogInIDAndPassword(iUser);
return(lUser);
}
/// <summary>
/// Description: This method calls a method in DAL layer to find a user with specifc id
/// </summary>
/// <param name="iUserIDPK"></param>
/// <returns>User with the id</returns>
public UserDBO FindUserByID(int iUserIDPK)
{
// Instantiate UserDAL object
UserDAL lUserDAL = new UserDAL();
// get the user with specific id
UserDBO lUser = lUserDAL.FindUserByID(iUserIDPK);
return(lUser);
}
/// <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);
}
public ActionResult UserLogIn(User iUser)
{
// Instantiate Objects
UserMapper lUserMapper = new UserMapper();
UserBLL lUserBLL = new UserBLL();
// Map to UserDBO object
UserDBO lUserDBO = lUserMapper.MapUserToUserDBO(iUser);
// Check if user is activated and if loginID and password match
UserDBO lUser = lUserBLL.FindUserbyLogInIDAndPassword(lUserDBO);
// store UserIDPK, LogInID and Role into session
if (lUser != null && lUser.UserIsActive != 0)
{
// log in success
Session["AUTHUserIDPK"] = lUser.UserIDPK;
Session["AUTHUserName"] = lUser.UserLogInID;
Session["AUTHRole"] = lUser.UserRoleName;
// message on successful login
TempData["msg"] = $"<script>alert('Successfully Logged In. Welcome {lUser.UserLogInID}!');</script>";
// redirect to main page
return(RedirectToAction("Index", "Home"));
}
else
{
if (lUser == null)
{
// message for wrong login id or password
TempData["msg"] = $"<script>alert('Wrong LogIn ID or wrong password');</script>";
}
else
{
// message for not activated users
TempData["msg"] = $"<script>alert('Your account is not activated.');</script>";
}
}
return(View(iUser));
}
public ActionResult UserUpdate(User iUser)
{
// Instantiate objects
UserBLL lUserBLL = new UserBLL();
UserMapper lUserMapper = new UserMapper();
// check if every input is valid
if (ModelState.IsValid)
{
// Map Model.User to UserDBO
UserDBO lUserDBO = lUserMapper.MapUserToUserDBO(iUser);
// Update the user in database
bool lResult = lUserBLL.UpdateUserByUserID(lUserDBO);
if (lResult)
{
// message on success
TempData["msg"] = "<script>alert('Successfully Updated!')</script>";
// After success, redirect to Profile page
return(RedirectToAction("UserProfile", "User", new { id = iUser.UserIDPK }));
}
else
{
// message on failure
TempData["msg"] = "<script>alert('Failed to Update. Please try later')</script>";
}
}
else
{
// message on invalid model state
TempData["msg"] = "<script>alert('Please Fill all Required Information properly')</script>";
var errors = ModelState.Values.SelectMany(v => v.Errors);
}
// Get the list of roles
iUser.RoleList = lUserMapper.MapRoleDBOListToRoleModelList(lUserBLL.GetAllRoles());
return(View(iUser));
}
/// <summary>
/// Description: This method maps User Model object to User database object
/// </summary>
/// <param name="iNewUser">User object to be mapped</param>
/// <returns>User database object</returns>
public UserDBO MapUserToUserDBO(User iNewUser)
{
UserDBO oNewUser = new UserDBO();
// set values
oNewUser.UserIDPK = iNewUser.UserIDPK;
oNewUser.UserLogInID = iNewUser.UserLogInID;
oNewUser.UserPassword = iNewUser.UserPassword;
oNewUser.UserFirstName = iNewUser.UserFirstName;
oNewUser.UserLastName = iNewUser.UserLastName;
oNewUser.UserBirth = iNewUser.UserBirth;
oNewUser.UserEmail = iNewUser.UserEmail;
oNewUser.UserPhone = iNewUser.UserPhone;
oNewUser.UserIsActive = iNewUser.UserIsActive;
oNewUser.UserRoleIDFK = iNewUser.UserRoleIDFK;
oNewUser.UserRoleName = iNewUser.UserRoleName;
oNewUser.UserDateCreated = iNewUser.UserDateCreated;
oNewUser.UserDateModified = iNewUser.UserDateModified;
return(oNewUser);
}
/// <summary>
/// Description: This method maps User database object to User Model object
/// </summary>
/// <param name="iNewUser">User database object to be mapped</param>
/// <returns>User Model object</returns>
public User MapUserDBOToUser(UserDBO iNewUser)
{
User oUser = new User();
// set values
oUser.UserIDPK = iNewUser.UserIDPK;
oUser.UserLogInID = iNewUser.UserLogInID;
oUser.UserPassword = iNewUser.UserPassword;
oUser.UserFirstName = iNewUser.UserFirstName;
oUser.UserLastName = iNewUser.UserLastName;
oUser.UserBirth = iNewUser.UserBirth;
oUser.UserDateCreated = iNewUser.UserDateCreated;
oUser.UserDateModified = iNewUser.UserDateModified;
oUser.UserRoleIDFK = iNewUser.UserRoleIDFK;
oUser.UserRoleName = iNewUser.UserRoleName;
oUser.UserIsActive = iNewUser.UserIsActive;
// If info is not provided, set value to empty string
oUser.UserEmail = iNewUser.UserEmail == "Unknown" ? "" : iNewUser.UserEmail;
oUser.UserPhone = iNewUser.UserPhone == "Unknown" ? "" : iNewUser.UserPhone;
return(oUser);
}
public ActionResult UserRegister(User iUser)
{
// variable to store return value for query execution
int lResult = 0;
// class to set message for users
Common lCommon = new Common();
// check if every input is valid
if (ModelState.IsValid)
{
// Instantiate objects
UserBLL lUserBLL = new UserBLL();
UserMapper lUserMapper = new UserMapper();
// Map Models.User to DBObjects.User
UserDBO lUserDBO = lUserMapper.MapUserToUserDBO(iUser);
// check if there are duplicates for UserLogInID
int lIsDuplicate = lUserBLL.FindUserByUserLogInID(lUserDBO.UserLogInID);
// no duplicate
if (lIsDuplicate == 0)
{
// insert into the database and get return value of UserIDPK
lResult = lUserBLL.CreateUser(lUserDBO);
}
else
{
// there is another user using same LogIn id
iUser.type = -1;
iUser.message = "Please Use another User Name";
// return to the view with message
return(View(iUser));
}
// if the new user is inserted into the database with no duplicates
if (lResult > 0)
{
// To pre-populate login ID and password
TempData["UserLogInID"] = iUser.UserLogInID;
TempData["UserPassword"] = iUser.UserPassword;
// message on success
TempData["msg"] = "<script>alert('Registered Successfully');</script>";
return(RedirectToAction("UserLogIn", "User"));
}
else
{
// message on failure
iUser.type = -1;
iUser.message = "You are not registered yet. Please try later.";
}
}
else
{
iUser.type = -1;
iUser.message = "Please Fill all Required Information properly.";
}
return(View(iUser));
}
/// <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);
}
/// <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);
}
