public ActionResult UpdateUser(UsersPO form)
{
ActionResult oresponse = null;
try
{
if (ModelState.IsValid)
{
UsersDO dataObject = UsersMap1.UsersPOtoDO(form);
_usersDataAccess.UpdateUser(dataObject);
oresponse = RedirectToAction("ViewUsers", "Users");
}
else
{
oresponse = View(form);
}
}
catch (Exception ex)
{
logger.Log("Fatal", ex.Source, ex.TargetSite.ToString(), ex.Message, ex.StackTrace);
}
finally
{ }
return(oresponse);
}
public ActionResult Users(int UserID)
{
ActionResult response;
try
{
//mapping all the data to the view page
UsersDO userDO = _UsersDAO.ViewUserByID(UserID);
UsersPO userDetails = Mapper.UsersDOtoUsersPO(userDO);
response = View(userDetails);
}
//logging exceptions and redirecting to error page
catch (SqlException sqlEx)
{
Logger.SqlErrorLog(sqlEx);
response = View("Error");
}
catch (Exception ex)
{
Logger.ErrorLog(ex);
response = View("Error");
}
//returning response view
return(response);
}
public ActionResult UpdateUser(Int64 UserID)
{
UsersDO item = _usersDataAccess.ViewUserByID(UserID);
UsersPO UserToUpdate = UsersMap1.UsersDOtoUsersPO(item);
return(View(UserToUpdate));
}
public ActionResult UpdateUser(UsersPO form)
{
ActionResult response;
//checking modelstate
if (ModelState.IsValid)
{
try
{
//storing data to database
UsersDO UserDO = Mapper.UsersPOtoUsersDO(form);
_UsersDAO.UpdateUserInformation(UserDO);
//setting response page
response = RedirectToAction("AllUsers", "Users");
}
//logging errors and redirecting
catch (SqlException sqlEx)
{
Logger.SqlErrorLog(sqlEx);
response = View("Error");
}
catch (Exception ex)
{
Logger.ErrorLog(ex);
response = View("Error");
}
}
else
{
//returning to form view if model state is invalid
response = View(form);
}
//returning to user page
return(response);
}
public ActionResult UpdateUser(int UserID)
{
ActionResult response;
try
{
//retrieving data and displaying to user
UsersDO UserDO = _UsersDAO.ViewUserByID(UserID);
UsersPO UserPO = Mapper.UsersDOtoUsersPO(UserDO);
response = View(UserPO);
}
//logging errors and redirecting
catch (SqlException sqlEx)
{
Logger.SqlErrorLog(sqlEx);
response = View("Error");
}
catch (Exception ex)
{
Logger.ErrorLog(ex);
response = View("Error");
}
//return view
return(response);
}
public void AddUser(UsersDO newUser)
{
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
try
{
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("ADD_USER", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
connectionToSql.Open();
storedProcedure.Parameters.AddWithValue("@FirstName", newUser.FirstName);
storedProcedure.Parameters.AddWithValue("@LastName", newUser.LastName);
storedProcedure.Parameters.AddWithValue("@Email", newUser.Email);
storedProcedure.Parameters.AddWithValue("@City", newUser.City);
storedProcedure.Parameters.AddWithValue("@UserName", newUser.UserName);
storedProcedure.Parameters.AddWithValue("@Password", newUser.Password);
storedProcedure.Parameters.AddWithValue("@RoleID", newUser.RoleID);
storedProcedure.ExecuteNonQuery();
}
catch (Exception ex)
{
LogFile.DataFile(ex: ex);
}
finally
{
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
}
public UsersDO ViewByUserName(string userName)
{
UsersDO user = new UsersDO();
SqlConnection connection = null;
SqlDataAdapter adapter = null;
DataTable table = new DataTable();
SqlCommand command = null;
try
{
connection = new SqlConnection(_ConnectionString);
command = new SqlCommand("View_By_UserName", connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
command.Parameters.AddWithValue("@UserName", userName);
adapter = new SqlDataAdapter(command);
adapter.Fill(table);
user = UsersMap2.DataTableToList(table).FirstOrDefault();
}
catch (Exception ex)
{
logger.Log("Fatal", ex.Source, ex.TargetSite.ToString(), ex.Message, ex.StackTrace);
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
return(user);
}
public static UsersDO RowToItem(DataRow isource)
{
UsersDO to = new UsersDO();
to.UserID = (Int64)isource["UserID"];
if (isource["UserName"] != DBNull.Value)
{
to.UserName = isource["UserName"].ToString();
}
if (isource["Password"] != DBNull.Value)
{
to.Password = isource["Password"].ToString();
}
if (isource["LastName"] != DBNull.Value)
{
to.LastName = isource["LastName"].ToString();
}
if (isource["FirstName"] != DBNull.Value)
{
to.FirstName = isource["FirstName"].ToString();
}
if (isource["Email"] != DBNull.Value)
{
to.Email = isource["Email"].ToString();
}
if (isource["Address"] != DBNull.Value)
{
to.Address = isource["Address"].ToString();
}
if (isource["City"] != DBNull.Value)
{
to.City = isource["City"].ToString();
}
if (isource["StateProvidence"] != DBNull.Value)
{
to.StateProvidence = isource["StateProvidence"].ToString();
}
if (isource["PostalCode"] != DBNull.Value)
{
to.PostalCode = (int)isource["PostalCode"];
}
if (isource["Country"] != DBNull.Value)
{
to.Country = isource["Country"].ToString();
}
if (isource.Table.Columns.Contains("RoleID") && isource["RoleID"] != DBNull.Value)
{
to.RoleID = (int)isource["RoleID"];
}
if (isource.Table.Columns.Contains("RoleName") && isource["RoleName"] != DBNull.Value)
{
to.RoleName = isource["RoleName"].ToString();
}
return(to);
}
//mapping back to Data Access layer
public static UsersDO UsersPOtoUsersDO(UsersPO from)
{
//mapping each individual attribute
UsersDO to = new UsersDO();
to.UserID = from.UserID;
to.Username = from.Username;
to.Email = from.Email;
to.Password = from.Password;
to.ESOname = from.ESOname;
to.RoleID = from.RoleID;
to.Server = from.Server;
return(to);
}
public ActionResult Login(LoginPO form)
{
ActionResult response;
//checks if model is valid
if (ModelState.IsValid)
{
try
{
//checking if valid user
UsersDO user = _UsersDAO.ViewUserByUsername(form.Username);
//checking if password is correct
if (!(user.UserID == 0) && form.Password.Equals(user.Password))
{
//setting session data
Session["UserID"] = user.UserID;
Session["Username"] = user.Username;
Session["RoleID"] = user.RoleID;
//setting response to redirect to home page
response = RedirectToAction("Index", "Home");
}
else
{
//informing user that information was incorrect and returning to form view
ModelState.AddModelError("Password", "Username or password was incorrect");
response = View(form);
}
}
//logging errors
catch (SqlException sqlEx)
{
Logger.SqlErrorLog(sqlEx);
response = View("Error");
}
catch (Exception ex)
{
Logger.ErrorLog(ex);
response = View("Error");
}
}
else
{
//returning to form view if model state is invalid
response = View(form);
}
//returning response view
return(response);
}
public static UsersBO UsersDOtoBO(UsersDO from)
{
UsersBO to = new UsersBO();
to.UserID = from.UserID;
to.FirstName = from.FirstName;
to.LastName = from.LastName;
to.Email = from.Email;
to.City = from.City;
to.UserName = from.UserName;
to.Password = from.Password;
to.RoleID = from.RoleID;
to.RoleName = from.RoleName;
return(to);
}
public static UsersDO RegisterPOtoUsersDO(RegisterPO from)
{
UsersDO to = new UsersDO();
to.UserName = from.UserName;
to.Password = from.Password;
to.LastName = from.LastName;
to.FirstName = from.FirstName;
to.Email = from.Email;
to.Address = from.Address;
to.City = from.City;
to.StateProvidence = from.StateProvidence;
to.PostalCode = from.PostalCode;
to.Country = from.Country;
to.RoleID = from.RoleID;
return(to);
}
//Getting User data from the database
public static UsersDO ReaderToUser(SqlDataReader from)
{
UsersDO to = new UsersDO();
//mapping data
to.UserID = (int)from["UserID"];
to.Username = from["Username"] as string;
to.Email = from["email"] as string;
to.Password = from["Password"] as string;
to.ESOname = from["ESOname"] as string;
to.RoleID = (byte)from["RoleID"];
to.Server = from["Server"] as string;
//returning the User Data
return(to);
}
public UsersDO ReadUser(string UserName)
{
UsersDO user = null;
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
try
{
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("READ_USER_BY_USERNAME", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
storedProcedure.Parameters.AddWithValue("@userName", UserName);
connectionToSql.Open();
SqlDataReader reader = storedProcedure.ExecuteReader();
reader.Read();
user = new UsersDO(
(long)reader["UserID"],
reader["FirstName"] as string,
reader["LastName"] as string,
reader["Email"] as string,
reader["City"] as string,
reader["UserName"] as string,
reader["Password"] as string,
(byte)reader["RoleID"],
reader["Description"] as string
);
reader.Close();
}
catch (Exception ex)
{
LogFile.DataFile(ex: ex);
}
finally
{
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
;
}
return(user);
}
public List <UsersDO> ReadALLUsers()
{
List <UsersDO> usersList = new List <UsersDO>();
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
try
{
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("READ_ALL_USERS", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
connectionToSql.Open();
SqlDataReader sqlDataReader = storedProcedure.ExecuteReader();
while (sqlDataReader.Read())
{
UsersDO user = new UsersDO();
usersList.Add(user);
user.UserID = (long)sqlDataReader["UserID"];
user.FirstName = sqlDataReader["FirstName"] as string;
user.LastName = sqlDataReader["LastName"] as string;
user.Email = sqlDataReader["Email"] as string;
user.City = sqlDataReader["City"] as string;
user.UserName = sqlDataReader["UserName"] as string;
user.Password = sqlDataReader["Password"] as string;
user.RoleID = (byte)sqlDataReader["RoleID"];
user.RoleName = sqlDataReader["Description"] as string;
}
}
catch (Exception ex)
{
LogFile.DataFile(ex: ex);
throw ex;
}
finally
{
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
return(usersList);
}
public UsersDO ReadUserByID(long userID)
{
UsersDO user = new UsersDO();
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
try
{
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("READ_USER_BY_ID", connectionToSql);
storedProcedure.Parameters.AddWithValue("@UserID", userID);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
connectionToSql.Open();
SqlDataReader sqlDataReader = storedProcedure.ExecuteReader();
sqlDataReader.Read();
user.UserID = (long)sqlDataReader["UserID"];
user.FirstName = sqlDataReader["FirstName"] as string;
user.LastName = sqlDataReader["LastName"] as string;
user.Email = sqlDataReader["Email"] as string;
user.City = sqlDataReader["City"] as string;
user.UserName = sqlDataReader["UserName"] as string;
user.Password = sqlDataReader["Password"] as string;
user.RoleID = (byte)sqlDataReader["RoleID"];
sqlDataReader.Close();
}
catch (Exception ex)
{
LogFile.DataFile(ex: ex);
throw ex;
}
finally
{
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
return(user);
}
public ActionResult Login(LoginPO form)
{
ActionResult response = null;
if (Session["RoleID"] == null)
{
if (ModelState.IsValid)
{
try
{
UsersDO registeredUser = _dataAccess.ReadUser(form.UserName);
if (form.UserName == registeredUser.UserName.Trim() && form.Password == registeredUser.Password.Trim())
{
Session["Users"] = registeredUser.UserName;
Session["RoleID"] = registeredUser.RoleID;
Session.Timeout = 5;
response = RedirectToAction("Index", "Home");
}
else
{
response = View();
}
}
catch (Exception ex)
{
LogFile.DataFile(ex: ex);
}
finally
{
}
}
else
{
response = View(form);
}
}
else
{
TempData["Statement"] = "Opps! Looks like you're already logged in!";
response = RedirectToAction("Index", "Home");
}
return(response);
}
//Retrieving User Data from the Database
public List <UsersDO> ViewAllUsers()
{
//Creating a list of users
List <UsersDO> usersList = new List <UsersDO>();
try
{
//defining commands to access the database
using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString))
using (SqlCommand viewUserTable = new SqlCommand("USERS_SELECT_ALL", sqlConnection))
{
//giving up after 60 seconds
viewUserTable.CommandType = CommandType.StoredProcedure;
viewUserTable.CommandTimeout = 60;
//Reading the data and using Mapper to store it
sqlConnection.Open();
using (SqlDataReader reader = viewUserTable.ExecuteReader())
{
while (reader.Read())
{
//creating a new user object for each entry and adding them to a list
UsersDO user = MapperDAL.ReaderToUser(reader);
usersList.Add(user);
}
}
sqlConnection.Close();
}
}
//logging errors
catch (SqlException sqlEx)
{
LoggerDAL.SqlErrorLog(sqlEx);
throw sqlEx;
}
catch (Exception ex)
{
LoggerDAL.ErrorLog(ex);
throw ex;
}
//returning list of all users
return(usersList);
}
//retrieve a single user entry from database
public UsersDO ViewUserByID(int UserID)
{
UsersDO userData = new UsersDO();
try
{
//defining commands to access the database
using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString))
using (SqlCommand viewByID = new SqlCommand("USERS_SELECT_BY_ID", sqlConnection))
{
//give up after 60 seconds
viewByID.CommandType = CommandType.StoredProcedure;
viewByID.CommandTimeout = 60;
//inserting the UserID to sort through entries
viewByID.Parameters.AddWithValue("UserID", UserID);
//reading the data and using Mapper to store it
sqlConnection.Open();
using (SqlDataReader reader = viewByID.ExecuteReader())
{
if (reader.Read())
{
userData = MapperDAL.ReaderToUser(reader);
}
}
}
}
//logging errors
catch (SqlException sqlEx)
{
LoggerDAL.SqlErrorLog(sqlEx);
throw sqlEx;
}
catch (Exception ex)
{
LoggerDAL.ErrorLog(ex);
throw ex;
}
//returning information
return(userData);
}
//Creating a new User entry
public int CreateNewUserEntry(UsersDO userInfo)
{
int rowsAffected = 0;
try
{
//defining commands
using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString))
using (SqlCommand createUser = new SqlCommand("USERS_CREATE_NEW", sqlConnection))
{
//timing out after 60 seconds
createUser.CommandType = CommandType.StoredProcedure;
createUser.CommandTimeout = 60;
//inserting information
createUser.Parameters.AddWithValue("Username", userInfo.Username);
createUser.Parameters.AddWithValue("Email", userInfo.Email);
createUser.Parameters.AddWithValue("Password", userInfo.Password);
createUser.Parameters.AddWithValue("ESOname", userInfo.ESOname);
createUser.Parameters.AddWithValue("RoleID", userInfo.RoleID);
createUser.Parameters.AddWithValue("Server", userInfo.Server);
//Saving information to database
sqlConnection.Open();
rowsAffected = createUser.ExecuteNonQuery();
}
}
//logging errors
catch (SqlException sqlEx)
{
LoggerDAL.SqlErrorLog(sqlEx);
throw sqlEx;
}
catch (Exception ex)
{
LoggerDAL.ErrorLog(ex);
throw ex;
}
return(rowsAffected);
}
public void UpdateUser(UsersDO user)
{
SqlConnection connection = null;
SqlCommand updateUserRowCommand = null;
try
{
connection = new SqlConnection(_ConnectionString);
updateUserRowCommand = new SqlCommand("UPDATE_USER", connection);
updateUserRowCommand.CommandType = CommandType.StoredProcedure;
connection.Open();
updateUserRowCommand.Parameters.AddWithValue("@UserID", user.UserID);
updateUserRowCommand.Parameters.AddWithValue("@UserName", user.UserName);
updateUserRowCommand.Parameters.AddWithValue("@Password", user.Password);
updateUserRowCommand.Parameters.AddWithValue("@LastName", user.LastName);
updateUserRowCommand.Parameters.AddWithValue("@FirstName", user.FirstName);
updateUserRowCommand.Parameters.AddWithValue("@Email", user.Email);
updateUserRowCommand.Parameters.AddWithValue("@Address", user.Address);
updateUserRowCommand.Parameters.AddWithValue("@City", user.City);
updateUserRowCommand.Parameters.AddWithValue("@StateProvidence", user.StateProvidence);
updateUserRowCommand.Parameters.AddWithValue("@PostalCode", user.PostalCode);
updateUserRowCommand.Parameters.AddWithValue("@Country", user.Country);
updateUserRowCommand.Parameters.AddWithValue("@RoleID", user.RoleID);
updateUserRowCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
logger.Log("Fatal", ex.Source, ex.TargetSite.ToString(), ex.Message, ex.StackTrace);
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
}
//Updating an existing user
public void UpdateUserInformation(UsersDO userInfo)
{
try
{
//defining some commands
using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString))
using (SqlCommand updateUser = new SqlCommand("USERS_UPDATE_ACCOUNT", sqlConnection))
{
//timing out after 60 seconds
updateUser.CommandType = CommandType.StoredProcedure;
updateUser.CommandTimeout = 60;
//inserting information
updateUser.Parameters.AddWithValue("UserID", userInfo.UserID);
updateUser.Parameters.AddWithValue("Username", userInfo.Username);
updateUser.Parameters.AddWithValue("Email", userInfo.Email);
updateUser.Parameters.AddWithValue("RoleID", userInfo.RoleID);
updateUser.Parameters.AddWithValue("Password", userInfo.Password);
updateUser.Parameters.AddWithValue("ESOname", userInfo.ESOname);
updateUser.Parameters.AddWithValue("Server", userInfo.Server);
//Saving information to database
sqlConnection.Open();
updateUser.ExecuteNonQuery();
}
}
//logging errors
catch (SqlException sqlEx)
{
LoggerDAL.SqlErrorLog(sqlEx);
throw sqlEx;
}
catch (Exception ex)
{
LoggerDAL.ErrorLog(ex);
throw ex;
}
}
public ActionResult Register(RegisterPO form)
{
ActionResult oResponse = null;
if (ModelState.IsValid)
{
if (Session["RoleID"] == null)
{
try
{
form.RoleID = 4;
UsersDO dataObject = RegisterMap.RegisterPOtoUsersDO(form);
_usersDataAccess.AddUser(dataObject);
oResponse = RedirectToAction("Index", "Home");
}
catch (Exception ex)
{
logger.Log("Fatal", ex.Source, ex.TargetSite.ToString(), ex.Message, ex.StackTrace);
oResponse = RedirectToAction("Index", "Home");
}
finally
{
//Do nothing
}
}
else
{
oResponse = View(form);
}
}
else
{
oResponse = RedirectToAction("Index", "Home");
}
return(oResponse);
}
public ActionResult CreateNewAccount(UsersPO form)
{
ActionResult response;
//checking modelstate
if (ModelState.IsValid)
{
try
{
//taking user input and mapping it to the database
form.RoleID = 3;
UsersDO newUser = Mapper.UsersPOtoUsersDO(form);
_UsersDAO.CreateNewUserEntry(newUser);
//setting response view
response = RedirectToAction("Login", "Users");
}
//logging errors
catch (SqlException sqlEx)
{
Logger.SqlErrorLog(sqlEx);
response = View("Error");
}
catch (Exception ex)
{
Logger.ErrorLog(ex);
response = View("Error");
}
}
else
{
//returning to form view if model state is invalid
response = View(form);
}
//return view page
return(response);
}
public void UpdateUser(UsersDO userDO)
{
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
try
{
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("UPDATE_USER", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
connectionToSql.Open();
storedProcedure.Parameters.AddWithValue("@UserID", userDO.UserID);
storedProcedure.Parameters.AddWithValue("@FirstName", userDO.FirstName);
storedProcedure.Parameters.AddWithValue("@LastName", userDO.LastName);
storedProcedure.Parameters.AddWithValue("@Email", userDO.Email);
storedProcedure.Parameters.AddWithValue("@City", userDO.City);
storedProcedure.Parameters.AddWithValue("@UserName", userDO.UserName);
storedProcedure.Parameters.AddWithValue("@RoleID", userDO.RoleID);
storedProcedure.ExecuteNonQuery();
}
catch (Exception ex)
{
LogFile.DataFile(ex: ex);
throw ex;
}
finally
{
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
}
