CPQuery中怎么拼接SQL

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

 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 。 

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