public Task <DataTable> GetDataFiles([FromBody] GetDataFilesPostData postData, CancellationToken cancellationToken)
{
return(Task.Run(() =>
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
const string QueryFormat =
"WITH cte AS " +
"( " +
" SELECT * " +
" FROM DataFile " +
" WHERE {0} " +
") " +
"SELECT DataFile.* " +
"FROM " +
"( " +
" SELECT " +
" ROW_NUMBER() OVER(ORDER BY {2}) AS RowNumber, " +
" DataFile.* " +
" FROM (SELECT DISTINCT FileGroupID ID FROM cte) FileGroup CROSS APPLY " +
" ( " +
" SELECT TOP 1 * " +
" FROM cte DataFile " +
" WHERE DataFile.FileGroupID = FileGroup.ID " +
" ORDER BY FileSize DESC, FilePath " +
" ) DataFile " +
") DataFile " +
"WHERE {1} " +
"ORDER BY RowNumber";
TableOperations <DataFile> dataFileTable = new TableOperations <DataFile>(connection);
RecordRestriction searchRestriction = dataFileTable.GetSearchRestriction(postData.filterString)
?? new RecordRestriction("1=1");
string searchClause = searchRestriction.FilterExpression;
int paramIndex = searchRestriction.Parameters.Length;
string pageClause = $"RowNumber BETWEEN {{{paramIndex}}} AND {{{paramIndex + 1}}}";
string sortOrder = postData.ascending ? "ASC" : "DESC";
string orderByClause = $"{postData.sortField} {sortOrder}";
string query = string.Format(QueryFormat, searchClause, pageClause, orderByClause);
int pageStart = (postData.page - 1) * postData.pageSize + 1;
int pageEnd = pageStart + postData.pageSize - 1;
object[] parameters = searchRestriction.Parameters
.Concat(new object[] { pageStart, pageEnd })
.ToArray();
return connection
.RetrieveData(query, parameters);
}
}, cancellationToken));
}
public Task <int> GetCount([FromBody] GetDataFilesPostData postData, CancellationToken cancellationToken)
{
return(Task.Run(() =>
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
const string QueryFormat =
"SELECT COUNT(*) " +
"FROM " +
"( " +
" SELECT DISTINCT FileGroupID " +
" FROM DataFile " +
" WHERE {0} " +
") DataFile";
TableOperations <DataFile> dataFileTable = new TableOperations <DataFile>(connection);
RecordRestriction restriction = dataFileTable.GetSearchRestriction(postData.filterString);
string query = string.Format(QueryFormat, restriction?.FilterExpression ?? "1=1");
return connection.ExecuteScalar <int>(query, restriction?.Parameters ?? new object[0]);
}
}, cancellationToken));
}
