C# 建立複雜靈活 SQL 查詢實戰（四）

巢狀 WHERE 子句建構

var query = new SelectQueryBuilder();
query.SelectFromTable("Orders");

// 第 1 層：Criteria
query.AddWhere("CustomerID", Comparison.Equals, "VINET", 1);
query.AddWhere("OrderDate", Comparison.LessThan, new DateTime(2005,1,1), 1);

// 第 2 層：Or...
query.AddWhere("CustomerID", Comparison.Equals, "TOMSP", 2);

// 第 2 層日期條件並捕獲子句物件
var clause = query.AddWhere("OrderDate", Comparison.LessThan, new DateTime(2004,6,30), 2);

// 在捕獲子句下再建立巢狀 OR 條件
clause.AddClause(LogicOperator.Or, Comparison.GreaterThan, new DateTime(2006,1,1));

產出 SQL

SELECT Orders.*
FROM Orders
WHERE
(
  (CustomerID = 'VINET') AND (OrderDate < '2005/01/01')
)
OR
(
  (CustomerID = 'TOMSP') AND
  (OrderDate < '2004/06/30' OR OrderDate > '2006/01/01')
)

結語

SelectQueryBuilder 隸屬 CodeEngine 框架，同系列尚有 DeleteQueryBuilder、UpdateQueryBuilder、InsertQueryBuilder。框架 DLL 可至 code-engine.com 下載。