SelectQueryBuilder類允許在你的代碼中建立復雜的SQL語句和命令。它也能幫助於避免SQL注入式攻擊。
介紹
承認，並且我們都這樣作過，也認為下面的方式是最好的和唯一的方式。就是我們建立大量的字符串包含所有的Where子句，然後提交到數據庫去執行它。來斷的加語句到我們的SQL字符串，極有可能會帶來Bugs和SQL注入式攻擊的危險。並且也使得我們的代碼更難看也不易於管理。
這種情況必須停止，但如何停止?有人說使用存儲過程。但它並沒有真正的解決這個問題。你還得動態建立你的SQL語句，只不過有問題移到數據庫層面上了，依然有SQL注入的危險。除了這個“解決方案”外，可能還有非常多的選擇供你考慮，但它們都會帶來一個基本的挑戰:讓SQL語句工作的更好、更安全。
當我從我的在線DAL(數據訪問層)生成工具http://www.code-engine.com/建立C#模板時，我想提供一個易於使用的方法來定制查詢數據。我不再想使用“字符串查詢”(我以前開發的模板)來查詢數據。我厭煩這種凌亂的方式來得到數據。我想用一種清晰的、直覺的、靈活的、簡單的方式從表中選擇數據，聯接一些別的語句，使用大量的Where子句，用一些列來分組數據，返回前X個記錄。
我開始開發所想的有這種嚴密功能的SelectQueryBuilder類。它暴露了許多屬性和方法，你能很容易地在Select語句中使用它們。一旦調用BuildQuery()和BuildCommand()方法，它能提供一種更好的舊的“字符串查詢“或可以使用命令參數的DbCommand對象來查詢數據。
使用代碼
舊的方式的代碼
下面的代碼闡明了以前建立SELECT語句的方法，它使用許多類變量來說明應該使用那種連接操作(WHERE，或者OR),同時也給你的數據庫帶來了可能的SQL注入式攻擊。
string statement = "SELECT TOP " + maxRecords + " * FROM Customers ";
string whereConcatenator = "WHERE ";
if (companyNameTextBox.Text.Length > 0)
{
statement += whereConcatenator;
statement += "CompanyName like '" + companyNameTextBox.Text + "%' ";
whereConcatenator = "AND ";
}
if (cityTextBox.Text.Length > 0)
{
statement += whereConcatenator;
statement += "City like '" + cityTextBox.Text + "%' ";
whereConcatenator = "AND ";
}
if (countryComboBox.SelectedItem != null)
{
statement += whereConcatenator;
statement += "Country = '" + countryComboBox.SelectedItem + "' ";
whereConcatenator = "AND ";
}
我相信上面的代碼對你來說是非常熟悉的，你可能在過去的十多年一直是這樣使用的，或者你曾經編碼過數據庫驅動的搜索功能。讓我告訴你這種思想:這種查詢你的數據庫的方法不能再使用了，它是難看的也是不安全的。
SelectQueryBuilder方式的代碼
同樣的查詢能夠使用SelectQueryBuilder類建立。
SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable("Customers");
query.SelectAllColumns();
query.TopRecords = maxRecords;
if (companyNameTextBox.Text.Length > 0)
query.AddWhere("CompanyName", Comparison.Like,companyNameTextBox.Text + "%");
if (cityTextBox.Text.Length > 0)
query.AddWhere("City", Comparison.Like,
cityTextBox.Text + "%");
if (countryComboBox.SelectedItem != null)
query.AddWhere("Country", Comparison.Equals,
countryComboBox.SelectedItem);
string statement = query.BuildQuery();
// or, have a DbCommand object built
// for even more safety against SQL Injection attacks:
query.SetDbProviderFactory(
DbProviderFactories.GetFactory(
"System.Data.SqlClient"));
DbCommand command = query.BuildCommand();
你能看到，這種方式比直接使用連接字符串更直觀。考慮到第一個例子SQL注入的危險，通過SelectQueryBuilder建立的SELECT查詢是非常安全的，並不用擔心使用的TextBoxs中的內容。事實上它也非常簡單!
使用SQL函數
如果你想在你的查詢中使用SQL函數，你能使用SqlLiteral類來打包函數的調用。說明這個類能作什麼的最好方式就是給你顯示一小段代碼例子:
SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable("Orders");
query.AddWhere("OrderDate", Comparison.LessOrEquals,new SqlLiteral("getDate()"));
如果我們沒有打包getDate()函數調用到SqlLiteral類中，建立的查詢就會產生WHERE子句:OrderDate<=’getDate()’。當然，我們希望在語句中的這個函數沒有被單引號包圍。這時SqlLiteral就可以派上用場了:它直接拷貝字符串到輸出，並沒有把它格式化成字符串。現在的輸出WHERE子句應當是OrderDate<=getDate()!
查詢中使用JOINs
要創建到其它表的JOINs，你能使用AddJoin方法。下面的代碼顯示了如何創建一個從Ordres表到Customers表的INNER JOIN。
SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable("Orders");
query.AddJoin(JoinType.InnerJoin,"Customers", "CustomerID",Comparison.Equals,"Orders", "CustomerID");
query.AddWhere("Customers.City",Comparison.Equals, "London");
這段代碼選擇所有居住在London的客戶的訂單。一旦調用了BuildQuery方法，就會產生下面的SQL語句:
SELECT Orders.*
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE (Customers.City = 'London')
注意到缺省的查詢只會建立所選擇的表的selects * 語句(這個例子中的Orders.*)。如果你也想選擇連接表的列的話，你必須得顯式地選擇它們。你能通用調用query.SelectColumns(“Orders.*”,”Customers.*”)。
建立計算查詢
如果你想對你的數據庫執行一個計算查詢。你能使用SelectCount方法如同下面顯示的:
Query.SelectCount();
在更加復雜的計算查詢中，你可能想使用GROUP BY語句。看一下下面的例子，它顯示了如何使用GroupBy和AddHaving方法。
SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectColumns("count(*) AS Count", "ShipCity");
query.SelectFromTable("Orders");
query.GroupBy("ShipCity");
query.AddHaving("ShipCity", Comparison.NotEquals, "Amsterdam");
query.AddOrderBy("count(*)", Sorting.Descending);
上面的代碼選擇了每個城市的訂單數，並用訂單數目排序，不考慮來自制Amsterdam的訂單，BuildQuery方法的輸出結果應當是:
SELECT count(*) AS Count, ShipCity
FROM Orders
GROUP BY ShipCity
HAVING (ShipCity <> 'Amsterdam')
ORDER BY count(*) DESC
復雜的Where語句
如果你曾經用過微軟的Access或SQL Server的內置的查詢生成器的話，是否驚訝你能建立和代碼一樣的包含多層ANDs和Ors，並沒有關心()符號的位置的查詢?是的?我也能!
你能使用SelectQueryBuilder類實現!你能加多層的WHERE語句到你的查詢。缺省，所有對query.AddWhere的調用被放在查詢的第一層上。你可以把它比作SQL Server查詢生成器的’Criteria’列;第二、三、四層等相應地對應於’Or…’列。
看一下下面的SQL Server查詢生成器的快照，通過它我能快速地把簡單的假的SELECT語句放在一起來:
如你看到的，我創建一個查詢，它選擇所有在1-1-2005日期之前的客戶’VINET’的訂單，和所有30-6-2004日期之前或1-1-2006日期之後的客戶’TOMSP’的訂單(請不要問為什麼有人想查詢某個人的訂單，這僅僅是一個 例子)。這個查詢能夠建立:
SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable("Orders");
// Add 'Criteria' column to level 1
query.AddWhere("CustomerID", Comparison.Equals,"VINET", 1);
query.AddWhere("OrderDate", Comparison.LessThan,new DateTime(2005,1,1), 1);
// Add first 'Or...' column to level 2
query.AddWhere("CustomerID", Comparison.Equals, "TOMSP", 2);
query.AddWhere("OrderDate", Comparison.LessThan,new DateTime(2004,6,30), 2);
// Add second 'Or...' column to level 3
query.AddWhere("CustomerID", Comparison.Equals,"TOMSP", 3);
query.AddWhere("OrderDate", Comparison.GreaterThan,new DateTime(2006,1,1), 3);
當調用 BuildQuery時，所有定義的層將被OR到一起，幾乎和SQL Server生成的一樣。
如果你到所產生的語句接近一樣時，想讓查詢更復雜，你可能會說“我的放兩個隨後的語句一起放在一個語句中，在兩個日期間使用OR”。你能夠這樣作。在SQL Server查詢生成器中，這個查詢看起來像:
同樣，它也可能使用SelectQueryBuilder通過創建’嵌套的WHERE子句’來實現。
SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable("Orders");
// Add 'Criteria' column to level 1
query.AddWhere("CustomerID", Comparison.Equals, "VINET", 1);
query.AddWhere("OrderDate", Comparison.LessThan,
new DateTime(2005,1,1), 1);
// Add 'Or...' column to level 2
query.AddWhere("CustomerID",
Comparison.Equals, "TOMSP", 2);
// Add the date selection clause
WhereClause clause =query.AddWhere("OrderDate", Comparison.LessThan,
new DateTime(2004,6,30), 2);
// Add a nested clause to the captured clause
clause.AddClause(LogicOperator.Or,
Comparison.GreaterThan, new DateTime(2006,1,1));
注意到我用了一個WhereClause對象，它由AddWhere調用返回。接著調用clause.AddClause創建一個嵌套的子句柄，並且選擇通過指定LogicOperator.Or來把它OR到第一個子句上。所產生的語句如下:
SELECT Orders.*
FROM Orders
WHERE
(
(CustomerID = 'VINET')
AND (OrderDate < '2005/01/01 12:00:00')
)
OR
(
(CustomerID = 'TOMSP')
AND (OrderDate < '2004/06/30 12:00:00' OR
OrderDate > '2006/01/01 12:00:00')
)
請注意這個例子中日期包含’12:00:00’，這是因為我在DateTime構造體中忽略了時間。但這只要由於我的習慣。如果我使用new DateTime(2006,1,1,0,0,0)，日期字符串將包含’00:00:00’。
結論
在介紹中我就提到，SelectQueryBuilder是CodeEngine框架的一部分。這個框架 也包含了DeleteQueryBuilder，UpdateQueryBuilder，InsertQueryBuilder。我在通過我的C#DAL產生器生成的代碼中使用這些生成器。你能從www.code-engine.com上下載一份 框架DLL的拷貝。在這期間我也將發布其它的查詢生成器的源代碼。同時如果你有什麼問題，評價或建議，請及時與我聯系。
