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