CPQuery 處理拼接SQL的新辦法。本站提示廣大學習愛好者：（CPQuery 處理拼接SQL的新辦法）文章只能為提供參考，不一定能成為您想要的結果。以下是CPQuery 處理拼接SQL的新辦法正文
我一向都不愛好在拜訪數據庫時采取拼接SQL的辦法，緣由有以下幾點：
1. 不平安：有被SQL注入的風險。
2. 能夠會影響機能：每條SQL語句都須要數據庫引擎履行[語句剖析]之類的開支。
3. 影響代碼的可保護性：SQL語句與C#混在一路，想修正SQL就得從新編譯法式，並且二種代碼混在一路，可讀性也欠好。
所以我平日會選擇【參數化SQL】的辦法去完成數據庫的拜訪進程，並且會將SQL語句與項目代碼（C#）分別開。
不外，有些人能夠會說：我的營業邏輯很龐雜，Where中的過慮前提弗成能事前肯定，是以不拼接SQL還不可。
看到這些缺陷，ORM用戶能夠會以為：應用ORM對象就是最終的處理計劃。
是的，切實其實ORM可以處理這些成績。
然則，處理計劃並不是只要ORM一種，還有些人就是愛好寫SQL呢。
所以，這篇博客不是寫給ORM用戶的，而是寫給一切愛好寫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 。
友誼提醒
本文一開端，我就明白表達了我的不雅點：CPQuery僅能處理拼接SQL的前二個缺陷。
應當僅當須要完成靜態查詢時才應用CPQuery，由於拼接會觸及多種語句的代碼混雜在一路，這類做法會給代碼的可保護性發生負面影響。
點擊此處下載CPQuery源碼和示例代碼
