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);
        }
Exemplo n.º 5
0
        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);
        }
Exemplo n.º 6
0
        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);
        }