public void Select_BetweenAnd() { SubSonic.SqlQuery q = new Select("productid").From(Product.Schema).Where("productid").IsBetweenAnd(2, 5); string sql = q.BuildSqlStatement(); Assert.AreEqual( "SELECT [dbo].[Products].[ProductID]\r\n FROM [dbo].[Products]\r\n WHERE [dbo].[Products].[ProductID] BETWEEN @ProductID0_start AND @ProductID0_end\r\n", sql); }
public void Join_Unequal() { SubSonic.SqlQuery q = new Select("productID", "categoryName") .From(Product.Schema) .NotEqualJoin(Category.Schema); string sql = q.BuildSqlStatement(); Assert.AreEqual( "SELECT [dbo].[Products].[ProductID], [dbo].[Categories].[CategoryName]\r\n FROM [dbo].[Products]\r\n JOIN [dbo].[Categories] ON [dbo].[Products].[CategoryID] <> [dbo].[Categories].[CategoryID]\r\n", sql); }
public void Join_RightOuter() { SubSonic.SqlQuery q = new Select("productID", "categoryName") .From(Product.Schema) .RightOuterJoin(Category.Schema); string sql = q.BuildSqlStatement(); Assert.AreEqual( "SELECT [dbo].[Products].[ProductID], [dbo].[Categories].[CategoryName]\r\n FROM [dbo].[Products]\r\n RIGHT OUTER JOIN [dbo].[Categories] ON [dbo].[Products].[CategoryID] = [dbo].[Categories].[CategoryID]\r\n", sql); }
public void Aggregate_Count() { SubSonic.SqlQuery q = new Select( Aggregate.Count("ProductID"), Aggregate.Sum("UnitPrice", "boots"), Aggregate.GroupBy("categoryID")) .From("Products").Where("CategoryID").IsGreaterThan(5) .OrderAsc("categoryID").OrderDesc("boots"); string sql = q.BuildSqlStatement(); Assert.AreEqual( "SELECT COUNT(ProductID) AS 'CountOfProductID', SUM(UnitPrice) AS 'boots', categoryID AS 'GroupByOfcategoryID'\r\n FROM [dbo].[Products]\r\n WHERE [dbo].[Products].[CategoryID] > @CategoryID0\r\n GROUP BY categoryID\r\n ORDER BY categoryID ASC,boots DESC\r\n", sql); }
public void Acc_Exec_SimpleAnd3() { DataProvider provider = DataService.GetInstance("NorthwindAccess"); SubSonic.SqlQuery query = new Select(provider, Aggregate.GroupBy("ProductID"), Aggregate.Avg("UnitPrice"), Aggregate.Avg("Quantity")) .From("Order Details"); query .Where(Aggregate.Avg("UnitPrice")) .IsGreaterThan(30) .And(Aggregate.Avg("Quantity")) .IsGreaterThan(20); int records = query.GetRecordCount(); string s = query.BuildSqlStatement(); Assert.AreEqual(17, records); }
public void Acc_Exec_SimpleAnd3() { DataProvider provider = DataService.GetInstance("NorthwindAccess"); SubSonic.SqlQuery query = new Select(provider, Aggregate.GroupBy("ProductID"), Aggregate.Avg("UnitPrice"), Aggregate.Avg("Quantity")) .From("Order Details"); query .Where(Aggregate.Avg("UnitPrice")) .IsGreaterThan(30) .And(Aggregate.Avg("Quantity")) .IsGreaterThan(20); int records = query.GetRecordCount(); string s = query.BuildSqlStatement(); Assert.AreEqual(17, records); }