public void GroupByTest()
{
const string expected =
"SELECT * " +
"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) " +
"GROUP BY _t1.[TerritoryID]";
SqlServerCommandBuilder builder = new SqlServerCommandBuilder(Session);
// Add tables
builder.AppendFrom(Customer.Table);
builder.AppendFrom(SalesTerritory.Table);
// Add Join Columns
builder.AppendWhere((Customer.Columns.TerritoryId == SalesTerritory.Columns.TerritoryId).AsJoin());
// 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);
builder.AppendGroupBy(SalesTerritory.Columns.TerritoryId);
AssertCommandTextSame(expected, builder);
AssertParamCountSame(builder, 5);
}
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);
}
public void SingleWhereColumn()
{
const string expected =
"SELECT * " +
"FROM [Sales].[Customer] _t0 " +
"WHERE _t0.[CustomerID] = @_p0";
SqlServerCommandBuilder builder = new SqlServerCommandBuilder(Session);
// Add tables
builder.AppendFrom(Customer.Table);
// Add Where Columns
builder.AppendWhere(Customer.Columns.CustomerId == 1);
AssertCommandTextSame(expected, builder);
AssertParamCountSame(builder, 1);
}
public void Paging()
{
const string expected =
"SELECT * FROM (" +
"SELECT TOP (@_pi + @_ps) ROW_NUMBER() " +
"OVER ( ORDER BY _t0.[CustomerID] ASC) AS _prr, * " +
"FROM [Sales].[Customer] _t0" +
") AS _prt " +
"WHERE (_prr BETWEEN @_pi + 1 AND (@_pi + @_ps))";
SqlServerCommandBuilder builder = new SqlServerCommandBuilder(Session);
// Add tables
builder.AppendFrom(Customer.Table);
builder.Constrain(Constraint.Page(0, 25));
AssertCommandTextSame(expected, builder);
AssertParamCountSame(builder, 2);
}
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);
}
