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