public void FromMultipleCallsDoesCrossJoin()
{
var query = new SqlQuery()
.From("TestTable1")
.From("TestTable2")
.Select("TestColumn");
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT TestColumn FROM TestTable1, TestTable2"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void DistinctAddsKeyword()
{
var query = new SqlQuery()
.Distinct(true)
.Select("TestColumn")
.From("TestTable");
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT DISTINCT TestColumn FROM TestTable"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void DistinctCanBeTurnedOff()
{
var query = new SqlQuery()
.Distinct(true)
.Select("TestColumn")
.From("TestTable")
.Distinct(false);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT TestColumn FROM TestTable"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void FromMixedOverloadsMultipleCallsDoesCrossJoin()
{
var query = new SqlQuery()
.From("TestTable1", new Alias("x1"))
.From(new Alias("TestTable2", "x2"))
.From("TestTable3")
.Select("TestColumn");
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT TestColumn FROM TestTable1 x1, TestTable2 x2, TestTable3"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void DoesAutomaticJoinProperlyWithAliasedFields()
{
var x = RowMappingTests.ComplexRow.Fields.As("x");
var query = new SqlQuery()
.From(x)
.Select(x.CountryName);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT x_c.Name AS [CountryName] FROM ComplexTable x " +
"LEFT JOIN TheCountryTable x_c ON (x_c.TheCountryID = x.CountryID)"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void DoesAutomaticJoinProperlyWithAliasedAndJoinedFieldsAndSelect()
{
var x = RowMappingTests.ComplexRow.Fields.As("x");
var y = RowMappingTests.ComplexRow.Fields.As("y");
var query = new SqlQuery()
.From(x)
.LeftJoin(y, y.ID == x.ID)
.Select(x.CountryName, "CountryNameX")
.Select(y.CountryName, "CountryNameY");
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT " +
"x_c.Name AS [CountryNameX], " +
"y_c.Name AS [CountryNameY] " +
"FROM ComplexTable x " +
"LEFT JOIN ComplexTable y ON (y.ComplexID = x.ComplexID) " +
"LEFT JOIN TheCountryTable x_c ON (x_c.TheCountryID = x.CountryID) " +
"LEFT JOIN TheCountryTable y_c ON (y_c.TheCountryID = y.CountryID)"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void DoesAutomaticJoinProperlyWithViewRowIfAllNamesAreSelectedInJoinOrder()
{
var fld = ViewRow.Fields;
var query = new SqlQuery()
.From(fld)
.Select(fld.District)
.Select(fld.City)
.Select(fld.Country);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT " +
"d.Name AS [District], " +
"c.Name AS [City], " +
"o.Name AS [Country] " +
"FROM ViewTable T0 " +
"LEFT JOIN Districts d ON (d.DistrictID = T0.DistrictID) " +
"LEFT JOIN Cities c ON (c.CityID = d.CityID) " +
"LEFT JOIN Countries o ON (o.CountryID = c.CountryID) "),
TestSqlHelper.Normalize(
query.ToString()));
}
public void OrderByFirstAppendsDescKeywordWhenDescArgumentIsTrue()
{
var query = new SqlQuery()
.Select("u.TestColumn")
.From("TestTable u")
.OrderBy(new Alias("u"), "TestColumn", desc: true)
.OrderByFirst("TestColumn2", desc: true);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT u.TestColumn FROM TestTable u ORDER BY TestColumn2 DESC, u.TestColumn DESC"),
TestSqlHelper.Normalize(
query.ToString()));
}
/// <summary>
/// <see cref="SqlQuery"/> nesnesinin içerdiği sorguyu bağlantı üzerinde çalıştırır.</summary>
/// <param name="connection">
/// Sorgunun çalıştırılacağı bağlantı. Gerekirse otomatik olarak açılır.</param>
/// <param name="query">
/// Sorguyu içeren <see cref="SqlQuery"/> nesnesi.</param>
/// <param name="param">Parameter dictionary</param>
/// <returns>
/// Sorgu sonuçlarına erişim sağlayan <see cref="IDataReader"/> nesnesi.</returns>
public static IDataReader ExecuteReader(IDbConnection connection, SqlQuery query, Dictionary param)
{
return(ExecuteReader(connection, query.ToString(), param));
}
public void TakeUsesCorrectSyntaxForSqliteDialect()
{
var query = new SqlQuery()
.Dialect(SqliteDialect.Instance)
.Select("c")
.From("t")
.Take(10);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT c FROM t LIMIT 10"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void SubQueryCanBeUsedAsCriteriaUsingWith()
{
var query = new SqlQuery()
.From("ParentTable")
.Select("ParentColumn")
.With(me => me.Where(new Criteria(me.SubQuery()
.From("SubTable")
.Take(1)
.Select("SubColumn")) >= 1));
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT ParentColumn FROM ParentTable WHERE " +
"((SELECT TOP 1 SubColumn FROM SubTable) >= @p1)"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void OrderByWithExpressionWorks()
{
var query = new SqlQuery()
.Select("TestColumn")
.From("TestTable")
.OrderBy("TestColumn")
.OrderBy("TestColumn2");
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT TestColumn FROM TestTable ORDER BY TestColumn, TestColumn2"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void OrderByFirstWorksProperlyWhenNoOrderByExists()
{
var query = new SqlQuery()
.Select("TestColumn")
.From("TestTable")
.OrderByFirst("TestColumn")
.OrderBy("SecondColumn");
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT TestColumn FROM TestTable ORDER BY TestColumn, SecondColumn"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void FromWithSubQueryAndAliasWorks()
{
var query = new SqlQuery().With(me => me
.From(me.SubQuery()
.From("SubTable")
.Select("SubColumn"), new Alias("sub"))
.Select("SubColumn"));
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT SubColumn FROM (SELECT SubColumn FROM SubTable) sub"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void NoAutomaticJoinsIfFromNotUsed()
{
var fld = RowMappingTests.ComplexRow.Fields;
var query = new SqlQuery()
.Select(fld.CountryName);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT c.Name AS [CountryName]"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void DoesAutomaticJoinsIfFromRowUsed()
{
var fld = RowMappingTests.ComplexRow.Fields;
var query = new SqlQuery()
.From(new RowMappingTests.ComplexRow())
.Select(fld.CountryName);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT c.Name AS [CountryName] FROM ComplexTable T0 " +
"LEFT JOIN TheCountryTable c ON (c.TheCountryID = T0.CountryID)"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void DoesAutomaticJoinProperlyWithViewRowIfOnlyDistrictIsSelected()
{
var fld = ViewRow.Fields;
var query = new SqlQuery()
.From(fld)
.Select(fld.District);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT " +
"d.Name AS [District] " +
"FROM ViewTable T0 " +
"LEFT JOIN Districts d ON (d.DistrictID = T0.DistrictID) "),
TestSqlHelper.Normalize(
query.ToString()));
}
public void OrderByFirstHandlesDescWhileMovingExpressionToFirst()
{
var query1 = new SqlQuery()
.From("TestTable")
.Select("a")
.OrderBy("a")
.OrderBy("b", desc: true)
.OrderBy("c")
.OrderByFirst("b");
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT a FROM TestTable ORDER BY b, a, c"),
TestSqlHelper.Normalize(
query1.ToString()));
var query2 = new SqlQuery()
.From("TestTable")
.Select("a")
.OrderBy("a")
.OrderBy("b")
.OrderBy("c")
.OrderByFirst("b", desc: true);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT a FROM TestTable ORDER BY b DESC, a, c"),
TestSqlHelper.Normalize(
query2.ToString()));
}
public void OrderByFirstMovesExpressionToStartIfAlreadyInStatement()
{
var query = new SqlQuery()
.From("TestTable")
.Select("a")
.OrderBy("a")
.OrderBy("b")
.OrderBy("c")
.OrderByFirst("b");
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT a FROM TestTable ORDER BY b, a, c"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void FromWithTableNameAndAliasWorks()
{
var query = new SqlQuery()
.From("TestTable", new Alias("TestAlias"))
.Select("TestColumn");
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT TestColumn FROM TestTable TestAlias"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void OrderByWithAliasAndFieldnameWorks()
{
var query = new SqlQuery()
.Select("u.TestColumn")
.From("TestTable u")
.OrderBy(new Alias("u"), "TestColumn")
.OrderBy(new Alias("u"), "TestColumn2");
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT u.TestColumn FROM TestTable u ORDER BY u.TestColumn, u.TestColumn2"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void SkipUsesWorkAroundWithOneOrderByForSql2000Dialect()
{
var query = new SqlQuery()
.Dialect(SqlServer2000Dialect.Instance)
.Select("c")
.From("t")
.OrderBy("x")
.Skip(10);
Assert.Equal(
TestSqlHelper.Normalize(
"DECLARE @Value0 SQL_VARIANT;" +
"SELECT TOP 10 @Value0 = x FROM t ORDER BY x;" +
"SELECT c FROM t WHERE ((((x IS NOT NULL AND @Value0 IS NULL) OR (x > @Value0)))) ORDER BY x"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void SkipThrowsExceptionIfNoOrderByForSql2000Dialect()
{
var query = new SqlQuery()
.Dialect(SqlServer2000Dialect.Instance)
.Select("c")
.From("t")
.Skip(10);
Assert.Throws<InvalidOperationException>(delegate
{
query.ToString();
});
}
public void DoesAutomaticJoinProperlyWithViewRowIfAreSelectedInJoinOrderAliased()
{
var vw = ViewRow.Fields.As("vw");
var query = new SqlQuery()
.From(vw)
.Select(vw.District)
.Select(vw.City)
.Select(vw.Country);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT " +
"vw_d.Name AS [District], " +
"vw_c.Name AS [City], " +
"vw_o.Name AS [Country] " +
"FROM ViewTable vw " +
"LEFT JOIN Districts vw_d ON (vw_d.DistrictID = vw.DistrictID) " +
"LEFT JOIN Cities vw_c ON (vw_c.CityID = vw_d.CityID) " +
"LEFT JOIN Countries vw_o ON (vw_o.CountryID = vw_c.CountryID) "),
TestSqlHelper.Normalize(
query.ToString()));
}
public void SkipUsesRowNumberForSql2005Dialect()
{
var query = new SqlQuery()
.Dialect(SqlServer2005Dialect.Instance)
.Select("c")
.From("t")
.OrderBy("x")
.Skip(10)
.Take(20);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT * FROM (\n" +
"SELECT TOP 30 c, ROW_NUMBER() OVER (ORDER BY x) AS __num__ FROM t) __results__ " +
"WHERE __num__ > 10"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void HavingDoesAndWhenCalledMoreThanOnce()
{
var query = new SqlQuery()
.From("t")
.GroupBy("c")
.Select("c")
.Having("count(*) > 2")
.Having("sum(y) < 1000");
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT c FROM t GROUP BY c HAVING count(*) > 2 AND sum(y) < 1000"),
TestSqlHelper.Normalize(
query.ToString())
);
}
public void SkipUsesWorkAroundWithTwoOrderByForSql2000Dialect()
{
var query = new SqlQuery()
.Dialect(SqlServer2000Dialect.Instance)
.Select("c")
.From("t")
.Where("c > 2")
.OrderBy("x")
.OrderBy("y")
.Skip(100)
.Take(50);
Assert.Equal(
TestSqlHelper.Normalize(
"DECLARE @Value0 SQL_VARIANT;" +
"DECLARE @Value1 SQL_VARIANT;" +
"SELECT TOP 100 @Value0 = x,@Value1 = y FROM t WHERE c > 2 ORDER BY x, y;" +
"SELECT TOP 50 c FROM t WHERE c > 2 AND " +
"((((x IS NOT NULL AND @Value0 IS NULL) OR (x > @Value0))) " +
"OR (((x IS NULL AND @Value0 IS NULL) OR (x = @Value0)) " +
"AND ((y IS NOT NULL AND @Value1 IS NULL) OR (y > @Value1)))) ORDER BY x, y"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void HavingWithExpressionWorks()
{
var query = new SqlQuery()
.Select("TestColumn")
.From("TestTable")
.GroupBy("TestColumn")
.Having("Count(*) > 5");
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT TestColumn FROM TestTable GROUP BY TestColumn HAVING Count(*) > 5"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void SubQueryShouldBeEnclosedInParen()
{
var sub = new SqlQuery().SubQuery()
.Select("TestColumn")
.From("TestTable");
Assert.Equal(
TestSqlHelper.Normalize(
"(SELECT TestColumn FROM TestTable)"),
TestSqlHelper.Normalize(
sub.ToString()));
}
public void JoinIgnoresExistingJoinsWithSameAliasAndSameExpression()
{
var row = new Serenity.Test.Data.RowMappingTests.ComplexRow();
var fld = Serenity.Test.Data.RowMappingTests.ComplexRow.Fields;
var query = new SqlQuery()
.From(row)
.Select(fld.CountryName)
.LeftJoin(new Alias("TheCountryTable", "c"), new Criteria("c", "TheCountryID") == new Criteria(0, "CountryID"));
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT c.Name AS [CountryName] FROM ComplexTable T0 LEFT JOIN TheCountryTable c ON (c.TheCountryID = T0.CountryID)"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void WhereDoesAndWhenCalledMoreThanOnce()
{
var query = new SqlQuery().From("t").Select("c").Where("x > 5").Where("y < 4");
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT c FROM t WHERE x > 5 AND y < 4"),
TestSqlHelper.Normalize(
query.ToString())
);
}
/// <summary>
/// <see cref="SqlQuery"/> nesnesinin içerdiği sorguyu bağlantı üzerinde çalıştırır.</summary>
/// <param name="connection">
/// Sorgunun çalıştırılacağı bağlantı. Gerekirse otomatik olarak açılır.</param>
/// <param name="query">
/// Sorguyu içeren <see cref="SqlQuery"/> nesnesi.</param>
/// <returns>
/// Sorgu sonuçlarına erişim sağlayan <see cref="IDataReader"/> nesnesi.</returns>
public static IDataReader ExecuteReader(IDbConnection connection, SqlQuery query)
{
return(ExecuteReader(connection, query.ToString(), query.Params));
}