Example #1
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;
        }