/// <summary>
/// Returns the role Id given a role name
/// </summary>
/// <param name="roleName">Role's name</param>
/// <returns>Role's Id</returns>
public string GetRoleId(string roleName)
{
string roleId = null;
string commandText = "Select Id from Roles where Name = @name";
Dictionary <string, object> parameters = new Dictionary <string, object>()
{
{ "@name", roleName }
};
var result = _database.QueryValue(commandText, parameters);
if (result != null)
{
return(Convert.ToString(result));
}
return(roleId);
}
public List <TUser> GetUsers(int pageIndex, int pageSize, string userName, string userRole, out int totalCount)
{
var whereExp = new StringBuilder();
string commandText = @"SELECT COUNT(1) FROM Users u inner join UserRoles ur on u.Id = ur.UserId
inner join Roles r on ur.RoleId = r.Id where IsDel=@isDel{0}";
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("@isDel", 0);
if (!string.IsNullOrEmpty(userName))
{
whereExp.Append(" and UPPER(UserName) like @userName");
parameters.Add("@userName", "%" + userName.ToUpper() + "%");
}
if (!string.IsNullOrEmpty(userRole))
{
whereExp.Append(" and UPPER(r.Id) = @userRole");
parameters.Add("@userRole", userRole.ToUpper());
}
int.TryParse(_database.QueryValue(string.Format(commandText.ToString(), whereExp.ToString()), parameters).ToString(), out totalCount);
List <TUser> userList = new List <TUser>();
commandText =
@"select * from (select row_number() over (order by u.id) as row_num,u.* from Users U inner join UserRoles ur on u.Id = ur.UserId
inner join Roles r on ur.RoleId = r.Id where u.IsDel=@isDel{0} ) u where row_num between @PageIndex and @PageEnd";
parameters.Add("@PageIndex", pageIndex + 1);
parameters.Add("@PageEnd", pageSize + pageIndex);
var users = _database.Query(string.Format(commandText.ToString(), whereExp.ToString()), parameters);
foreach (var user in users)
{
var identityUser = (TUser)Activator.CreateInstance(typeof(TUser));
identityUser.Id = user["Id"];
identityUser.UserName = user["UserName"];
identityUser.Email = user["Email"];
identityUser.Department = user["Department"];
identityUser.PhoneNumber = user["PhoneNumber"];
identityUser.CreateTime = user["CreateTime"];
identityUser.Status = int.Parse(user["Status"]);
userList.Add(identityUser);
}
return(userList);
}