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