Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
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);
        }
Exemplo n.º 3
0
        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);
        }
Exemplo n.º 6
0
        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();
        }
Exemplo n.º 7
0
        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);
        }
Exemplo n.º 8
0
        public void Exec_Collection_Joined()
        {
            ProductCollection p = Select.AllColumnsFrom <Product>()
                                  .InnerJoin(Category.Schema)
                                  .Where("CategoryName").IsEqualTo("Beverages")
                                  .ExecuteAsCollection <ProductCollection>();

            Assert.IsTrue(p.Count == 12);
        }
Exemplo n.º 9
0
        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);
        }
Exemplo n.º 10
0
        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);
        }
Exemplo n.º 11
0
        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);
        }
Exemplo n.º 12
0
        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);
        }
Exemplo n.º 13
0
        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);
        }
Exemplo n.º 14
0
        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);
        }
Exemplo n.º 15
0
        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);
        }
Exemplo n.º 16
0
 public void Select_IN()
 {
     SubSonic.SqlQuery q = Select.AllColumnsFrom <Product>()
                           .Where("productid").In(1, 2, 3, 4, 5);
     string sql = q.BuildSqlStatement();
 }