public void Basic() { const string expected = "SELECT _t0.[CustomerID], _t0.[AccountNumber], _t1.[Name] " + "FROM [Sales].[Customer] _t0, [Sales].[SalesTerritory] _t1 " + "WHERE _t0.[TerritoryID] = _t1.[TerritoryID] " + "AND _t0.[CustomerID] = @_p0 " + "AND (_t0.[CustomerID] = @_p1 OR _t0.[TerritoryID] = @_p2) " + "OR (_t0.[CustomerID] > @_p3 OR _t1.[Name] LIKE @_p4)"; SqlServerCommandBuilder builder = new SqlServerCommandBuilder(Session); // Add Select Columns builder.AppendSelect(Customer.Columns.CustomerId, Customer.Columns.AccountNumber, SalesTerritory.Columns.Name); // Add Join Columns builder.AppendWhere(Customer.Columns.TerritoryId == SalesTerritory.Columns.TerritoryId); // Add Where Columns builder.AppendWhere(Customer.Columns.CustomerId == 1); builder.AppendWhere((Customer.Columns.CustomerId == 2) | (Customer.Columns.TerritoryId == 5)); ColumnPredicate predicate = (Customer.Columns.CustomerId > 2) | SalesTerritory.Columns.Name.Contains("X"); predicate.OrToPreviousGroup = true; builder.AppendWhere(predicate); AssertCommandTextSame(expected, builder); AssertParamCountSame(builder, 5); }
private static CommandBuilder GetSelectBuilder(QueryTree queryTree, ConstraintType constraintType) { SqlServerCommandBuilder commandBuilder = new SqlServerCommandBuilder(queryTree) { Distinct = queryTree.SelectWithDistinct }; if (queryTree.Localized) { commandBuilder.JoinInWhereClause = false; } commandBuilder.Constrain(queryTree.SelectWithPaging ? Constraint.Page(queryTree.StartIndex, queryTree.PageSize) : queryTree.ResultConstraint); for (QueryTree current = queryTree; current != null; current = current.Next) { if (constraintType != ConstraintType.Count) { commandBuilder.AppendSelect(current.RetrieveQueryColumns()); } commandBuilder.AppendFrom(current.Table); foreach (JoinPredicate joinPredicate in current.Joins) { commandBuilder.AppendWhere(joinPredicate); } foreach (ColumnPredicate columnPredicate in current.WherePredicates) { commandBuilder.AppendWhere(columnPredicate); } foreach (ColumnPredicate columnPredicate in current.HavingPredicates) { commandBuilder.AppendHaving((columnPredicate)); } commandBuilder.AppendGroupBy(current.RetrieveGroupByColumns()); commandBuilder.AppendOrderBy(current.RetrieveOrderByColumns()); } return(commandBuilder); }