public override List <IDictionary <string, object> > QueryDynamicTable(string tableName, DynamicTableFilter filter, ICollection <DynamicTableSort> sorts, out int totalCount, string tableAlias = null, DynamicQueryCallbackProvider queryCallbacks = null, int?hitsPerPage = null, int?page = null)
{
var desc = DescribeTable(tableName, true, out _);
using (Facade.UseConnection(out DbCommand cmd))
{
var colFx = new Func <List <TableColumnDefinition>, string, string, TableColumnResolveCallback, string>((cols, colName, alias, cb) =>
{
var retVal = BrowseColumns(cols, colName, alias, cb, out var tmp);
if (tmp is AliasQualifiedColumn ali)
{
retVal = ali.FullQualifiedName;
}
return(retVal);
});
var rawQuery = filter != null?BuildWhereClause(desc, filter, cmd, 0, tableAlias, queryCallbacks) : null;
var rawOrdering = (sorts != null && sorts.Count != 0) ? string.Join(", ", from t in sorts select $"{colFx(desc, t.ColumnName, tableAlias, queryCallbacks?.FQColumnQuery)} {t.SortOrder}") : null;
var rawCols = string.Join(", ", from t in desc select SyntaxProvider.FullQualifyColumn(tableAlias, t.ColumnName));
var finalQuery = $@"select {rawCols}{(!string.IsNullOrEmpty(queryCallbacks?.CustomQuerySelection) ? $", {queryCallbacks?.CustomQuerySelection}" : "")}, [$$totalRecordCount$$] = count(*) over() from [{tableName}] {(!string.IsNullOrEmpty(tableAlias) ? $"[{tableAlias}]" : "")} {queryCallbacks?.CustomQueryTablePart} {(!string.IsNullOrEmpty(rawQuery) ? $"where {rawQuery}" : "")} {(!string.IsNullOrEmpty(rawOrdering) ? $"order by {rawOrdering}" : "")}
{(hitsPerPage != null && page != null ? $@"offset {hitsPerPage * (page - 1)} rows
fetch next {hitsPerPage} rows only" : "")}";
LogEnvironment.LogDebugEvent(null, finalQuery, (int)LogSeverity.Report, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter");
cmd.CommandText = finalQuery;
var retVal = cmd.ExecuteReader().ToDictionaries(true).ToList();
totalCount = 0;
if (retVal.Count != 0)
{
totalCount = Convert.ToInt32(retVal[0]["$$totalRecordCount$$"]);
}
return(retVal);
}
}
private string BuildWhereClause(ICollection <TableColumnDefinition> desc, DynamicTableFilter filter, DbCommand cmd, int paramIdSeed = 0, string tableAlias = null, DynamicQueryCallbackProvider queryCallbacks = null)
{
var paramId = paramIdSeed;
return(filter?.BuildQueryPart((string name, out TableColumnDefinition def) => BrowseColumns(desc, name, tableAlias, queryCallbacks?.FQColumnQuery, out def), o =>
{
var par = cmd.CreateParameter();
cmd.Parameters.Add(par);
par.ParameterName = $"@qpm{paramId++}";
par.Value = o;
return par.ParameterName;
}, SyntaxProvider));
}
protected DataSourceResult DataResultFor(string tableName, DynamicTableFilter filter, List <DynamicTableSort> sort, string alias, DynamicQueryCallbackProvider callbacks, int?hitsPerPage, int?page, IEnumerable defaultResult = null)
{
var tmp = dataSource.QueryDynamicTable(tableName, filter, sort, out var totalCount, alias, callbacks, hitsPerPage, page);
DataSourceResult result = new DataSourceResult();
result.Data = defaultResult ?? tmp;
result.Total = totalCount;
return(result);
}
