public void Acc_Multiple_Providers_Should_Return_Correct_ProviderName()
{
SubSonic.SqlQuery query1 = DB.Select();
SubSonic.SqlQuery query2 = Southwind.DB.Select();
Assert.AreEqual("NorthwindAccess", query1.ProviderName);
Assert.AreEqual("Southwind", query2.ProviderName);
}
public void Exec_NotInWithSelect()
{
// "verbose" style
SubSonic.SqlQuery query1 = DB.Select()
.From(Category.Schema)
.Where(Category.Columns.CategoryID)
.NotIn(
DB.Select(Product.Columns.CategoryID)
.From(Product.Schema)
);
// "generics" style
SubSonic.SqlQuery query2 = Select.AllColumnsFrom <Category>()
.Where(Category.Columns.CategoryID)
.NotIn(
new Select(Product.Columns.CategoryID)
.From(Product.Schema)
);
// do both produce the same sql?
string sql1 = query1.ToString();
string sql2 = query2.ToString();
Assert.AreEqual(sql1, sql2);
// does the sql work?
int records = query1.GetRecordCount();
Assert.IsTrue(records == 0);
}
public void Exec_PagedSimple()
{
SubSonic.SqlQuery q = Select.AllColumnsFrom <Product>().Paged(1, 20).Where("productid").IsLessThan(100);
int records = q.GetRecordCount();
Assert.IsTrue(records == 20);
}
public void Where_Simple()
{
SubSonic.SqlQuery qry = Select.AllColumnsFrom <Product>().Where("productID").IsGreaterThan(5);
ANSISqlGenerator gen = new ANSISqlGenerator(qry);
string w = gen.GenerateWhere();
Assert.AreEqual(" WHERE [dbo].[Products].[ProductID] > @ProductID0\r\n", w);
}
public void Exec_Select_Top_Ten()
{
SubSonic.SqlQuery query = DB.Select().Top("10")
.From("Customers");
int records = query.ExecuteTypedList <Customer>().Count;
Assert.AreEqual(10, records);
}
public void Exec_LeftOuterJoin_With_String()
{
SubSonic.SqlQuery query = DB.Select(Aggregate.GroupBy("CompanyName"))
.From("Customers")
.LeftOuterJoin("Orders");
int records = query.GetRecordCount();
Assert.AreEqual(91, records);
}
public void Exec_LeftOuterJoin_With_TableColumn()
{
SubSonic.SqlQuery query = DB.Select(Aggregate.GroupBy("CompanyName"))
.From(Customer.Schema)
.LeftOuterJoin(Order.CustomerIDColumn, Customer.CustomerIDColumn);
int records = query.GetRecordCount();
Assert.AreEqual(91, records);
}
public void Acc_Exec_LeftOuterJoin_With_Generics()
{
SubSonic.SqlQuery query = DB.Select(Aggregate.GroupBy("CompanyName"))
.From <Customer>()
.LeftOuterJoin <Order>();
int records = query.GetRecordCount();
Assert.AreEqual(91, records);
}
public void Where_Expression()
{
SubSonic.SqlQuery q = Select.AllColumnsFrom <Product>()
.WhereExpression("categoryID").IsLessThan(5).And("ProductID").IsGreaterThan(3).CloseExpression()
.OrExpression("categoryID").IsGreaterThan(8).And("productID").IsLessThan(2).CloseExpression();
string sql = q.ToString();
Assert.AreEqual(
"SELECT [dbo].[Products].[ProductID], [dbo].[Products].[ProductName], [dbo].[Products].[SupplierID], [dbo].[Products].[CategoryID], [dbo].[Products].[QuantityPerUnit], [dbo].[Products].[UnitPrice], [dbo].[Products].[UnitsInStock], [dbo].[Products].[UnitsOnOrder], [dbo].[Products].[ReorderLevel], [dbo].[Products].[Discontinued], [dbo].[Products].[AttributeXML], [dbo].[Products].[DateCreated], [dbo].[Products].[ProductGUID], [dbo].[Products].[CreatedOn], [dbo].[Products].[CreatedBy], [dbo].[Products].[ModifiedOn], [dbo].[Products].[ModifiedBy], [dbo].[Products].[Deleted]\r\n\r\n FROM [dbo].[Products]\r\n WHERE ([dbo].[Products].[CategoryID] < @CategoryID0\r\n AND [dbo].[Products].[ProductID] > @ProductID1\r\n)\r\n OR ([dbo].[Products].[CategoryID] > @CategoryID3\r\n AND [dbo].[Products].[ProductID] < @ProductID4\r\n)\r\n",
sql);
}
public void Select_Generate_JoinList()
{
SubSonic.SqlQuery qry = Select.AllColumnsFrom <Product>()
.InnerJoin(Category.Schema)
.InnerJoin(Supplier.Schema);
ANSISqlGenerator gen = new ANSISqlGenerator(qry);
string joins = gen.GenerateJoins();
Assert.AreEqual(
" INNER JOIN [dbo].[Categories] ON [dbo].[Products].[CategoryID] = [dbo].[Categories].[CategoryID]\r\n INNER JOIN [dbo].[Suppliers] ON [dbo].[Products].[SupplierID] = [dbo].[Suppliers].[SupplierID]\r\n",
joins);
}
public void Select_Paged_Can_Covert_To_SqlQuery()
{
bool threw = false;
try {
SubSonic.SqlQuery q2 = new SubSonic.SqlQuery().From(Product.Schema)
.InnerJoin(Category.Schema)
.Where("productid").IsLessThan(10)
.Paged(1, 20);
ProductCollection pc2 =
q2.ExecuteAsCollection <ProductCollection>();
} catch {
//nada
threw = true;
}
Assert.IsFalse(threw);
}
public void Select_Paged()
{
SubSonic.SqlQuery q = Select.AllColumnsFrom <Product>()
.InnerJoin(Category.Schema)
.Where("productid").IsLessThan(10)
.OrderAsc("productid")
.Paged(1, 20);
ProductCollection pc = q.ExecuteAsCollection <ProductCollection>();
Assert.GreaterThanOrEqualTo(pc.Count, 1);
SubSonic.SqlQuery q2 = new SubSonic.SqlQuery().From(Product.Schema)
.InnerJoin(Category.Schema)
.Where("productid").IsLessThan(10)
.Paged(1, 20);
ProductCollection pc2 = q2.ExecuteAsCollection <ProductCollection>();
Assert.GreaterThanOrEqualTo(pc2.Count, 1);
}
public void Select_IN()
{
SubSonic.SqlQuery q = Select.AllColumnsFrom <Product>()
.Where("productid").In(1, 2, 3, 4, 5);
string sql = q.BuildSqlStatement();
}
public void Select_Paged()
{
SubSonic.SqlQuery q = Select.AllColumnsFrom<Product>()
.InnerJoin(Category.Schema)
.Where("productid").IsLessThan(10)
.OrderAsc("productid")
.Paged(1, 20);
ProductCollection pc = q.ExecuteAsCollection<ProductCollection>();
Assert.GreaterEqualThan(pc.Count, 1);
SubSonic.SqlQuery q2 = new SubSonic.SqlQuery().From(Product.Schema)
.InnerJoin(Category.Schema)
.Where("productid").IsLessThan(10)
.Paged(1, 20);
ProductCollection pc2 = q2.ExecuteAsCollection<ProductCollection>();
Assert.GreaterEqualThan(pc2.Count, 1);
}
public void Acc_Select_Paged_Can_Covert_To_SqlQuery()
{
bool threw = false;
try {
SubSonic.SqlQuery q2 = new SubSonic.SqlQuery().From(Product.Schema)
.InnerJoin(Category.Schema)
.Where("productid").IsLessThan(10)
.Paged(1, 20);
ProductCollection pc2 =
q2.ExecuteAsCollection<ProductCollection>();
} catch {
//nada
threw = true;
}
Assert.IsFalse(threw);
}
