public PortalLoginDto UpdatePortalLogin(string id, string username, string password, IEnumerable <string> roleIds, bool isPasswordToChange, bool isPasswordChanged, bool isLocked)
{
var columnsCommand = @"
SELECT l.Id, l.Username, l.PasswordHash, l.IsPasswordToChange, l.CreatorId, l.CreationTime, l.EditorId, l.LastEditTime, l.IsValid,
STUFF((
SELECT ','+r.Name
FROM PortalRoles r INNER JOIN PortalLoginRoles lr on lr.RoleId=r.Id
WHERE lr.LoginId=l.Id
FOR XML PATH('')
), 1, 1, '') as Roles";
var queryCommand = @"
FROM [PortalLogins] l
WHERE l.Id=@Id";
using (var connection = GetConnection())
{
connection.Open();
using (var transcation = connection.BeginTransaction())
{
try
{
PortalLoginDto dto = connection.Query <PortalLoginDto>($@"{columnsCommand} {queryCommand}", new { Id = id }, transcation).FirstOrDefault();
var login = new PortalLogin()
{
Id = id,
Username = username,
PasswordHash = isPasswordChanged ? _passwordHasher.HashPassword(password) : dto.PasswordHash,
IsPasswordToChange = dto.IsPasswordToChange ? dto.IsPasswordToChange : isPasswordToChange,
IsLocked = isLocked,
CreationTime = DateTime.UtcNow,
CreatorId = CurrentUserId,
EditorId = CurrentUserId,
LastEditTime = DateTime.UtcNow,
IsValid = true,
};
connection.Update(login, transcation);
var existingRoles = connection.Query <PortalLoginRole>(@"SELECT * FROM PortalLoginRoles WHERE LoginId=@Id",
new { Id = id }, transcation);
var roles = existingRoles.Select(r => r.RoleId);
var toAdd = roleIds.Where(r => !roles.Contains(r));
var toRemove = existingRoles.Where(r => !roleIds.Contains(r.RoleId));
foreach (var r in toAdd)
{
var role = new PortalLoginRole()
{
LoginId = login.Id,
RoleId = r
};
connection.Insert(role, transcation);
}
foreach (var r in toRemove)
{
connection.Delete(r, transcation);
}
dto = connection.Query <PortalLoginDto>($@"{columnsCommand} {queryCommand}", new { Id = id }, transcation).FirstOrDefault();
transcation.Commit();
return(dto);
}
catch (Exception e)
{
_logger.LogError(e.Message);
transcation.Rollback();
return(null);
}
}
}
}
public PortalLoginDto CreatePortalLogin(string username, string password, IEnumerable <string> roleIds, bool isPasswordToChange)
{
var login = new PortalLogin()
{
Id = IdHelper.NewId(),
Username = username,
PasswordHash = _passwordHasher.HashPassword(password),
IsPasswordToChange = isPasswordToChange,
IsLocked = false,
CreationTime = DateTime.UtcNow,
CreatorId = CurrentUserId,
EditorId = null,
LastEditTime = null,
IsValid = true,
};
var columnsCommand = @"
SELECT l.Id, l.Username, l.PasswordHash, l.IsPasswordToChange, l.CreatorId, l.CreationTime, l.EditorId, l.LastEditTime, l.IsValid,
STUFF((
SELECT ','+r.Name
FROM PortalRoles r INNER JOIN PortalLoginRoles lr on lr.RoleId=r.Id
WHERE lr.LoginId=l.Id
FOR XML PATH('')
), 1, 1, '') as Roles";
var queryCommand = @"
FROM [PortalLogins] l
WHERE l.Id=@Id";
using (var connection = GetConnection())
{
connection.Open();
using (var transcation = connection.BeginTransaction())
{
try
{
connection.Insert(login, transcation);
foreach (var r in roleIds)
{
var role = new PortalLoginRole()
{
LoginId = login.Id,
RoleId = r
};
connection.Insert(role, transcation);
}
var dto = connection.QueryFirstOrDefault <PortalLoginDto>($@"{columnsCommand} {queryCommand}", new { Id = login.Id }, transcation);
transcation.Commit();
return(dto);
}
catch (Exception e)
{
_logger.LogError(e.Message);
transcation.Rollback();
return(null);
}
}
}
}