public static string Count(DMATable table)
{
var query = string.Format("SELECT COUNT(1) as RecordCount FROM {0}", getFQN(table));
if (!table.DMAColumns.Any(col => col.FilterOperator != ""))
return query;
var filterExpressions = GetFilterExpressions(table);
return query + " WHERE " + string.Join(" AND ", filterExpressions);
}
private static List<string> GetFilterExpressions(DMATable table)
{
Parameters = new List<SqlParameter>();
var filterColumns = table.DMAColumns.Where(col => col.FilterOperator != "").ToList();
var filterExpressions = new List<string>();
for (var i = 0; i < filterColumns.Count; i++)
{
var name = "@param" + i;
var filterColumn = filterColumns[i];
var filterExpression = string.Format("{0} {1} {2}", filterColumn.Name, filterColumn.FilterOperator, name);
filterExpressions.Add(filterExpression);
Parameters.Add(new SqlParameter(name, filterColumn.FilterValue));
}
return filterExpressions;
}
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;
}
private static string getFQN(DMATable table)
{
return string.Format("{0}.{1}.{2}", escape(table.Database), escape(table.Schema), escape(table.Name));
}
public static string PagedSelect(DMATable table, int page, int pageSize, DMAColumn orderBy, List<WhereCondition> where)
{
var select = new PagedSelect(table, orderBy, page, pageSize, where);
Parameters = new List<SqlParameter>(select.Parameters.ToList());
return select.Query;
}
private void GetPresetFiltering(DMATable table, List<WhereCondition> where)
{
var filterColumns = table.DMAColumns.Where(col => col.FilterOperator != "").ToList();
foreach (var filterColumn in filterColumns)
where.Add(new WhereCondition(filterColumn.GetFullName(), filterColumn.FilterOperator, filterColumn.FilterValue));
}