/// <summary>
/// Retrieve the authorizations definition list with pagination
/// </summary>
/// <param name="query">Query page parameters</param>
/// <returns>Paged authorizations data</returns>
public async Task <PagedData <dynamic> > GetAsync(PageQuery query)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
// Getting filters values
var sqlFilters = FiltersFactory.ConvertColumnFilters(query.Filters);
// Configuring filters with wildcards to match substrings also
var name = sqlFilters.FirstOrDefault(i => i.Name == "name")?.Value;
var nameValue = string.IsNullOrEmpty(name) ? string.Empty : $"%{name}%";
var count = await connection.QueryAsync <int>(
$"SELECT COUNT(*) FROM IdentityContext.def_authorizations " +
((!string.IsNullOrEmpty(nameValue)) ? "AND Name like @nameValue " : ""),
new
{
nameValue
}
);
var startRow = query.PageNumber > 0 ? (query.Size * query.PageNumber) + 1 : 1;
var endRow = startRow + query.Size;
var result = await connection.QueryAsync <dynamic>(
$@"SELECT * FROM ( " +
$"SELECT ROW_NUMBER() OVER ( ORDER BY Name ) AS RowNum, * FROM IdentityContext.def_authorizations " +
((!string.IsNullOrEmpty(nameValue)) ? "AND name like @nameValue " : "") +
") AS RowConstrainedResult WHERE RowNum >= @startRow AND RowNum < @endRow ORDER BY RowNum",
new
{
nameValue,
startRow,
endRow
}
);
// Update pagination data
query.TotalElements = count.First();
query.TotalPages = query.TotalElements / query.Size;
return(new PagedData <dynamic>
{
Data = result.AsList().Select(r => MapAuthResult(r)).ToList(),
Page = query
});
}
}
/// <summary>
/// Retrieve roles list with pagination
/// </summary>
/// <param name="query">Query page parameters</param>
/// <returns>Paged roles data</returns>
public async Task <PagedData <dynamic> > GetAsync(PageQuery query)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
// Getting filters values
var sqlFilters = FiltersFactory.ConvertColumnFilters(query.Filters);
var description = sqlFilters.FirstOrDefault(i => i.Name == "description")?.Value;
var descriptionValue = string.IsNullOrEmpty(description) ? string.Empty : $"%{description}%";
var count = await connection.QueryAsync <int>(
"SELECT COUNT(*) FROM IdentityContext.roles " +
((!string.IsNullOrEmpty(descriptionValue)) ? "WHERE Description LIKE @descriptionValue " : ""),
new
{
descriptionValue
}
);
var startRow = query.PageNumber > 0 ? (query.Size * query.PageNumber) + 1 : 1;
var endRow = startRow + query.Size;
var queryResult = await connection.QueryAsync <dynamic>(
"SELECT * FROM ( " +
"SELECT ROW_NUMBER() OVER ( ORDER BY Description ) AS RowNum, * FROM IdentityContext.roles " +
((!string.IsNullOrEmpty(descriptionValue)) ? "WHERE Description LIKE @descriptionValue " : "") +
") AS RowConstrainedResult WHERE RowNum >= @startRow AND RowNum < @endRow ORDER BY RowNum",
new
{
descriptionValue,
startRow,
endRow
});
// Update pagination data
query.TotalElements = count.First();
query.TotalPages = query.TotalElements / query.Size;
return(new PagedData <dynamic>
{
Data = queryResult.AsList().Select(item => MapRoleObject(item)).ToList(),
Page = query
});
}
}
/// <summary>
/// Retrieve users list with pagination
/// </summary>
/// <param name="query">Query page parameters</param>
/// <returns>Paged users data</returns>
public async Task <PagedData <dynamic> > GetAsync(PageQuery query)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
// Getting filters values
var sqlFilters = FiltersFactory.ConvertColumnFilters(query.Filters);
// Configuring filters with wildcards to match substrings also
var name = sqlFilters.FirstOrDefault(i => i.Name == "name")?.Value;
var nameValue = string.IsNullOrEmpty(name) ? string.Empty : $"%{name}%";
var surname = sqlFilters.FirstOrDefault(i => i.Name == "surname")?.Value;
var surnameValue = string.IsNullOrEmpty(surname) ? string.Empty : $"%{surname}%";
var username = sqlFilters.FirstOrDefault(i => i.Name == "username")?.Value;
var usernameValue = string.IsNullOrEmpty(username) ? string.Empty : $"%{username}%";
var email = sqlFilters.FirstOrDefault(i => i.Name == "email")?.Value;
var emailValue = string.IsNullOrEmpty(email) ? string.Empty : $"%{email}%";
// Get the total rows count
var count = await connection.QueryAsync <int>(
"SELECT COUNT(*) FROM IdentityContext.users WHERE Root = 0 AND Disabled = 0 " +
((!string.IsNullOrEmpty(nameValue)) ? "AND name LIKE @nameValue " : "") +
((!string.IsNullOrEmpty(surnameValue)) ? "AND surname LIKE @surnameValue " : "") +
((!string.IsNullOrEmpty(usernameValue)) ? "AND username LIKE @usernameValue " : "") +
((!string.IsNullOrEmpty(emailValue)) ? "AND email LIKE @emailValue " : ""),
new
{
nameValue,
surnameValue,
usernameValue,
emailValue,
});
var startRow = query.PageNumber > 0 ? (query.Size * query.PageNumber) + 1 : 1;
var endRow = startRow + query.Size;
// Get the paged users data
var queryResult = await connection.QueryAsync <dynamic>(
"SELECT * FROM ( " +
"SELECT ROW_NUMBER() OVER ( ORDER BY Email ) AS RowNum, * FROM IdentityContext.users WHERE Root = 0 AND Disabled = 0 " +
((!string.IsNullOrEmpty(nameValue)) ? "AND name LIKE @nameValue " : "") +
((!string.IsNullOrEmpty(surnameValue)) ? "AND surname LIKE @surnameValue " : "") +
((!string.IsNullOrEmpty(usernameValue)) ? "AND username LIKE @usernameValue " : "") +
((!string.IsNullOrEmpty(emailValue)) ? "AND email LIKE @emailValue " : "") +
") AS RowConstrainedResult WHERE RowNum >= @startRow AND RowNum < @endRow ORDER BY RowNum",
new
{
nameValue,
surnameValue,
usernameValue,
emailValue,
startRow,
endRow
});
// Update pagination data
query.TotalElements = count.First();
query.TotalPages = query.TotalElements / query.Size;
return(new PagedData <dynamic>
{
Data = queryResult.AsList().Select(item => MapUserObject(item)).ToList(),
Page = query
});
}
}
