/// <summary>
/// Returns a list of BE_User instances given a user name
/// </summary>
/// <param name="userName">User's name</param>
/// <returns></returns>
public List <BE_User> GeBE_UserByName(string userName)
{
List <BE_User> users = new List <BE_User>();
string commandText = "Select * from Users where UserName = @name";
Dictionary <string, object> parameters = new Dictionary <string, object>()
{
{ "@name", userName }
};
var rows = _database.Query(commandText, parameters);
foreach (var row in rows)
{
BE_User user = (BE_User)Activator.CreateInstance(typeof(BE_User));
user.id = row["Id"];
user.userName = row["UserName"];
user.passwordHash = string.IsNullOrEmpty(row["PasswordHash"]) ? null : row["PasswordHash"];
user.securityStamp = string.IsNullOrEmpty(row["SecurityStamp"]) ? null : row["SecurityStamp"];
user.email = string.IsNullOrEmpty(row["Email"]) ? null : row["Email"];
user.emailConfirmed = row["EmailConfirmed"] == "1" ? true : false;
user.phoneNumber = string.IsNullOrEmpty(row["PhoneNumber"]) ? null : row["PhoneNumber"];
user.phoneNumberConfirmed = row["PhoneNumberConfirmed"] == "1" ? true : false;
user.lockoutEnabled = row["LockoutEnabled"] == "1" ? true : false;
user.twoFactorEnabled = row["TwoFactorEnabled"] == "1" ? true : false;
user.lockoutEnd = string.IsNullOrEmpty(row["LockoutEndDateUtc"]) ? DateTime.Now : DateTime.Parse(row["LockoutEndDateUtc"]);
user.lockoutEndFormat = user.lockoutEnd.ToShortDateString();
user.accessFailedCount = string.IsNullOrEmpty(row["AccessFailedCount"]) ? 0 : int.Parse(row["AccessFailedCount"]);
users.Add(user);
}
return(users);
}
/// <summary>
/// Returns an BE_User given the user's id
/// </summary>
/// <param name="userId">The user's id</param>
/// <returns></returns>
public BE_User GeBE_UserById(string userId)
{
BE_User user = null;
string commandText = "Select * from Users where Id = @id";
Dictionary <string, object> parameters = new Dictionary <string, object>()
{
{ "@id", userId }
};
var rows = _database.Query(commandText, parameters);
if (rows != null && rows.Count == 1)
{
var row = rows[0];
user = (BE_User)Activator.CreateInstance(typeof(BE_User));
user.id = row["Id"];
user.userName = row["UserName"];
user.passwordHash = string.IsNullOrEmpty(row["PasswordHash"]) ? null : row["PasswordHash"];
user.securityStamp = string.IsNullOrEmpty(row["SecurityStamp"]) ? null : row["SecurityStamp"];
user.email = string.IsNullOrEmpty(row["Email"]) ? null : row["Email"];
user.emailConfirmed = row["EmailConfirmed"] == "1" ? true : false;
user.phoneNumber = string.IsNullOrEmpty(row["PhoneNumber"]) ? null : row["PhoneNumber"];
user.phoneNumberConfirmed = row["PhoneNumberConfirmed"] == "1" ? true : false;
user.lockoutEnabled = row["LockoutEnabled"] == "1" ? true : false;
user.lockoutEnd = string.IsNullOrEmpty(row["LockoutEndDateUtc"]) ? DateTime.Now : DateTime.Parse(row["LockoutEndDateUtc"]);
user.lockoutEndFormat = user.lockoutEnd.ToShortDateString();
user.accessFailedCount = string.IsNullOrEmpty(row["AccessFailedCount"]) ? 0 : int.Parse(row["AccessFailedCount"]);
}
return(user);
}
public List <BE_User> getUserGeneral(BE_UserFilter bE_UserFilter)
{
BE_User _BeUser = null;
List <BE_User> _lstUser = new List <BE_User>();
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("_userId", bE_UserFilter.id);
parameters.Add("_userEmailList", bE_UserFilter.UserEmailList);
parameters.Add("_userNameList", bE_UserFilter.UserNameList);
parameters.Add("_searchFilter", bE_UserFilter.SearchFilter);
var rows = _database.QuerySP("sp_getUserGeneral", parameters);
foreach (var row in rows)
{
_BeUser = new BE_User();
_BeUser.id = row["Id"];
_BeUser.accessFailedCount = string.IsNullOrEmpty(row["AccessFailedCount"]) ? 0 : int.Parse(row["AccessFailedCount"]);
_BeUser.concurrencyStamp = row["ConcurrencyStamp"];
_BeUser.configuration = row["Configuration"];
_BeUser.email = row["Email"];
_BeUser.emailConfirmed = string.IsNullOrEmpty(row["EmailConfirmed"]) ? false : row["EmailConfirmed"].Equals("1") ? true : false;
_BeUser.firstName = row["FirstName"];
_BeUser.isEnabled = string.IsNullOrEmpty(row["IsEnabled"]) ? false : row["IsEnabled"].Equals("1") ? true : false;
_BeUser.lastName = row["LastName"];
_BeUser.lockoutEnabled = string.IsNullOrEmpty(row["LockoutEnabled"]) ? false : row["LockoutEnabled"].Equals("1") ? true : false;
_BeUser.lockoutEnd = string.IsNullOrEmpty(row["LockoutEnd"]) ? DateTime.MinValue : DateTime.Parse(row["LockoutEnd"]);
_BeUser.lockoutEndFormat = _BeUser.lockoutEnd.ToShortDateString();
_BeUser.normalizedEmail = row["NormalizedEmail"];
_BeUser.normalizedUserName = row["NormalizedUserName"];
_BeUser.passwordHash = row["PasswordHash"];
_BeUser.phoneNumber = row["PhoneNumber"];
_BeUser.phoneNumberConfirmed = string.IsNullOrEmpty(row["PhoneNumberConfirmed"]) ? false : row["PhoneNumberConfirmed"].Equals("1") ? true : false;
_BeUser.twoFactorEnabled = string.IsNullOrEmpty(row["TwoFactorEnabled"]) ? false : row["TwoFactorEnabled"].Equals("1") ? true : false;
_BeUser.userName = row["UserName"];
_lstUser.Add(_BeUser);
}
return(_lstUser);
}
/// <summary>
/// Inserts a new user in the Users table
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public int Insert(BE_User user)
{
string commandText = @"Insert into Users (UserName, Id, PasswordHash, SecurityStamp,Email,EmailConfirmed,PhoneNumber,PhoneNumberConfirmed, AccessFailedCount,LockoutEnabled,LockoutEndDateUtc,TwoFactorEnabled)
values (@name, @id, @pwdHash, @SecStamp,@email,@emailconfirmed,@phonenumber,@phonenumberconfirmed,@accesscount,@lockoutenabled,@lockoutenddate,@twofactorenabled)";
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("@name", user.userName);
parameters.Add("@id", user.id);
parameters.Add("@pwdHash", user.passwordHash);
parameters.Add("@SecStamp", user.securityStamp);
parameters.Add("@email", user.email);
parameters.Add("@emailconfirmed", user.emailConfirmed);
parameters.Add("@phonenumber", user.phoneNumber);
parameters.Add("@phonenumberconfirmed", user.phoneNumberConfirmed);
parameters.Add("@accesscount", user.accessFailedCount);
parameters.Add("@lockoutenabled", user.lockoutEnabled);
parameters.Add("@lockoutenddate", user.lockoutEnd);
parameters.Add("@twofactorenabled", user.twoFactorEnabled);
return(_database.Execute(commandText, parameters));
}
/// <summary>
/// Updates a user in the Users table
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public int Update(BE_User user)
{
string commandText = @"Update Users set UserName = @userName, PasswordHash = @pswHash, SecurityStamp = @secStamp,
Email=@email, EmailConfirmed=@emailconfirmed, PhoneNumber=@phonenumber, PhoneNumberConfirmed=@phonenumberconfirmed,
AccessFailedCount=@accesscount, LockoutEnabled=@lockoutenabled, LockoutEndDateUtc=@lockoutenddate, TwoFactorEnabled=@twofactorenabled
WHERE Id = @userId";
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("@userName", user.userName);
parameters.Add("@pswHash", user.passwordHash);
parameters.Add("@secStamp", user.securityStamp);
parameters.Add("@userId", user.id);
parameters.Add("@email", user.email);
parameters.Add("@emailconfirmed", user.emailConfirmed);
parameters.Add("@phonenumber", user.phoneNumber);
parameters.Add("@phonenumberconfirmed", user.phoneNumberConfirmed);
parameters.Add("@accesscount", user.accessFailedCount);
parameters.Add("@lockoutenabled", user.lockoutEnabled);
parameters.Add("@lockoutenddate", user.lockoutEnd);
parameters.Add("@twofactorenabled", user.twoFactorEnabled);
return(_database.Execute(commandText, parameters));
}
/// <summary>
/// Deletes a user from the Users table
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public int Delete(BE_User user)
{
return(Delete(user.id));
}