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 Exec_Collection_Simple()
{
ProductCollection p = Select.AllColumnsFrom <Product>()
.ExecuteAsCollection <ProductCollection>();
Assert.IsTrue(p.Count == 77);
}
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 Select_Generate_FromList()
{
Select qry = Select.AllColumnsFrom <Product>();
ANSISqlGenerator gen = new ANSISqlGenerator(qry);
string from = gen.GenerateFromList();
Assert.AreEqual(" FROM [dbo].[Products]\r\n", from);
}
public IList <ReviewDesc> GetAllReviewsPaged(int pageIndex, int pageSize)
{
SqlQuery q = Select.AllColumnsFrom <VwAllReview>().OrderDesc("ReviewDate", "PhaseStartDate", "DeadlineDate").Paged(pageIndex, pageSize);
int count = q.GetRecordCount();
return(q.ExecuteTypedList <ReviewDesc>());
throw new NotImplementedException();
}
public void Exec_NestedExpression()
{
ProductCollection products = Select.AllColumnsFrom <Product>()
.WhereExpression("categoryID").IsEqualTo(5).And("productid").IsGreaterThan(10)
.Or("categoryID").IsEqualTo(2).AndExpression("productID").IsBetweenAnd(2, 5)
.ExecuteAsCollection <ProductCollection>();
Assert.IsTrue(products.Count == 3);
}
public void Exec_Collection_Joined()
{
ProductCollection p = Select.AllColumnsFrom <Product>()
.InnerJoin(Category.Schema)
.Where("CategoryName").IsEqualTo("Beverages")
.ExecuteAsCollection <ProductCollection>();
Assert.IsTrue(p.Count == 12);
}
public void Insert_SimpleWithSelectAndSchema()
{
Insert i = new Insert().Into(Category.Schema)
.Select(Select.AllColumnsFrom <Category>());
string sql = i.BuildSqlStatement();
Assert.AreEqual(
"INSERT INTO [dbo].[Categories](CategoryName,Description,Picture)\r\nSELECT [dbo].[Categories].[CategoryName], [dbo].[Categories].[Description], [dbo].[Categories].[Picture]\r\n FROM [dbo].[Categories]\r\n\r\n",
sql);
}
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 Exec_InWithSelect()
{
int records = Select.AllColumnsFrom <Product>()
.Where("productid")
.In(
new Select("productid").From(Product.Schema)
.Where("categoryid").IsEqualTo(5)
)
.GetRecordCount();
Assert.IsTrue(records == 7);
}
public void testPaging2()
{
// SqlQuery q = Select.AllColumnsFrom<MeetingLib>().Paged(1, 5);
ITaxonRepository taxons = new TaxonRepository();
//int count = taxons.GetAllReviewsPaged(1, 5).Count;
SqlQuery q = Select.AllColumnsFrom <VwAllReview>().Paged(1, 6);
int records = q.GetRecordCount();
Assert.AreEqual(records, 10);
}
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);
}
private IList <ReviewDesc> GetAllReviews(int?pageIndex)
{
ITaxonRepository Taxon = new TaxonRepository();
IReviewRepository reviews = new ReviewRepository();
// IList<ReviewDesc> review = Taxon.GetAllReviews();
int currentIndex = (int)(pageIndex ?? 1);
//int count = review.Count;
// int count = Taxon.getAllReviews().Count;
SqlQuery query = Select.AllColumnsFrom <VwAllReview>();
int count = query.GetRecordCount();
ViewData["TotalItemCount"] = count;
ViewData["PageNumber"] = currentIndex;
ViewData["PageSize"] = UpdateUtils.DEFAULT_PAGE_SIZE;
IList <ReviewDesc> review = Taxon.GetAllReviewsPaged(currentIndex, UpdateUtils.DEFAULT_PAGE_SIZE);
var sortedReviews = from r in review
orderby r.CtyShort
select r;
int pagedCount = review.Count;
if (pagedCount > 0)
{
for (int i = 0; i < pagedCount; i++)
{
review[i].TaxonName = Taxon.getReviewTaxonName(review[i].ID, review[i].Taxontype,
review[i].Kingdom);
review[i].Paragraph = Taxon.getParagraphStagePerReview(review[i].ID);
review[i].Concern = reviews.getConcernForReview(review[i].ID);
review[i].DeadlineDate = Taxon.getDeadlineDatePerReview(review[i].ID);
}
}
if (pagedCount <= 0)
{
review = null;
}
return(review);
}
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();
}
