public PagedSelect(DMATable table, DMAColumn orderBy, int page, int pageSize, List<WhereCondition> where)
{
parameters = new List<SqlParameter>();
var columns = table.GetColumnsForListView(INCLUDE_KEY_COLUMNS);
var aliases = GenerateColumnAliases(columns);
var columns_aliased = columns.Select(col => col.GetFullName() + " as [" + aliases[col] + "]");
var externalTables = table.GetExternalTables();
var tables = new List<string> { table.GetFQN() };
tables.AddRange(externalTables.Select(t => t.GetFQN()));
if (orderBy == null)
orderBy = columns.First();
where = (where ?? new List<WhereCondition>());
foreach(var extRefColumn in table.DMAColumns.Where(col => col.ExternalTable != Guid.Empty))
{
var extKeyColumn = extRefColumn.GetExternalKeyColumn();
where.Add(new WhereCondition { Column = extRefColumn.DMATable.Name + "." + extRefColumn.Name, Operator = "=", Value = extKeyColumn.DMATable.Name + "." + extKeyColumn.Name, UseRawValue = true });
}
var startIndex = (page - 1) * pageSize + 1;
var endIndex = page * pageSize;
GetPresetFiltering(table, where);
var query = queryTemplate
.Replace("{table}", string.Join(", ", tables))
.Replace("{columns}", string.Join(", ", columns_aliased))
.Replace("{columns-display}", string.Join(", ", columns.Select(col => "[" + aliases[col] + "] as [" + col.Alias + "]")))
.Replace("{orderby}", string.Join(", ", orderBy.GetFullName()));
ResolveParameters(where);
if (where.Any())
{
var whereCond = string.Join(" AND ", where.Select(w => w.UseRawValue
? w.Column + " " + w.Operator + " " + w.Value
: w.Column + " " + w.Operator + " " + valueRef[w.Value]));
query = query.Replace("{where}", "WHERE " + whereCond);
}
else
{
query = query.Replace("{where}", "");
}
query = query.Replace("{paging}", "WHERE __rowno BETWEEN " + startIndex + " AND " + endIndex);
Query = query;
}