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); }