public async Task <DocumentDetailList> ListDocumentsAsync(DocumentSearchParameters search, CancellationToken token)
{
var parameters = new DynamicParameters();
var(whereClause, offsetclause) = ExtractSearchClauses(search, parameters);
var queries = $@"SELECT COUNT_BIG(1) FROM [Documents] {whereClause};
SELECT [DocumentId], [FileName], [Title], [Description], [CreateDate], [FileSize], [DocumentType], [Owner]
FROM [dbo].[Documents]
{whereClause}
ORDER BY [DocumentId]
{offsetclause}
;";
await new SyncContextRemover();
await using var conn = await CreateAndOpenConnection(token);
var definition = CreateCommand(queries, parameters, token);
using var reader = await conn.QueryMultipleAsync(definition);
var total = await reader.ReadSingleOrDefaultAsync <long>();
var items = await reader.ReadAsync <DocumentDetailsDto>();
return(new DocumentDetailList
{
Total = (uint)total,
Documents = items.ToList()
});
}
// internal for testing
internal static (string where, string pagination) ExtractSearchClauses(DocumentSearchParameters search, DynamicParameters parameters)
{
if (!(search is {} p))
{
return(null, null);
}
string whereClause = null;
string offsetClause = null;
if (p.DocumentTypes?.Count > 0)
{
whereClause = "WHERE [DocumentType] IN @DocTypes";
parameters.Add("DocTypes", p.DocumentTypes);
}
if (p.Page.HasValue || p.PageSize.HasValue)
{
var take = p.PageSize.GetValueOrDefault();
if (take <= 0)
{
take = 1;
}
var page = p.Page.GetValueOrDefault() - 1;
if (page < 0)
{
page = 0;
}
parameters.Add("@Skip", page * take);
parameters.Add("@Take", take);
offsetClause = "OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY";
}
return(whereClause, offsetClause);
}