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  ASC )  AS  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

 

上一篇  下一篇