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

SelectQueryBuilder 寫法對比

var q = new SelectQueryBuilder();
q.SelectFromTable("Customers");
q.SelectAllColumns();
q.TopRecords = maxRecords;

if (!string.IsNullOrEmpty(txtCompany.Text))
    q.AddWhere("CompanyName", Comparison.Like, txtCompany.Text + "%");
if (!string.IsNullOrEmpty(txtCity.Text))
    q.AddWhere("City", Comparison.Like, txtCity.Text + "%");
if (cboCountry.SelectedItem != null)
    q.AddWhere("Country", Comparison.Equals, cboCountry.SelectedItem);

string sql = q.BuildQuery();
// 更安全：產生參數化 DbCommand
q.SetDbProviderFactory(DbProviderFactories.GetFactory("System.Data.SqlClient"));
DbCommand cmd = q.BuildCommand();

優點：直觀、免拼字串、自動參數化防 SQL Injection。

使用 SQL 函數

q.AddWhere("OrderDate", Comparison.LessOrEquals, new SqlLiteral("getDate()"));

SqlLiteral 讓函數呼叫不被加引號，生成 OrderDate <= getDate()。

JOIN 查詢

q.AddJoin(JoinType.InnerJoin, "Customers", "CustomerID", Comparison.Equals, "Orders", "CustomerID");
q.AddWhere("Customers.City", Comparison.Equals, "London");

產出：

SELECT Orders.*
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE (Customers.City = 'London')

若需連接表欄位，需顯式 q.SelectColumns("Orders.*", "Customers.*")。