Exemple #1
0
 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);
 }
Exemple #2
0
 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;
 }
Exemple #3
0
        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;
        }
Exemple #4
0
 private static string getFQN(DMATable table)
 {
     return string.Format("{0}.{1}.{2}", escape(table.Database), escape(table.Schema), escape(table.Name));
 }
Exemple #5
0
 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;
 }
Exemple #6
0
 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));
 }