public void WhereQuery_AliasedWhereFact()
{
var fromTable = new Table("dbo.from", "f");
var addressCol = new AliasedExpression <Expression>(
new Expression("CONCAT(Street, Number)"), "Address");
var zipCityCol = new AliasedExpression <Expression>(
new Expression("CONCAT(Zip, City)"), "ZipCity");
var builder = new SqlExpressionBuilderSelect();
builder
.From(fromTable)
.Select(addressCol)
.Select(zipCityCol)
.Where(
new Compare <string, string>(addressCol.Alias, "@address"));
builder.Where(
new Compare <string, IDbDataParameter>(zipCityCol.Alias, new SqlParameter {
ParameterName = "@zc"
}));
Assert.Equal(
"SELECT CONCAT(Street, Number) AS Address, CONCAT(Zip, City) AS ZipCity FROM dbo.from f " +
"WHERE Address = @address AND ZipCity = @zc", builder.ToString());
Assert.Equal(2, builder.Parameters.Count());
}
public void BindParameter_BindAndRebindParameterFact()
{
var builder = new SqlExpressionBuilderSelect();
builder.From("table", "t");
builder.Where("t.foo = @foo");
var param = new SqlParameter("@foo", SqlDbType.Int)
{
Value = 5
};
builder.BindParameter(param);
Assert.Equal(1, builder.Parameters.Count());
Assert.Equal(param, builder.Parameters.First());
// Rebind by new param
var newParam = new SqlParameter("@foo", SqlDbType.Int)
{
Value = 6
};
builder.BindParameter(newParam);
Assert.Equal(1, builder.Parameters.Count());
Assert.Equal(newParam, builder.Parameters.First());
// Rebind by overwrite
builder.BindParameter("@foo", 7);
Assert.Equal(1, builder.Parameters.Count());
Assert.Equal(newParam, builder.Parameters.First());
Assert.Equal(7, builder.Parameters.First().Value);
}
public void WhereQuery_ComparerFact()
{
var fromTable = new Table("dbo.from", "f");
var builder = new SqlExpressionBuilderSelect();
builder.From(fromTable);
builder.Where(fromTable.GetColumn("foo"), "@foo");
builder.Where(fromTable.GetColumn("bar"), new SqlParameter {
ParameterName = "@bar"
});
builder.Where(fromTable.GetColumn("baz"), Expression.Null, CompareOperations.IsNot);
builder.Where(fromTable.GetColumn("moo"),
new Expression("(SELECT value FROM options o WHERE key = 'mooKey')"));
Assert.Equal("SELECT * FROM dbo.from f WHERE f.foo = @foo AND f.bar = @bar AND f.baz IS NOT NULL " +
"AND f.moo = (SELECT value FROM options o WHERE key = 'mooKey')",
builder.ToString());
Assert.Equal(2, builder.Parameters.Count());
}
public void BindParameter_DoesNotThrowOnParameterFoundFact()
{
var builder = new SqlExpressionBuilderSelect();
builder.From("table", "t");
builder.Where("t.foo = @foo");
Assert.DoesNotThrow(() => builder.BindParameter("@foo", DbType.Int32, 12));
}
public void WhereQuery_SimpleFact()
{
var fromTable = new Table("dbo.from", "f");
var builder = new SqlExpressionBuilderSelect();
builder.From(fromTable);
builder.Where("bla = @bla OR blubb = @bla");
Assert.Equal("SELECT * FROM dbo.from f WHERE bla = @bla OR blubb = @bla", builder.ToString());
Assert.Equal(1, builder.Parameters.Count());
}
public void FillCommand_ThrowsIfIncompleteFact()
{
var command = new SqlCommand();
var builder = new SqlExpressionBuilderSelect();
var fromTable = new Table("table", "t");
var param = new SqlParameter("@bla", SqlDbType.VarChar);
builder.From(fromTable);
builder.Where("t.col = @bla", new[] { param });
Assert.Throws <Exception>(() => builder.FillCommand(command));
}
public void FillCommand()
{
var command = new SqlCommand();
var builder = new SqlExpressionBuilderSelect();
var fromTable = new Table("table", "t");
var param = new SqlParameter("@bla", SqlDbType.VarChar)
{
Value = 5
};
builder.From(fromTable);
builder.Where("t.col = @bla", new[] { param });
builder.FillCommand(command);
Assert.Equal(1, command.Parameters.Count);
Assert.Equal(param, command.Parameters.Cast <SqlParameter>().First());
Assert.Equal(builder.ToString(), command.CommandText);
}
