public PaginationResult <PortalLoginDto> GetPortalLogins(PortalLoginQuery query)
{
using (var connection = GetConnection())
{
connection.Open();
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 (@IsValid is NULL or @IsValid=l.IsValid) AND
(@Username is NULL or l.Username like @Username)";
var pagingCommand = query.Page == -1 ? "" : @"
ORDER BY l.Id DESC
OFFSET @Offset ROWS
FETCH NEXT @Size ROWS ONLY; ";
query.Username = string.IsNullOrWhiteSpace(query.Username) ?
null : $"%{query.Username.Trim()}%";
var data = connection.Query <PortalLoginDto>($@"{columnsCommand} {queryCommand} {pagingCommand}", query).ToList();
var count = connection.ExecuteScalar <long>($@"SELECT COUNT(l.Id) {queryCommand}", query);
var result = CombinePaginationResult(query, data, count);
return(result);
}
}
public IActionResult GetMorePortalLogins(PortalLoginQuery query)
{
var logins = _pr.GetPortalLogins(query);
return(PartialView("_portalLoginList", logins));
}
