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 BrowseColumns(ICollection <TableColumnDefinition> desc, string columnName, string tableAlias, TableColumnResolveCallback getCustomColumnDef, out TableColumnDefinition def)
{
def = desc.FirstOrDefault(n => n.ColumnName.Equals(columnName, StringComparison.OrdinalIgnoreCase));
if (def != null)
{
return(SyntaxProvider.FullQualifyColumn(tableAlias, def.ColumnName)); // $"{(tableAlias != null ? $"[{tableAlias}]." : "")}[{col.ColumnName}]";
}
return(getCustomColumnDef?.Invoke(columnName, out def));
}
