public string Format(Filter filter, string originalQuery, out string totalRecordsQuery) { //{0}: sort column //{1}: sort column order //{2}: number of rows per page //{3}: original query without order by //{4}: where clause //{5}: page number string template = @" SELECT * FROM ( SELECT ( ( (ROW_NUMBER() OVER (ORDER BY [OriginalTable].[{0}] {1}) - 1) -- sort column and sort column order / {2} -- number of rows per page ) ) + 1 as [PageNumber], * FROM ( -- Start Original Query -- Restriction: Original Query cannot have an ORDER BY clause {3} -- End Original Query ) [OriginalTable] WHERE ({4}) -- where clause ) [PagedTable] WHERE [PagedTable].[PageNumber] IN ({5}) -- page number "; string sortColumnOrder = filter.SortAscending ? "asc" : "desc"; string whereClause = GetWhereClause(filter, "OriginalTable"); totalRecordsQuery = "SELECT COUNT([OriginalTable].[" + filter.SortColumnName + "]) FROM (" + originalQuery + ") [OriginalTable] WHERE (" + whereClause + ")"; return string.Format(template, filter.SortColumnName, sortColumnOrder, filter.RowsPerPage, originalQuery, whereClause, filter.Page); }
private string GetWhereClause(Filter filter, string tableName) { if (filter.FilterRows.Count == 0) return "0 = 0"; string whereClause = "(" + filter.FilterRows[0].FieldOperator.GetFilter(GetFullyQualifiedName(tableName, filter.FilterRows[0].FieldName), filter.FilterRows[0].FieldValue) + ")"; for (int i = 1; i < filter.FilterRows.Count; i++) { string filterTemplate = filter.FilterOperator.GetTemplate(); whereClause += string.Format(filterTemplate, filter.FilterRows[i].FieldOperator.GetFilter(GetFullyQualifiedName(tableName, filter.FilterRows[i].FieldName), filter.FilterRows[i].FieldValue)); } return whereClause; }
public Filter Parse(QueryStringInfo queryStringInfo) { Filter filter = new Filter { Page = queryStringInfo.page, RowsPerPage = queryStringInfo.rows, SortColumnName = queryStringInfo.sidx, SortAscending = queryStringInfo.sord.ToUpper().Equals("ASC") }; filter.FilterOperator = new FilterOperatorFactory().Build(queryStringInfo.filters.groupOp); foreach (QueryStringFilterRule queryStringFilterRule in queryStringInfo.filters.rules) { filter.FilterRows.Add(new FilterRow { FieldName = queryStringFilterRule.field, FieldValue = queryStringFilterRule.data, FieldOperator = new FieldOperatorFactory().Build(queryStringFilterRule.op) }); } return filter; }