/// <summary>
/// Apply paginiation to the query
/// </summary>
/// <param name="orderByField">filed to sort the data, in order to get page numbers</param>
/// <param name="sortDirection">sort direction</param>
/// <param name="startRow">page start row</param>
/// <param name="endRow">page end row</param>
public void ApplyPagination(SqlExpressionBase orderByField, SortDirection sortDirection, SqlExpressionBase startRow, SqlExpressionBase endRow)
{
//Add row number column to the current query builder
this.QueryBuilder.AddField(DataHelper.RowNumber(orderByField.ToString(), sortDirection));
WrapInOuterQuery();
//Add pagination where clause to new query builder
this.QueryBuilder.AddWhereClauseItem(DataHelper.Between(rowNumberColumnName, startRow.ToString(), endRow.ToString()));
}
/// <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;
}
/// <summary>
/// LessThanOreEqualTo
/// </summary>
/// <param name="expresion">sql expression</param>
/// <returns>instance fo SqlExpression</returns>
public SqlExpressionBase LessThanOrEqualTo(SqlExpressionBase expression)
{
return(new SqlExpression("(" + ExpressionString + " <= " + expression.ToString() + ")"));
}
/// <summary>
/// Create new SQL expression that will divide one given SQL expression by another (Experssion 1 / Expression 2)
/// </summary>
/// <param name="expression1">SQL expression 1</param>
/// <param name="expression2">SQL expression 2</param>
/// <returns>Return SQL expression</returns>
public static SqlExpressionBase Divide(SqlExpressionBase expression1, SqlExpressionBase expression2)
{
return(new SqlExpression("(" + expression1.ToString() + " / " + expression2.ToString() + ")"));
}
/// <summary>
/// Create new SQL expression that will multiply given SQL expressions
/// </summary>
/// <param name="expression1">SQL expression 1</param>
/// <param name="expression2">SQL expression 2</param>
/// <returns>Return SQL expression</returns>
public static SqlExpressionBase Multiply(SqlExpressionBase expression1, SqlExpressionBase expression2)
{
return(new SqlExpression("(" + expression1.ToString() + " * " + expression2.ToString() + ")"));
}
/// <summary>
/// Create new SQL expression that will substract one given SQL expression from another (expression 1 - expression 2)
/// </summary>
/// <param name="expression1">SQL expression 1</param>
/// <param name="expression2">SQL expression 2</param>
/// <returns>Return SQL expression</returns>
public static SqlExpressionBase Subtract(SqlExpressionBase expression1, SqlExpressionBase expression2)
{
return(new SqlExpression("(" + expression1.ToString() + " - " + expression2.ToString() + ")"));
}
/// <summary>
/// RowNumber
/// </summary>
/// <param name="sortOrder">SqlExpressionBase</param>
/// <param name="sortDirection">SortDirection</param>
/// <returns>SqlExpression</returns>
public static SqlExpressionBase RowNumber(SqlExpressionBase partitionField, SqlExpressionBase sortOrder, SortDirection sortDirection)
{
return(new SqlExpression("ROW_NUMBER() OVER (PARTITION BY " + partitionField.ToString() + " ORDER BY " + sortOrder.ToString() + " " + Enum.GetName(typeof(SortDirection), sortDirection) + " ) "));
}
/// <summary>
/// Greater than
/// </summary>
/// <param name="expresion">sql expression</param>
/// <returns>instance of SqlExpression</returns>
public SqlExpressionBase GreaterThan(SqlExpressionBase expression)
{
return(new SqlExpression("(" + ExpressionString + " > " + expression.ToString() + ")"));
}
/// <summary>
/// SetFeild method.
/// </summary>
/// <param name="field">SqlFeild</param>
/// <param name="value">SqlExpressionBase</param>
public void SetField(SqlField field, SqlExpressionBase value)
{
this.QueryBuilder.AddSetClauseItemWithFieldValueString(field.FieldName, value.ToString());
}
/// <summary>
/// Apply paginiation to the query
/// </summary>
/// <param name="orderByField">Filed to sort the data, in order to get page numbers</param>
/// <param name="startRow">page start row</param>
/// <param name="endRow">page end row</param>
public void ApplyPagination(SqlExpressionBase orderByField, SqlExpressionBase startRow, SqlExpressionBase endRow)
{
ApplyPagination(orderByField, SortDirection.Asc, startRow, endRow);
}
/// <summary>
/// Method to Set Having Clause.Use SqlExpressionBase type expression to set the having clause.
/// </summary>
/// <param name="expression">SqlExpressionBase</param>
public void SetHavingClause(SqlExpressionBase expression)
{
queryBuilder.Having = expression.ToString();
}
/// <summary>
/// Sets the Expression for the GroupBy Clause via the Expression parameter
/// </summary>
/// <param name="expression">SqlExpressionBase</param>
public void SetGroupByClause(SqlExpressionBase expression)
{
queryBuilder.GroupBy = expression.ToString();
}
/// <summary>
/// Gets the expression and the sortorder from the two Parameters to set the expression and the order for the OrderBy Clause
/// </summary>
/// <param name="expression">SqlExpressionBase</param>
/// <param name="sortOrder">SortOrderTypes</param>
public void AddOrderByClause(SqlExpressionBase expression, SortOrderTypes sortOrder)
{
queryBuilder.AddOrderClauseItem(expression.ToString(), sortOrder, orderBySequance);
orderBySequance++;
}
/// <summary>
/// Takes expression from the parameter and Add to Where clause
/// </summary>
/// <param name="expression">SqlExpressionBase</param>
public void AddWhereClause(SqlExpressionBase expression)
{
queryBuilder.AddWhereClauseItem(expression.ToString());
}
/// <summary>
/// NotEqualsTo
/// </summary>
/// <param name="expresion">sql expression</param>
/// <returns>instance of SqlExpression</returns>
public SqlExpressionBase NotEqualsTo(SqlExpressionBase expression)
{
return(new SqlExpression("(" + ExpressionString + " <> " + expression.ToString() + ")"));
}
/// <summary>
/// Like
/// </summary>
/// <param name="expresion">sql expression</param>
/// <returns>instance of SqlExpression</returns>
public SqlExpressionBase Like(SqlExpressionBase expression)
{
return(new SqlExpression("(" + ExpressionString + " LIKE " + expression.ToString() + ")"));
}
/// <summary>
/// This is the AddFeild method.Takes filed and alias as parameters
///
///
/// </summary>
/// <param name="filed">SqlExpressionBase</param>
/// <param name="alias">string</param>
public void AddField(SqlExpressionBase filed, string alias)
{
this.fieldNames.Add(alias);
this.QueryBuilder.AddFunction(filed.ToString(), alias);
}