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