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