public void Equals_WithArgumentOfDifferentType_ReturnsFalse()
{
SqlConstant first = new SqlConstant(5);
string second = "The rain in Spain falls mainly on the plain.";
Assert.False(first.Equals(second));
}
public void Equals_WithNullArgument_ReturnsFalse()
{
SqlConstant first = new SqlConstant(5);
Assert.False(first.Equals((object)null));
Assert.False(first.Equals((SqlConstant)null));
}
public void Equals_WithNullValueAndNullArgument_ReturnsTrue()
{
SqlConstant first = new SqlConstant(null);
SqlConstant second = new SqlConstant(null);
Assert.True(first.Equals(second));
}
public void Equals_WithNullValueAndNonNullArgument_ReturnsFalse()
{
SqlConstant first = new SqlConstant(null);
SqlValue second = "The rain in Spain falls mainly on the plain.";
Assert.False(first.Equals(second));
}
public void Equal_WithNullLeftOperand_ThrowsArgumentNull()
{
var right = new SqlConstant(5);
var ex = Assert.Throws <ArgumentNullException>(() => SqlExpression.Equal(null, right));
Assert.Equal("left", ex.ParamName);
}
public void Ctor_WithValue_SetsValueProperty()
{
var value = new object();
var constant = new SqlConstant(value);
Assert.Same(value, constant.Value);
}
public void Ctor_WithValue_SetsValueProperty()
{
var expected = new SqlConstant(5);
var assign = new SqlAssign("Id", expected);
Assert.NotNull(assign.Value);
Assert.Equal(expected, assign.Value);
}
public void Ctor_WithValue_SetsValueProperty()
{
var expected = new SqlConstant(5);
var assign = new SqlAssign("Id", expected);
Assert.NotNull(assign.Value);
Assert.Equal(expected, assign.Value);
}
public virtual void Pivot1()
{
SelectQuery baseQuery = new SelectQuery();
baseQuery.Columns.Add(new SelectColumn("*"));
baseQuery.FromClause.BaseTable = FromTerm.Table("orders");
PivotTable pivot = new PivotTable();
SetupPivot(pivot);
pivot.BaseQuery = baseQuery;
pivot.Function = SqlAggregationFunction.Sum;
pivot.ValueField = "quantaty";
pivot.RowField = "customerId";
PivotColumn pivotCol = new PivotColumn("date", SqlDataType.Date);
TimePeriod currentYear = TimePeriod.FromToday(TimePeriodType.Year);
pivotCol.Values.Add(PivotColumnValue.CreateRange("PreviousYears", new Range(null, currentYear.Add(-1).PeriodStartDate)));
pivotCol.Values.Add(PivotColumnValue.CreateRange("LastYear", new Range(currentYear.Add(-1).PeriodStartDate, currentYear.PeriodStartDate)));
pivotCol.Values.Add(PivotColumnValue.CreateRange("FollowingYears", new Range(currentYear.PeriodStartDate, null)));
pivot.Columns.Add(pivotCol);
pivotCol = new PivotColumn("productId", SqlDataType.Number);
pivotCol.Values.Add(PivotColumnValue.CreateScalar("product1", 1));
pivotCol.Values.Add(PivotColumnValue.CreateRange("product2", new Range(2, 3)));
pivot.Columns.Add(pivotCol);
SelectQuery pivotQuery = pivot.BuildPivotSql();
DataTable data = Fill(pivotQuery);
if (data == null)
{
return;
}
WriteTable(data);
Console.WriteLine("Drill down");
SelectQuery drillDownQuery = pivot.BuildDrillDownSql(SqlConstant.Number(1), "LastYear");
data = Fill(drillDownQuery);
if (data == null)
{
return;
}
WriteTable(data);
Console.WriteLine("Drill down");
drillDownQuery = pivot.BuildDrillDownSql(null, "LastYear");
data = Fill(drillDownQuery);
if (data == null)
{
return;
}
WriteTable(data);
}
public void AdditionOperator_ReturnsParameter()
{
var constant = new SqlConstant(5);
var parameter = "Id" + constant;
Assert.NotNull(parameter);
Assert.NotNull(parameter.ParameterName);
Assert.Equal("Id", parameter.ParameterName);
Assert.NotNull(parameter.Value);
Assert.Equal(5, parameter.Value);
}
public void Equal_WithLeftAndRightOperand_ReturnsBinaryExpression()
{
var left = new SqlColumn("dbo.Users.Id");
var right = new SqlConstant(5);
var expression = SqlExpression.Equal(left, right);
Assert.NotNull(expression.Left);
Assert.Same(left, expression.Left);
Assert.Equal(SqlBinaryOperator.Equal, expression.Operator);
Assert.NotNull(expression.Right);
Assert.Same(right, expression.Right);
}
/// <summary>
/// Renders a list of values
/// </summary>
/// <param name="builder"></param>
/// <param name="values"></param>
/// <returns></returns>
protected virtual void ConstantList(StringBuilder builder, SqlConstantCollection values)
{
for (int i = 0; i < values.Count; i++)
{
SqlConstant val = values[i];
Constant(builder, val);
if (i != values.Count - 1)
{
Coma(builder);
}
}
}
public void Equal_WithLeftAndRightOperand_ReturnsBinaryExpression()
{
var left = new SqlColumn("dbo.Users.Id");
var right = new SqlConstant(5);
var expression = SqlExpression.Equal(left, right);
Assert.NotNull(expression.Left);
Assert.Same(left, expression.Left);
Assert.Equal(SqlBinaryOperator.Equal, expression.Operator);
Assert.NotNull(expression.Right);
Assert.Same(right, expression.Right);
}
public void Ctor_WithLeftAndRightOperand_SetsPropertyValues()
{
var left = new SqlConstant(5);
var @operator = SqlBinaryOperator.Equal;
var right = new SqlConstant(1);
var expression = new SqlBinaryExpression(left, @operator, right);
Assert.NotNull(expression.Left);
Assert.Same(left, expression.Left);
Assert.Equal(@operator, expression.Operator);
Assert.NotNull(expression.Right);
Assert.Same(right, expression.Right);
}
public void Ctor_WithLeftAndRightOperand_SetsPropertyValues()
{
var left = new SqlConstant(5);
var @operator = SqlBinaryOperator.Equal;
var right = new SqlConstant(1);
var expression = new SqlBinaryExpression(left, @operator, right);
Assert.NotNull(expression.Left);
Assert.Same(left, expression.Left);
Assert.Equal(@operator, expression.Operator);
Assert.NotNull(expression.Right);
Assert.Same(right, expression.Right);
}
/// <summary>
/// Renders a constant
/// </summary>
/// <param name="builder"></param>
/// <param name="expr"></param>
protected virtual void Constant(StringBuilder builder, SqlConstant expr)
{
SqlDataType type = expr.Type;
if (type == SqlDataType.Number)
{
builder.Append(expr.Value.ToString());
}
else if (type == SqlDataType.String)
{
builder.AppendFormat("'{0}'", (expr.Value == null) ? "" : expr.Value.ToString());
}
else if (type == SqlDataType.Date)
{
DateTime val = (DateTime)expr.Value;
bool dateOnly = (val.Hour == 0 && val.Minute == 0 && val.Second == 0 && val.Millisecond == 0);
string format = (dateOnly) ? dateFormat : dateTimeFormat;
builder.AppendFormat("'{0}'", val.ToString(format));
}
}
/// <summary>
/// Creates a <see cref="SelectQuery"/> which produces drill-down results
/// </summary>
/// <param name="crossTabRowKey">Value identifying cross-tab's row</param>
/// <param name="crossTabColumnName">Name of a cross-tab column</param>
/// <returns>A <see cref="SelectQuery"/> which produces drill-down results of the specified cross-tab cell</returns>
public SelectQuery BuildDrillDownSql(SqlConstant crossTabRowKey, string crossTabColumnName)
{
Validate();
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("*"));
query.FromClause.BaseTable = GetBaseFromTerm();
PivotColumn pivotCol;
PivotColumnValue pivotVal;
if (!FindPivotColumnValue(crossTabColumnName, out pivotCol, out pivotVal))
{
throw new PivotTableException(string.Format("Cross-Tab column '{0}' could not be found in pivot transformation definition.", crossTabColumnName));
}
query.WherePhrase.Terms.Add(CreateColumnValueCondition(pivotCol, pivotVal));
if (crossTabRowKey != null)
{
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(rowField), SqlExpression.Constant(crossTabRowKey), CompareOperator.Equal));
}
return(query);
}
protected override SqlExpression VisitConstant(SqlConstant expression)
{
IEnumerable<SqlValue> values = expression.Value as IEnumerable<SqlValue>;
if (values != null)
{
_writer.WriteStartList();
foreach (var value in values)
{
Visit(value);
}
_writer.WriteEndList();
}
else
{
_writer.WriteValue(expression.Value);
}
return expression;
}
/// <summary>
/// Visits the specified <see cref="SqlConstant"/>.
/// </summary>
/// <param name="expression">
/// The expression to visit.
/// </param>
public virtual void Visit(SqlConstant expression)
{
}
/// <summary>
/// Renders a constant
/// </summary>
/// <param name="builder"></param>
/// <param name="expr"></param>
protected virtual void Constant(StringBuilder builder, SqlConstant expr)
{
SqlDataType type = expr.Type;
if (type == SqlDataType.Number)
{
builder.Append(expr.Value.ToString());
}
else if (type == SqlDataType.String)
{
builder.AppendFormat("'{0}'", (expr.Value == null) ? "" : expr.Value.ToString());
}
else if (type == SqlDataType.Date)
{
DateTime val = (DateTime) expr.Value;
bool dateOnly = (val.Hour == 0 && val.Minute == 0 && val.Second == 0 && val.Millisecond == 0);
string format = (dateOnly) ? dateFormat : dateTimeFormat;
builder.AppendFormat("'{0}'", val.ToString(format));
}
}
protected virtual SqlExpression VisitConstant(SqlConstant expr)
{
return expr;
}
protected override SqlExpression VisitConstant(SqlConstant expr)
{
FormatValue(expr.Value);
return expr;
}
protected virtual SqlExpression VisitConstant(SqlConstant expr)
{
return(expr);
}
protected override SqlExpression VisitConstant(SqlConstant expr)
{
FormatValue(expr.Value);
return(expr);
}
public void Equal_WithNullLeftOperand_ThrowsArgumentNull()
{
var right = new SqlConstant(5);
Assert.Throws<ArgumentNullException>(() => SqlExpression.Equal(null, right));
}
/// <summary>
/// Visits the specified <see cref="SqlConstant"/>.
/// </summary>
/// <param name="expression">
/// The expression to visit.
/// </param>
public virtual void Visit(SqlConstant expression)
{
}
public virtual void ComplicatedQuery()
{
FromTerm tCustomers = FromTerm.Table("customers", "c");
FromTerm tProducts = FromTerm.Table("products", "p");
FromTerm tOrders = FromTerm.Table("orders", "o");
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn(SqlExpression.IfNull(SqlExpression.Field("name", tCustomers), SqlExpression.Constant(SqlConstant.String("name"))), "notNull"));
query.Columns.Add(new SelectColumn(SqlExpression.Null(), "nullValue"));
query.Columns.Add(new SelectColumn("name", tProducts, "productName", SqlAggregationFunction.None));
query.Columns.Add(new SelectColumn("price", tProducts));
query.FromClause.BaseTable = tCustomers;
query.FromClause.Join(JoinType.Left, tCustomers, tOrders, "customerId", "customerId");
query.FromClause.Join(JoinType.Inner, tOrders, tProducts, "productId", "productId");
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("John"), CompareOperator.Equal));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.String("Dohe"), SqlExpression.Field("name", tCustomers), CompareOperator.NotEqual));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("J%"), CompareOperator.Like));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Date(DateTime.Now), SqlExpression.Field("date", tOrders), CompareOperator.Greater));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(9), CompareOperator.Greater));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(9), CompareOperator.GreaterOrEqual));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(11.5), CompareOperator.LessOrEqual));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(1), SqlExpression.Number(1), CompareOperator.BitwiseAnd));
WhereClause group = new WhereClause(WhereClauseRelationship.Or);
group.Terms.Add(WhereTerm.CreateBetween(SqlExpression.Field("price", tProducts), SqlExpression.Number(1), SqlExpression.Number(10)));
group.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("name", tProducts), SqlConstantCollection.FromList(new string[] { "Nail", "Hamer", "Skrewdriver" })));
group.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("name", tProducts), "select name from products"));
group.Terms.Add(WhereTerm.CreateNotIn(SqlExpression.Field("name", tProducts), SqlConstantCollection.FromList(new string[] { "Unkown" })));
group.Terms.Add(WhereTerm.CreateNotIn(SqlExpression.Field("name", tProducts), "select name from products"));
group.Terms.Add(WhereTerm.CreateIsNull(SqlExpression.Field("name", tProducts)));
group.Terms.Add(WhereTerm.CreateIsNotNull(SqlExpression.Field("name", tProducts)));
group.Terms.Add(WhereTerm.CreateExists("select productId from products"));
group.Terms.Add(WhereTerm.CreateNotExists("select productId from products"));
query.WherePhrase.SubClauses.Add(group);
query.OrderByTerms.Add(new OrderByTerm("name", tCustomers, OrderByDirection.Descending));
query.OrderByTerms.Add(new OrderByTerm("price", OrderByDirection.Ascending));
query.Distinct = true;
query.Top = 10;
RenderSelect(query);
}
public void ExpressionType_ReturnsConstant()
{
var constant = new SqlConstant(null);
Assert.Equal(SqlExpressionType.Constant, constant.ExpressionType);
}