NQL.NET 数据库对象查询语言简介 2

      17. 比DataSet更简单的分页操作,并且性能也大大增强了,CurrentPageIndex = 1,PageSize = 5

this.dataGrid1.DataSource = new Query("Customers")
                .Where(Ex.Like(
"ContactTitle","Sales%"))
                .GetDataTable(
1,5);

SQL  输出

SELECT TOP 5 *  FROM ( SELECT  * 
FROM Customers 
WHERE [ContactTitle]  LIKE @ContactTitle ) AS TMP 
WHERE TMP.CustomerID > (SELECT MAX(TMP3.CustomerID) FROM ( SELECT TOP 5 TMP2.CustomerID FROM ( SELECT  * 
FROM Customers 
WHERE [ContactTitle]  LIKE @ContactTitle ) AS TMP2 ORDER BY TMP2.CustomerID ASCAS TMP3) ORDER BY TMP.CustomerID ASC

18。在计算列上可以进行+,-,*,/,%,++,--操作,在条件表达式上不仅可以进行算术运算而且可以进行逻辑运算 &&,||,! 以及进行比较运算==,!=,>,>=,<,<=,还可以进行范围运算%,in,between等运算

18.1 算术运算和比较运算

Query q = new Query("Products");
 Condition con 
= q.NewCondition("UnitPrice"* q.NewCondition("UnitsInStock"<=1000;
 
this.dataGrid1.DataSource = con.GetDataTable();

SQL  输出

SELECT  * 
FROM Products 
WHERE 
    
[UnitPrice] * [UnitsInStock] <= @UnitPrice

18.2 逻辑and 和范围Between

Query q = new Query("Products");
 ICondition con 
= q.NewCondition("UnitPrice"* q.NewCondition("UnitsInStock"<= 1000 
                
&& q.NewCondition("SupplierID").Between(1,5);
this.dataGrid1.DataSource = con.GetDataTable();

SQL  输出

SELECT  * 
FROM Products 
WHERE 
    
[UnitPrice] * [UnitsInStock] <= @UnitPrice
    
AND [SupplierID] BETWEEN @SupplierID AND @_Another_SupplierID

18.3 模糊查询%

Query q = new Query("Customers");
 
this.dataGrid1.DataSource = q.Where(q.NewCondition("ContactTitle"% "Sales%").GetDataTable();

SQL  输出

SELECT  * 
FROM Customers 
WHERE 
    
[ContactTitle] LIKE @ContactTitle

18.4 范围运算IN

Query qryCustomer = new Query("Customers")
                ._(
"CustomerID")
                .Where(
"ContactTitle","Owner");

Query qryOrder 
= new Query("Orders");
qryOrder.Where(    qryOrder.NewCondition(
"ShipVia"== 1 && qryOrder.NewCondition("CustomerID").In(qryCustomer));
 
this.dataGrid1.DataSource = qryOrder.GetDataTable();

SQL  输出

SELECT  * 
FROM Orders 
WHERE 
    
[ShipVia] = @ShipVia
    
AND [CustomerID] IN (SELECT 
                                                        
[CustomerID]
                             
FROM Customers 
                             
WHERE [ContactTitle]  = @ContactTitle )

18.5 复杂一点的条件

Query q = new Query("Products");
 Condition con 
= q.NewCondition("UnitPrice">= 50 
                
&& (
                            q.NewCondition("UnitsInStock ") <= 20 
                          
|| q.NewCondition("UnitsOnOrder"> 0
                            )
                
&& q.NewCondition("ProductID") >6;

this.dataGrid1.DataSource = q.Where(con).GetDataTable();

SQL  输出

SELECT  * 
FROM Products 
WHERE 
    
[UnitPrice] >= @UnitPrice
    
AND (
        
[UnitsInStock ] <= @UnitsInStock  OR [UnitsOnOrder] > @UnitsOnOrder
    ) 
    
AND [ProductID] > @ProductID

19 . 查询表达式另一种写法 算术运算 Add(+),Minus(-),Multiply(*),Divide(/),Mode (%),逻辑运算and,or,not

比较运算GreatThan(>,GT),GreaterEquals(>=,GE),Equal(==,EQ),LessEquals(<=,LE),Little(<,LT),NotEquals(!=,NE)

IsNull,IsNotNull,IgnoreCase(忽略大小写)等运算

Query q = new Query("Products");
Condition con 
= q.NewCondition("UnitPrice").GreaterEquals(50)
                .And
                  (
                         q.NewCondition(
"UnitsInStock ").LessEquals(20)
                         .Or
                              (
                                  q.NewCondition(
"UnitsOnOrder").GreaterThan(0)
                              )
                )
                .And(q.NewCondition(
"ProductID").GT(6));
            
this.dataGrid1.DataSource = q.Where(con).GetDataTable();

SQL  输出

SELECT  * 
FROM Products 
WHERE 
    
[UnitPrice] >= @UnitPrice
    
AND (
        
[UnitsInStock ] <= @UnitsInStock  OR [UnitsOnOrder] > @UnitsOnOrder
    ) 
    
AND [ProductID] > @ProductID

20. Case When 语句的用法

Query q = new Query("Orders");
 
this.dataGrid1.DataSource = q.Select("OrderID",
                      "ShipCountry",
                    q.Case(
"ShipCountry")
                        .When(
"France").Then("fc")
                        .When(
"USA").Then("us")
                        .When(
"German").Then("gm")
                        .Else(
"com")
                        .End().As(
"SC"),
                    
"OrderDate")
                .GetDataTable();

SQL  输出

SELECT 
    
[OrderID],
    
[ShipCountry],
    
CASE [ShipCountry]
        
WHEN 'France' THEN 'fc'
        
WHEN 'USA' THEN 'us'
        
WHEN 'German' THEN 'gm'
        
ELSE 'com'
        
END AS SC,
    
[OrderDate]
FROM Orders 

21 统计函数(Count,Avg,Max,Min,Sum)的用法

21.1 Count

Query q = new Query("Products").Count("ProductID");
            Condition con 
= q.NewCondition("UnitPrice"* q.NewCondition("UnitsInStock"<=1000;
            
this.richTextBox1.Text = con.GetScalar().ToString();

SQL  输出

SELECT 
    
COUNT([Products].[ProductID])
FROM Products 
WHERE 
    
[UnitPrice] * [UnitsInStock] <= @UnitPrice

21.2 Avg,其它的Max,Min,Sum用法一样

Query q = new Query("Products").Avg("ProductID");
            Condition con 
= q.NewCondition("UnitPrice"* q.NewCondition("UnitsInStock"<=1000;
            
this.richTextBox1.Text = con.GetScalar().ToString();

SQL 输出

SELECT 
    
AVG([Products].[ProductID])
FROM Products 
WHERE 
    
[UnitPrice] * [UnitsInStock] <= @UnitPrice

22 GroupBy的用法

Query q = new Query("Orders")
                .Select(
"ShipVia","ShipRegion")
                .GroupBy(
"ShipVia","ShipRegion");
            
this.dataGrid1.DataSource = q.GetDataTable();

SQL  输出

SELECT 
    
[ShipVia],
    
[ShipRegion]
FROM Orders 
 
GROUP BY ShipVia,ShipRegion

 

上一篇  下一篇

posted @ 2006-10-29 12:30  风云  阅读(...)  评论(...编辑  收藏