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)); }