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
上一篇 下一篇