static void Main(string[] args) { var queryBuilder = new QueryBuilder(); var t0 = new Table() { TableName = "Table0", TableAlias = "t0" }; var t1 = new Table() { TableName = "Table1", Column1Name = "Column1", Column2Name = "Column2", JoinType = JoinType.Inner, TableAlias = "t2" }; ////Create table from first table //var tables = new List<Table>(); //tables.Add(t1); //// Perform a join //t0.JoinOn(tables); //Add columns which you want to add. queryBuilder.AddSelectColumn("column1"); queryBuilder.AddSelectColumn("column2"); queryBuilder.AddTable(t0); ////Create Where clauses using predicates query builder provides you methods to add predicate ////there are two ways you can add predicate either by string or predicate object var textMatchExpression = new Predicate("t1.[Column3] like 'Joe'"); ////AddPredicate also takes just string if you have only one where condition. ////add predicate and specify the operation type supports and or and not queryBuilder.AddPredicate(textMatchExpression, OperationType.And); var datesExpressions1 = new List<string>(); datesExpressions1.Add("f.[Column5] >= '2015-09-01'"); datesExpressions1.Add("f.[Column6] <= '2015-12-31'"); var dateExpressions2 = new List<string>(); dateExpressions2.Add("f.[Column7] >= '2015-10-01'"); dateExpressions2.Add("f.[Column8] >= '2015-12-31'"); var textMatchExpression2 = new Predicate("t1.[Column3] like 'Joe'"); //add groups of predicates queryBuilder.AddPredicate(datesExpressions1, OperationType.And, OperationType.Or); queryBuilder.AddPredicate(dateExpressions2, OperationType.And, OperationType.Or); queryBuilder.AddPredicate(textMatchExpression2, OperationType.And); //Order by queryBuilder.Query.OrderBy("Column1", OrderByType.ASC); //Fetch overloaded with offset and number of rows. queryBuilder.Query.FetchNext("2", "20"); queryBuilder.GenerateQuery(); queryBuilder.GenerateCountQuery(); }
/// <summary> /// Adds predicate using a string without the operation type. /// </summary> /// <param name="expression"></param> public void AddPredicate(string expression) { var predicate = new Predicate(expression); Predicates.Add(predicate); }
/// <summary> /// Adds list of predicates which are list of strings. /// </summary> /// <param name="predicates"></param> /// <param name="operationType"></param> public void AddPredicate(List<string> predicates, OperationType innerOperation,OperationType operationType) { var expression = "("; var operation = ""; switch (innerOperation) { case OperationType.Or: operation = " OR "; break; case OperationType.And: operation = " AND "; break; case OperationType.Not: operation = " NOT "; break; } if (predicates.Count > 1) { expression += predicates[0]; for (var i = 1; i < predicates.Count; i++) { expression += operation + predicates[i]; } } else { expression += predicates[0]; } expression += ")"; var predicate = new Predicate(expression); predicate.OperationType = operationType; Predicates.Add(predicate); }
/// <summary> /// Adds the predicate object to list with Or and And methods. /// </summary> /// <param name="expression">Of Predicate object type</param> public void AddPredicate(Predicate predicate, OperationType operationType) { predicate.OperationType = operationType; Predicates.Add(predicate); }