共計 12951 個字符，預計需要花費 33 分鐘才能閱讀完成。

CPQuery 中怎么拼接 SQL，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面丸趣 TV 小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

 CPQuery 是什么？看到博客的標題，你會不會想：CPQuery 是什么？下面是我的回答：1. CPQuery 是一個縮寫：Concat Parameterized Query 2. CPQuery 可以讓你繼續使用熟悉的拼接方式來寫參數化的 SQL 3. CPQuery 是我設計的一種解決方案，它可以解決拼接 SQL 的前二個缺點。4. CPQuery 也是這個解決方案中核心類型的名稱。希望大家能記住 CPQuery 這個名字。 

CPQuery 適合哪些人使用？答：適合于喜歡手寫 SQL 代碼的人，尤其是當需要寫動態查詢時。 

參數化的 SQL 語句 對于需要動態查詢的場景，我認為：拼接 SQL 或許是必需的，但是，你不要將數值也拼接到 SQL 語句中嘛，或者說，你應該拼接參數化的 SQL 來解決你遇到的問題。說到【拼接參數化 SQL】，我想解釋一下這個東西了。這個方法的實現方式是：拼接 SQL 語句時，不要把參數值拼接到 SQL 語句中，在 SQL 語句中使用占位符參數，具體的參數值通過 ADO.NET 的 command.Parameters.Add() 傳入。現在流行的 ORM 工具應該都會采用這個方法。我認為參數化的 SQL 語句可以解決本文開頭所說的那些問題，尤其是前二個。對于代碼的維護問題，我的觀點是：如果你硬是將 SQL 與 C# 混在一起，那么參數化的 SQL 語句也是沒有辦法的。如果想解決這個問題，你需要將 SQL 語句與項目代碼分離，然后可以選擇以配置文件或者存儲過程做為保存那些 SLQ 語句的容器。所以，參數化的 SQL 并不是萬能的，代碼的可維護性與技術的選擇無關，與架構的設計有關。任何優秀的技術都可能寫出難以維護的代碼來，這就是我的觀點。改造現有的拼接語句 還是說動態查詢，假設我有這樣一個查詢界面：

顯然，在設計程序時，不可能知道用戶會輸入什么樣的過濾條件。因此，喜歡手寫 SQL 的人們通常會這樣寫查詢：復制代碼 代碼如下:
var query = select ProductID, ProductName from Products where (1=1) if(p.ProductID 0) query = query + and ProductID = + p.ProductID.ToString(); if( string.IsNullOrEmpty(p.ProductName) == false ) query = query + and ProductName like + p.ProductName + if(p.CategoryID 0) query = query + and CategoryID = + p.CategoryID.ToString(); if( string.IsNullOrEmpty(p.Unit) == false ) query = query + and Unit = + p.Unit + if(p.UnitPrice 0) query = query + and UnitPrice = + p.UnitPrice.ToString(); if( p.Quantity 0) query = query + and Quantity = + p.Quantity.ToString();

如果使用這種方式，本文開頭所說的前二個缺點肯定是存在的。我想很多人應該是知道參數化查詢的，最終放棄或許有以下 2 個原因：1. 這種拼接 SQL 語句的方式很簡單，非常容易實現。2. 便于包裝自己的 API，參數只需要一個（萬能的）字符串！如果你認為這 2 個原因很難解決的話，那我今天就給你“一種改動極小卻可以解決上面二個缺點”的解決方案，改造后的代碼如下：復制代碼 代碼如下:
var query = select ProductID, ProductName from Products where (1=1) .AsCPQuery(true); if(p.ProductID 0) query = query + and ProductID = + p.ProductID.ToString(); if( string.IsNullOrEmpty(p.ProductName) == false ) query = query + and ProductName like + p.ProductName + if(p.CategoryID 0) query = query + and CategoryID = + p.CategoryID.ToString(); if( string.IsNullOrEmpty(p.Unit) == false ) query = query + and Unit = + p.Unit + if(p.UnitPrice 0) query = query + and UnitPrice = + p.UnitPrice.ToString(); if( p.Quantity 0) query = query + and Quantity = + p.Quantity.ToString();

你看到差別了嗎？差別在于第一行代碼，后面調用了一個擴展方法：AsCPQuery(true)，這個方法的實現代碼我后面再說。這個示例的主要關鍵代碼如下：復制代碼 代碼如下:
private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings[MyNorthwind_MSSQL].ConnectionString; private void btnQuery_Click(object sender, EventArgs e) {Product p = new Product(); p.ProductID = SafeParseInt(txtProductID.Text); p.ProductName = txtProductName.Text.Trim(); p.CategoryID = SafeParseInt(txtCategoryID.Text); p.Unit = txtUnit.Text.Trim(); p.UnitPrice = SafeParseDecimal(txtUnitPrice.Text); p.Quantity = SafeParseInt(txtQuantity.Text); var query = BuildDynamicQuery(p); try {txtOutput.Text = ExecuteQuery(query); } catch(Exception ex) {txtOutput.Text = ex.Message;} } private CPQuery BuildDynamicQuery(Product p) {var query = select ProductID, ProductName from Products where (1=1) .AsCPQuery(true); if(p.ProductID 0) query = query + and ProductID = + p.ProductID.ToString(); if( string.IsNullOrEmpty(p.ProductName) == false ) query = query + and ProductName like + p.ProductName + if(p.CategoryID 0) query = query + and CategoryID = + p.CategoryID.ToString(); if( string.IsNullOrEmpty(p.Unit) == false ) query = query + and Unit = + p.Unit + if(p.UnitPrice 0) query = query + and UnitPrice = + p.UnitPrice.ToString(); if( p.Quantity 0) query = query + and Quantity = + p.Quantity.ToString(); return query;} private string ExecuteQuery(CPQuery query) {StringBuilder sb = new StringBuilder(); using(SqlConnection connection = new SqlConnection(ConnectionString) ) {SqlCommand command = connection.CreateCommand(); // 將前面的拼接結果綁定到命令對象。query.BindToCommand(command); // 輸出調試信息。sb.AppendLine(================================================== sb.AppendLine(command.CommandText); foreach(SqlParameter p in command.Parameters) sb.AppendFormat({0} = {1}\r\n , p.ParameterName, p.Value); sb.AppendLine(==================================================\r\n // 打開連接，執行查詢 connection.Open(); SqlDataReader reader = command.ExecuteReader(); while( reader.Read() ) sb.AppendFormat({0}, {1}\r\n , reader[0], reader[1]); } return sb.ToString();} private int SafeParseInt(string s) {int result = 0; int.TryParse(s, out result); return result; } private decimal SafeParseDecimal(string s) {decimal result = 0m; decimal.TryParse(s, out result); return result; }

我們來看一下程序運行的結果：

根據前面給出的調試代碼：復制代碼 代碼如下:
// 輸出調試信息。sb.AppendLine(================================================== sb.AppendLine(command.CommandText); foreach(SqlParameter p in command.Parameters) sb.AppendFormat({0} = {1}\r\n , p.ParameterName, p.Value); sb.AppendLine(==================================================\r\n

以及圖片反映的事實，可以得出結論：改造后的查詢已經是參數化的查詢了！揭秘原因 是不是很神奇：加了一個 AsCPQuery() 的調用，就將原來的拼接 SQL 變成了參數化查詢？這其中的原因有以下幾點：1. AsCPQuery() 的調用產生了一個新的對象，它的類型不是 string，而是 CPQuery 2. 在每次執行 + 運算符時，已經不再是二個 string 對象的相加。3. CPQuery 重載了 + 運算符，會識別拼接過程中的參數值與 SQL 語句片段。4. 查詢構造完成后，得到的結果不再是一個字符串，而是一個 CPQuery 對象，它可以生成參數化的 SQL 語句，它還包含了所有的參數值。AsCPQuery() 是一個擴展方法，代碼：復制代碼 代碼如下:
public static CPQuery AsCPQuery(this string s) {return new CPQuery(s, false); } public static CPQuery AsCPQuery(this string s, bool autoDiscoverParameters) {return new CPQuery(s,autoDiscoverParameters); }

所以在調用后，會得到一個 CPQuery 對象。觀察前面的示例代碼，你會發現 AsCPQuery() 只需要調用一次。要得到一個 CPQuery 對象，也可以調用 CPQuery 類型的靜態方法：復制代碼 代碼如下:
public static CPQuery New() { return new CPQuery(null, false); } public static CPQuery New(bool autoDiscoverParameters) {return new CPQuery(null, autoDiscoverParameters); }

這二種方法是等效的，示例代碼：復制代碼 代碼如下:
// 下面二行代碼是等價的，可根據喜好選擇。var query = select ProductID, ProductName from Products where (1=1) .AsCPQuery(); //var query = CPQuery.New() + select ProductID, ProductName from Products where (1=1)

繼續看拼接的處理：復制代碼 代碼如下:
public static CPQuery operator +(CPQuery query, string s) {query.AddSqlText(s); return query; }

CPQuery 重載了 + 運算符，所以，結果已經不再是二個 string 對象的相加的結果，而是 CPQuery 對象本身（JQuery 的鏈接設計思想，便于繼續拼接）。思考一下：where id = + 234 + ………… 你認為我是不是可以判斷出 234 就是一個參數值？類似的還有：where name = + Fish Li + 顯然，Fish Li 就是表示一個字符串的參數值嘛，因為拼接的左右二邊都有 包圍著。所以，CPQuery 對象會識別拼接過程中的參數值與 SQL 語句片段。查詢拼接完成了，但是此時的 SQL 語句保存在 CPQuery 對象中，而且不可能通過一個字符串的方式返回，因為還可能包含多個查詢參數呢。所以，在執行查詢時，相關的方法需要能夠接收 CPQuery 對象，例如：復制代碼 代碼如下:
static string ExecuteQuery(CPQuery query) {StringBuilder sb = new StringBuilder(); using(SqlConnection connection = new SqlConnection(ConnectionString) ) {SqlCommand command = connection.CreateCommand(); // 將前面的拼接結果綁定到命令對象。query.BindToCommand(command);

一旦調用了 query.BindToCommand(command); CPQuery 對象會把它在內部拼接的參數化 SQL，以及收集的所有參數值賦值給 command 對象。后面的事情，該怎么做就怎么做吧，我想大家都會，就不再多說了。CPQuery 源碼 前面只貼出了 CPQuery 的部分代碼，這里給出相關的全部代碼：復制代碼 代碼如下:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Common; namespace CPQueryDEMO {public sealed class CPQuery { private enum SPStep // 字符串參數的處理進度 { NotSet, // 沒開始或者已完成一次字符串參數的拼接。EndWith, // 拼接時遇到一個單引號結束 Skip // 已跳過一次拼接} private int _count; private StringBuilder _sb = new StringBuilder(1024); private Dictionary string, QueryParameter _parameters = new Dictionary string, QueryParameter (10); private bool _autoDiscoverParameters; private SPStep _step = SPStep.NotSet; public CPQuery(string text, bool autoDiscoverParameters) {_sb.Append(text); _autoDiscoverParameters = autoDiscoverParameters; } public static CPQuery New() { return new CPQuery(null, false); } public static CPQuery New(bool autoDiscoverParameters) {return new CPQuery(null, autoDiscoverParameters); } public override string ToString() { return _sb.ToString(); } public void BindToCommand(DbCommand command) {if( command == null) throw new ArgumentNullException(command command.CommandText = _sb.ToString(); command.Parameters.Clear(); foreach( KeyValuePair string, QueryParameter kvp in _parameters) {DbParameter p = command.CreateParameter(); p.ParameterName = kvp.Key; p.Value = kvp.Value.Value; command.Parameters.Add(p); } } private void AddSqlText(string s) {if( string.IsNullOrEmpty(s) ) return; if(_autoDiscoverParameters) {if( _step == SPStep.NotSet) {if( s[s.Length – 1] == \ ) {// 遇到一個單引號結束 _sb.Append(s.Substring(0, s.Length – 1)); _step = SPStep.EndWith; } else {object val = TryGetValueFromString(s); if(val == null) _sb.Append(s); else this.AddParameter(val.AsQueryParameter()); } } else if(_step == SPStep.EndWith) {// 此時的 s 應該是字符串參數，不是 SQL 語句的一部分 // _step 在 AddParameter 方法中統一修改，防止中途拼接非字符串數據。this.AddParameter(s.AsQueryParameter()); } else {if( s[0] != \ ) throw new ArgumentException(正在等待以單引號開始的字符串，但參數不符合預期格式。// 找到單引號的閉合輸入。_sb.Append(s.Substring(1)); _step = SPStep.NotSet; } } else {// 不檢查單引號結尾的情況，此時認為一定是 SQL 語句的一部分。_sb.Append(s); } } private void AddParameter(QueryParameter p) {if( _autoDiscoverParameters _step == SPStep.Skip) throw new InvalidOperationException(正在等待以單引號開始的字符串，此時不允許再拼接其它參數。string name = @p + (_count++).ToString(); _sb.Append(name); _parameters.Add(name, p); if(_autoDiscoverParameters _step == SPStep.EndWith) _step = SPStep.Skip; } private object TryGetValueFromString(string s) {// 20，可以是 byte, short, int, long, uint, ulong … int number1 = 0; if( int.TryParse(s, out number1) ) return number1; DateTime dt = DateTime.MinValue; if(DateTime.TryParse(s, out dt) ) return dt; // 23.45，可以是 float, double, decimal decimal number5 = 0m; if(decimal.TryParse(s, out number5) ) return number5; // 其它類型全部放棄嘗試。return null; } public static CPQuery operator +(CPQuery query, string s) {query.AddSqlText(s); return query; } public static CPQuery operator +(CPQuery query, QueryParameter p) {query.AddParameter(p); return query; } } public sealed class QueryParameter {private object _val; public QueryParameter(object val) {_val = val;} public object Value {get { return _val;} } public static explicit operator QueryParameter(string a) {return new QueryParameter(a); } public static implicit operator QueryParameter(int a) {return new QueryParameter(a); } public static implicit operator QueryParameter(decimal a) {return new QueryParameter(a); } public static implicit operator QueryParameter(DateTime a) {return new QueryParameter(a); } // 其它需要支持的隱式類型轉換操作符重載請自行添加。} public static class CPQueryExtensions {public static CPQuery AsCPQuery(this string s) {return new CPQuery(s, false); } public static CPQuery AsCPQuery(this string s, bool autoDiscoverParameters) {return new CPQuery(s,autoDiscoverParameters); } public static QueryParameter AsQueryParameter(this object b) {return new QueryParameter(b); } } }

CPQuery 的已知問題以及解決方法 在開始閱讀這一節之前，請務必保證已經閱讀過前面的源代碼，尤其是 AddSqlText，TryGetValueFromString 這二個方法。在【揭秘原因】這節中，我說過：CPQuery 重載了 + 運算符，會識別拼接過程中的參數值與 SQL 語句片段。其實這個所謂的識別過程，主要就是在這二個方法中實現的。尤其是在 TryGetValueFromString 方法中，我無奈地寫出了下面的注釋：復制代碼 代碼如下:
// 20，可以是 byte, short, int, long, uint, ulong … // 23.45，可以是 float, double, decimal // 其它類型全部放棄嘗試。

很顯然，當把一個數字變成字符串后，很難再知道數字原來的類型是什么。因此，在這個方法的實現過程中，我只使用了我認為最常見的數據類型。我不能保證它們永遠能夠正確運行。還有，雖然我們可以通過判斷二個 來確定中間是一個字符串參數值，然而，對于前面的示例中的參數值來說：Fish Li 這個字符串如果是寫成這樣呢：Fish + + Li？因為很有可能實際代碼是：s1 + + s2，換句話說：字符串參數值也是拼接得到的。對于這二個問題，我只能說：我也沒辦法了。這是一個已知道問題，那么有沒有解決方法呢？答案是：有的。思路也簡單：既然猜測可能會出錯，那么就不要去猜了，你得顯式指出參數值。如何【顯式指出參數值】呢？其實也不難，大致有以下方法：1. 非字符串參數值不要轉成字符串，例如：數字就讓它是數字。2. 字符串參數需要單獨標識出來。具體方法可參考下面的示例代碼（與前面的代碼是等價的）：復制代碼 代碼如下:
static CPQuery BuildDynamicQuery(Product p) {// 下面二行代碼是等價的，可根據喜好選擇。var query = select ProductID, ProductName from Products where (1=1) .AsCPQuery(); //var query = CPQuery.New() + select ProductID, ProductName from Products where (1=1) // 注意：下面的拼接代碼中不能寫成: query += ….. if(p.ProductID 0) query = query + and ProductID = + p.ProductID; // 整數參數。if(string.IsNullOrEmpty(p.ProductName) == false ) // 給查詢添加一個字符串參數。query = query + and ProductName like + p.ProductName.AsQueryParameter(); if( p.CategoryID 0) query = query + and CategoryID = + p.CategoryID; // 整數參數。if(string.IsNullOrEmpty(p.Unit) == false ) query = query + and Unit = + (QueryParameter)p.Unit; // 字符串參數 if(p.UnitPrice 0) query = query + and UnitPrice = + p.UnitPrice; // decimal 參數。if(p.Quantity 0) query = query + and Quantity = + p.Quantity; // 整數參數。return query; }

在這段代碼中，數字沒有轉成字符串，它在運行時，其實是執行 QueryParameter 類型中定義的隱式類型轉換，它們會轉換成 QueryParameter 對象，因此，根本就沒有機會搞錯，而且執行效率更高。字符串參數值需要調用 AsQueryParameter() 擴展方法或者顯式轉換成 QueryParameter 對象，此時也不需要識別，因此也沒機會搞錯。我強烈推薦使用這種方法來拼接。注意：1. 字符串參數值在拼接時，不需要由二個 包起來。2. AsCPQuery() 或者 CPQuery.New() 的調用中，不需要參數，或者傳入 false。說明：1. 在拼接字符串時，C# 本身就允許 abc + 123 這樣的寫法，只是說寫成 abc + 123.ToString() 會快點。2. 在使用 CPQuery 時，所有的參數值都可以顯式轉換成 QueryParameter，例如：“……”+ (QueryParameter)p.Quantity 更多 CPQuery 示例 CPQuery 是為了部分解決拼接 SQL 的缺點而設計的，它做為 ClownFish 的增強功能已補充到 ClownFish 中。在 ClownFish 的示例中，也專門為 CPQuery 準備了一個更強大的示例，那個示例演示了在 4 種數據庫中使用 CPQuery：

為了方便的使用 CPQuery，ClownFish 的 DbHelper 類為所有的數據庫訪問方法提供了對應的重載方法：復制代碼 代碼如下:
public static int ExecuteNonQuery(CPQuery query) public static int ExecuteNonQuery(CPQuery query, DbContext dbContext) public static object ExecuteScalar(CPQuery query) public static object ExecuteScalar(CPQuery query, DbContext dbContext) public static T ExecuteScalar T (CPQuery query) public static T ExecuteScalar T (CPQuery query, DbContext dbContext) public static T GetDataItem T (CPQuery query) public static T GetDataItem T (CPQuery query, DbContext dbContext) public static List T FillList T (CPQuery query) public static List T FillList T (CPQuery query, DbContext dbContext) public static List T FillScalarList T (CPQuery query) public static List T FillScalarList T (CPQuery query, DbContext dbContext) public static DataTable FillDataTable(CPQuery query) public static DataTable FillDataTable(CPQuery query, DbContext dbContext)

所以，使用起來也非常容易：復制代碼 代碼如下:
var query = BuildDynamicQuery(p); DataTable table = DbHelper.FillDataTable(query);

CPQuery 的設計目標及使用建議 CPQuery 的設計目標是：將傳統的拼接 SQL 代碼轉成參數化的 SQL，而且將使用和學習成本降到最低。本文開頭的示例我想已經證明了 CPQuery 已經實現了這個目標。只需要拼接的第一個字符串上調用 AsCPQuery() 擴展方法，或者在所有字符串前加上 CPQuery.New() 就能解決。注意：1. 提供 AsCPQuery(true) 或者 CPQuery.New(true) 方法，僅僅用于處理現有代碼，可認為是兼容性解決方案。2. 我強烈建議調用 AsCPQuery() 或者 CPQuery.New() 來處理拼接，原因前面有解釋，這里不再重復。有些人看到了示例代碼會認為 CPQuery 使用起來好復雜。這種說法完全是不動腦子的說法。你寫拼接 SQL 的代碼會短多少？我前面已經說過了：CPQuery 的設計目標不是一個數據訪問層，它只是為解決拼接 SQL 而設計的。使用起來方不方便，要看具體的數據訪問層來與 CPQuery 的整體與包裝方式。示例代碼為了保證所有人能看懂，我直接使用了 ADO.NET，而且中間包含了調試代碼，所以看起來長了點，但是，關鍵代碼有多少，這個還看不出來嗎？CPQuery 類的代碼，你看不懂也沒用關系，我們只需要調用一次它的擴展方法（或者靜態方法）就可以了。關于易用性，我最后想說的就是：如果想方便，可以試一下 ClownFish，它集成了 CPQuery。 

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注丸趣 TV 行業資訊頻道，感謝您對丸趣 TV 的支持。