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