/// <summary> /// Saves user into repository /// </summary> /// <param name="user"></param> public bool Save(User user) { using (var unitofwork = (IFileUnitOfWork)Training.Workshop.UnitOfWork.UnitOfWork.Start()) { unitofwork.Database.users.Add(user); unitofwork.Commit(); } return true; }
/// <summary> /// Creates a new user in the system /// </summary> /// <param name="username"></param> /// <param name="password"></param> /// <returns></returns> public virtual User Create(string username, string password) { var user = new User { Username = username, Password = password }; Data.Context.Current.RepositoryFactory.GetUserRepository() .Save(user); return user; }
/// <summary> /// Saves user into repository /// </summary> /// <param name="user"></param> public void Save(User user) { base.Save(user); }
/// <summary> /// return all users from database with permissions and roles /// </summary> /// <returns></returns> public List<User> GetAllUsers() { var listofusernames = new List<string>(); var listofusers = new List<User>(); //return all usernames using (var unitofwork = (ISQLUnitOfWork)Training.Workshop.UnitOfWork.UnitOfWork.Start()) { using (var command = unitofwork.Connection.CreateCommand()) { command.CommandText = "RetrieveAllUsernames"; command.CommandType = CommandType.StoredProcedure; var reader = command.ExecuteReader(); while (reader.Read()) { listofusernames.Add(reader["username"].ToString()); } } } //take all roles by usernames and construct list of users with userroles foreach (var username in listofusernames) { var user = new User() { Username = username, Roles = GetRolesandPermissionsbyUsername(username) }; listofusers.Add(user); } return listofusers; }
/// <summary> /// Get User with roles and permissions by one stored procedure /// </summary> /// <param name="username"></param> /// <returns></returns> public User RetrieveUser(string username) { //User Construction var user = new User(); using (var unitofwork = (ISQLUnitOfWork)Training.Workshop.UnitOfWork.UnitOfWork.Start()) { using (var command = unitofwork.Connection.CreateCommand()) { command.CommandText = "usp_GetUser"; command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("Username", username); using (IDataReader reader = command.ExecuteReader()) { //UserID,Username List<Tuple<string, string>> userlist = GetUser(reader); //to search roles and permissions by userID string userID = userlist[0].Item1; if (!reader.NextResult()) throw new InvalidOperationException("Cant execute SELECT ROLES"); //UserID,RoleID,RoleName List<Tuple<string, string, string>> roleslist = GetRoles(reader,userID); if (!reader.NextResult()) throw new InvalidOperationException("Cant execute SELECT PERMISSIONS"); // Get Permissions by username //UserID,RoleID,Permission List<Tuple<string, string, string>> permissionslist = GetPermissions(reader,userID); reader.Close(); //filled user role and permissions before return. user.Username = username; var rolelist=new List<Role>(); foreach (var roleelement in roleslist) { var role = new Role(); role.Name = roleelement.Item3.ToString(); var Permissionlist = new List<string>(); foreach (var permissionelement in permissionslist) { //if permission relates to role-adding permission to list belongs to role. if (roleelement.Item2.ToString() == permissionelement.Item2.ToString()) { Permissionlist.Add(permissionelement.Item3.ToString()); } } //adding all permissions to role role.Permissions = Permissionlist; //adding role to role list of user rolelist.Add(role); } user.Roles = rolelist; } } } return user; }
/// <summary> /// Returns all users with permissions and roles. /// New method works with 1 stored procedure. /// </summary> /// <returns></returns> public List<User> RetrieveAllUsers() { var listofusers = new List<User>(); using (var unitofwork = (ISQLUnitOfWork)Training.Workshop.UnitOfWork.UnitOfWork.Start()) { using (var command = unitofwork.Connection.CreateCommand()) { command.CommandText = "usp_GetAllUsers"; command.CommandType = CommandType.StoredProcedure; using (IDataReader reader = command.ExecuteReader()) { //UserID,Username List<Tuple<string, string>> userlist = GetUser(reader); if (!reader.NextResult()) throw new InvalidOperationException("Cant execute SELECT ROLES"); //UserID,RoleID,RoleName List<Tuple<string, string, string>> roleslist = GetRoles(reader); if (!reader.NextResult()) throw new InvalidOperationException("Cant execute SELECT PERMISSIONS"); // Get Permissions by username List<Tuple<string, string, string>> permissionslist = GetPermissions(reader); reader.Close(); //filled user fields before return. foreach (var userelement in userlist) { var newuser = new User(); var userid = userelement.Item1; newuser.Username = userelement.Item2.ToString(); var rolelist = new List<Role>(); foreach (var roleelement in roleslist) { var role = new Role(); if (userid == roleelement.Item1) { role.Name = roleelement.Item3; var Permissionlist = new List<string>(); foreach (var permissionelement in permissionslist) { //if permission relates to role-adding permission to list belongs to role. if (roleelement.Item2 == permissionelement.Item2) { { Permissionlist.Add(permissionelement.Item3.ToString()); } } } //adding all permissions to role role.Permissions = Permissionlist; //adding role to role list of user rolelist.Add(role); } } newuser.Roles = rolelist; listofusers.Add(newuser); } } } } return listofusers; }
/// <summary> /// Search and return user if he exist in database,with all his roles and permissions. /// </summary> /// <param name="username"></param> /// <param name="password"></param> public User GetUser(string username, string password) { var user = new User(); string saltfromdatabase; string enteredPasswordwithSaltHash; //take salt from database by username and generate SHA hash from entered password and salt using (var unitofwork = (ISQLUnitOfWork)Training.Workshop.UnitOfWork.UnitOfWork.Start()) { using (var command = unitofwork.Connection.CreateCommand()) { var salt = new SqlParameter("salt", SqlDbType.VarChar); //add Value to search by username command.CommandText = "TakeSaltbyUserName"; command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("username",username); //add values to output data from database salt.Direction = ParameterDirection.Output; salt.Size = 15; command.Parameters.Add(salt); command.ExecuteNonQuery(); saltfromdatabase = command.Parameters["salt"].Value.ToString(); enteredPasswordwithSaltHash = GenerateSHAHashFromPasswordWithSalt(password, saltfromdatabase); //clear parameters for new procedure command.Parameters.Clear(); command.CommandText = "CheckPasswordAndReturnUsername"; command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("username", username); command.Parameters.AddWithValue("enteredpassword", enteredPasswordwithSaltHash); var correctusername = new SqlParameter("correctusername", SqlDbType.VarChar); correctusername.Direction = ParameterDirection.Output; correctusername.Size = 50; command.Parameters.Add(correctusername); command.ExecuteNonQuery(); //Construct User user.Username = command.Parameters["correctusername"].Value.ToString(); } } //return filled user if username and password is correct //return empty user if user does not exist in database user.Roles = GetRolesandPermissionsbyUsername(user.Username); return user; }