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_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 SelectStatement_ThrowsIfWrongColumnName()
{
var builder = new SqlExpressionBuilderSelect();
builder.From("table", "t");
Assert.Throws <ArgumentException>(() => builder.Select("t.b.foo"));
}
public void BindParameter_DoesNotThrowOnSupressedWarningFact()
{
var builder = new SqlExpressionBuilderSelect();
builder.From("table", "t");
Assert.DoesNotThrow(() => builder.BindParameter("@foo", DbType.Int32, 12, true));
}
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 BindParameter_ThrowsOnParameterNotFoundFact()
{
var builder = new SqlExpressionBuilderSelect();
builder.From("table", "t");
Assert.Throws <InvalidOperationException>(() => builder.BindParameter("@foo", DbType.Int32, 12));
Assert.Throws <InvalidOperationException>(() => builder.BindParameter("@foo", 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 JoinReferencedTableExistsCheck_ExceptionDoesNotThrowFact()
{
var fromTable = new Table("from", "f");
var joinTable1 = new Table("join1", "j1");
// Works
var builder = new SqlExpressionBuilderSelect();
builder.From(fromTable);
Assert.DoesNotThrow(() => builder.JoinInner(fromTable.GetColumn("bla"), joinTable1.GetColumn("bla")));
}
public void JoinReferencedTableExistsCheck_ExceptionThrownOnReferencedTableNotFirstArgumentFact()
{
var fromTable = new Table("from", "f");
var joinTable1 = new Table("join1", "j1");
var builder = new SqlExpressionBuilderSelect();
builder.From(fromTable);
// first table has to be the referenced table
Assert.Throws <InvalidOperationException>(
() => builder.JoinInner(joinTable1.GetColumn("bla"), fromTable.GetColumn("bla")));
}
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 SimpleSelectFromFact()
{
var fromTable = new Table("from", "f");
var builder = new SqlExpressionBuilderSelect();
builder.From(fromTable);
Assert.Equal("SELECT * FROM from f", builder.ToString());
var expr = new AliasedExpression <CountExpression>(
new CountExpression(new ColumnExpression("Id", fromTable)), "Anzahl");
Assert.Equal("SELECT COUNT(f.Id) AS Anzahl FROM from f", builder.GetSingleSelectString(expr));
Assert.Equal("SELECT * FROM from f", builder.ToString());
}
public void MultipleFromQueryFact()
{
var fromTable1 = new Table("from1", "f1");
var fromTable2 = new Table("from2", "f2");
var builder = new SqlExpressionBuilderSelect();
builder
.From(fromTable1)
.From(fromTable2, new[] { "col1", "col2", "col3" })
.From("from3", "f3")
.From("from4");
Assert.Equal(
"SELECT f2.col1, f2.col2, f2.col3 FROM from1 f1, from2 f2, from3 f3, from4 from4",
builder.ToString());
}
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 SelectStatementFact()
{
var fromTable = new Table("dbo.from", "f");
var joinTable = new Table("dbo.join", "j");
var builder = new SqlExpressionBuilderSelect();
builder
.From(fromTable)
.Select("bla")
.Select(new[] { "f.blubb", "f.foo" });
Assert.Equal("SELECT f.bla, f.blubb, f.foo FROM dbo.from f", builder.ToString());
builder.Select(new[] { "f.muh" }, ExpressionOptions.Overwrite);
Assert.Equal("SELECT f.muh FROM dbo.from f", builder.ToString());
builder.Select("maeh", fromTable);
Assert.Equal("SELECT f.muh, f.maeh FROM dbo.from f", builder.ToString());
builder.JoinLeft(joinTable, "was = wer");
builder.Select(new[] { "muh", "maeh", "foo" }, joinTable);
Assert.Equal(
"SELECT f.muh, f.maeh, j.muh, j.maeh, j.foo FROM dbo.from f LEFT JOIN dbo.join j ON was = wer",
builder.ToString());
builder.Select(new[] { "foo", "bar" }, joinTable, ExpressionOptions.Overwrite);
Assert.Equal(
"SELECT j.foo, j.bar FROM dbo.from f LEFT JOIN dbo.join j ON was = wer", builder.ToString());
builder.Select(new AliasedExpression <Expression>(new Expression("CONCAT(Street, Number)"), "Address"));
Assert.Equal(
"SELECT j.foo, j.bar, CONCAT(Street, Number) AS Address " +
"FROM dbo.from f LEFT JOIN dbo.join j ON was = wer",
builder.ToString());
builder.Distinct();
Assert.Equal(
"SELECT DISTINCT j.foo, j.bar, CONCAT(Street, Number) AS Address " +
"FROM dbo.from f LEFT JOIN dbo.join j ON was = wer",
builder.ToString());
}
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);
}
public void GroupFacts()
{
var builder = new SqlExpressionBuilderSelect();
var fromTable = new Table("table", "t");
var joinTable = new Table("join", "j");
builder.From(fromTable);
builder.Select("Id", fromTable);
builder.Select(new AliasedExpression <CountExpression>(
new CountExpression(joinTable.GetColumn("moo")), "count"));
builder.JoinLeft(fromTable.GetColumn("Id"), joinTable.GetColumn("fromId"));
builder.Group(new[] { "t.Id" });
const string expected =
"SELECT t.Id, COUNT(j.moo) AS count " +
"FROM table t " +
"LEFT JOIN join j ON t.Id = j.fromId " +
"GROUP BY t.Id";
Assert.Equal(expected, builder.ToString());
}
public void JoinRightQueryFact()
{
var fromTable = new Table("dbo.from", "f");
var joinTable1 = new Table("dbo.join1", "j1");
var joinTable3 = new Table("dbo.join3", "j3");
var joinTable4 = new Table("dbo.join4", "j4");
var joinTable5 = new Table("dbo.join5", "j5");
var joinTable6 = new Table("dbo.join6", "j6");
var joinTable7 = new Table("dbo.join7", "j7");
var joinTable8 = new Table("dbo.join8", "j8");
var joinTable9 = new Table("dbo.join9", "j9");
var joinTableA = new Table("dbo.joinA", "jA");
var builder = new SqlExpressionBuilderSelect();
builder.From(fromTable);
builder.JoinRight(joinTable1, "f.Id = j1.f_Id");
builder.JoinRight("dbo.join2", "j2", "j1.Id = j2.j1_Id");
builder.JoinRight(joinTable3, "j2.Id = j3.j2_Id", new[] { "col1", "col2", "col3" });
builder.JoinRight(
joinTable4,
"j3.Id = j4.j3_Id AND j4.col1 = @param1",
new List <SqlParameter> {
new SqlParameter {
ParameterName = "@param1"
}
});
builder.JoinRight(
joinTable5,
"j4.Id = j5.j4_Id AND j5.col1 = @param2",
new[] { "col1", "col2" },
new[] { new SqlParameter {
ParameterName = "@param2"
} });
builder.JoinRight(
new Compare <ColumnExpression, ColumnExpression>(
new ColumnExpression("Id", joinTable5),
new ColumnExpression("j5_Id", joinTable6)));
builder.JoinRight(
new Compare <ColumnExpression, ColumnExpression>(
new ColumnExpression("Id", joinTable6),
new ColumnExpression("j6_Id", joinTable7)),
new[] { "col1", "col2" });
builder.JoinRight(joinTable7.GetColumn("Id"), joinTable8.GetColumn("j7_Id"));
builder.JoinRight(joinTable8.GetColumn("Id"), joinTable9.GetColumn("j8_Id"), new[] { "col1", "col2" });
builder.JoinRight(joinTable9.GetColumn("Id"), joinTableA.GetColumn("j9_Id"), CompareOperations.Equals);
const string expected =
"SELECT j3.col1, j3.col2, j3.col3, j5.col1, j5.col2, j7.col1, j7.col2, j9.col1, j9.col2 " +
"FROM dbo.from f " +
"RIGHT JOIN dbo.join1 j1 ON f.Id = j1.f_Id " +
"RIGHT JOIN dbo.join2 j2 ON j1.Id = j2.j1_Id " +
"RIGHT JOIN dbo.join3 j3 ON j2.Id = j3.j2_Id " +
"RIGHT JOIN dbo.join4 j4 ON j3.Id = j4.j3_Id AND j4.col1 = @param1 " +
"RIGHT JOIN dbo.join5 j5 ON j4.Id = j5.j4_Id AND j5.col1 = @param2 " +
"RIGHT JOIN dbo.join6 j6 ON j5.Id = j6.j5_Id " +
"RIGHT JOIN dbo.join7 j7 ON j6.Id = j7.j6_Id " +
"RIGHT JOIN dbo.join8 j8 ON j7.Id = j8.j7_Id " +
"RIGHT JOIN dbo.join9 j9 ON j8.Id = j9.j8_Id " +
"RIGHT JOIN dbo.joinA jA ON j9.Id = jA.j9_Id";
string actual = builder.ToString();
Assert.Equal(expected, actual);
}
public void OrderQueryFact()
{
string expectedTemplate = "SELECT * FROM dbo.from f ";
var fromTable = new Table("dbo.from", "f");
var joinTable = new Table("dbo.join", "j");
var builder = new SqlExpressionBuilderSelect();
builder
.From(fromTable)
.Order("bla")
.Order("blubb", OrderOptions.Desc);
string expected = expectedTemplate + "ORDER BY f.bla ASC, f.blubb DESC";
Assert.Equal(expected, builder.ToString());
// Fails
bool throws = false;
builder = new SqlExpressionBuilderSelect();
builder.From(fromTable).Order("bla");
try
{
builder.Order("b.blubb", OrderOptions.Desc); // table with alias b not found
}
catch
{
throws = true;
}
Assert.True(throws);
builder
.JoinInner(joinTable, "wayne = wayne")
.Order("j.blubb", OrderOptions.Desc)
.Order("j.foo", ExpressionOptions.Overwrite)
.Order(new ColumnExpression("bla", fromTable))
.Order(new ColumnExpression("muh", fromTable), OrderOptions.Desc);
expectedTemplate += "INNER JOIN dbo.join j ON wayne = wayne ";
expected = expectedTemplate + "ORDER BY j.foo ASC, f.bla ASC, f.muh DESC";
Assert.Equal(expected, builder.ToString());
builder.Order(new ColumnExpression("bla", fromTable), ExpressionOptions.Overwrite);
expected = expectedTemplate + "ORDER BY f.bla ASC";
Assert.Equal(expected, builder.ToString());
builder.Order(new ColumnExpression("foo", fromTable), OrderOptions.Desc, ExpressionOptions.Overwrite);
expected = expectedTemplate + "ORDER BY f.foo DESC";
Assert.Equal(expected, builder.ToString());
// Fails
throws = false;
builder = new SqlExpressionBuilderSelect();
builder.From(fromTable).Order("bla");
try
{
builder.Order(new ColumnExpression("foo", joinTable)); // table does not exist in sql builder
}
catch
{
throws = true;
}
Assert.True(throws);
}