/// <summary> /// Apply paginiation to the query /// </summary> /// <param name="orderByFieldList">fileds to sort the data, in order to get page numbers</param> /// <param name="sortDirectionList">sort direction</param> /// <param name="startRow">page start row</param> /// <param name="endRow">page end row</param> public void ApplyPagination(Collection <SqlExpressionBase> orderByFieldList, Collection <SortDirection> sortDirectionList, SqlExpressionBase startRow, SqlExpressionBase endRow) { //Add row number column to the current query builder string[] fieldList = new string[orderByFieldList.Count]; SortDirection[] sortDirectionArray = new SortDirection[sortDirectionList.Count]; for (int i = 0; i < orderByFieldList.Count; i++) { fieldList.SetValue(orderByFieldList[i].ToString(), i); } for (int i = 0; i < sortDirectionList.Count; i++) { sortDirectionArray.SetValue(sortDirectionList[i], i); } this.QueryBuilder.AddField(DataHelper.RowNumber(fieldList, sortDirectionArray)); //Create a new query builder for paged query QueryBuilder pagedQueryBuilder = new QueryBuilder(SQLType.SelectSQL, this.Name); //Add current query builder as an inner query to new query builder pagedQueryBuilder.AddTable(this.QueryBuilder, pagedTableName); //Add pagination where clause to new query builder pagedQueryBuilder.AddWhereClauseItem(DataHelper.Between(rowNumberColumnName, startRow.ToString(), endRow.ToString())); //Add all columns to new query builder foreach (var fieldName in this.FieldNames) { pagedQueryBuilder.AddField(pagedTableName, fieldName, fieldName); } //Add all parameters to new query builder foreach (var parameter in this.QueryBuilder.Parameters) { pagedQueryBuilder.Parameters.Add(parameter); } //Replace curretn query builder with new query builder this.QueryBuilder = pagedQueryBuilder; }