服务器之家

服务器之家 > 正文

CPQuery 解决拼接SQL的新方法

时间:2019-12-24 14:20     来源/作者:MSSQL教程网

我一直都不喜欢在访问数据库时采用拼接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并不是万能的,代码的可维护性与技术的选择无关,与架构的设计有关。任何优秀的技术都可能写出难以维护的代码来,这就是我的观点。 

改造现有的拼接语句 
还是说动态查询,假设我有这样一个查询界面: 
CPQuery,拼接,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; 


我们来看一下程序运行的结果:

 

CPQuery,拼接,SQL

根据前面给出的调试代码: 

复制代码代码如下:

// 输出调试信息。 
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,拼接,SQL

为了方便的使用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,因为拼接会涉及多种语句的代码混合在一起,这种做法会给代码的可维护性产生负面影响。 

标签:

相关文章

热门资讯

玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
配置IIS网站web服务器的安全策略配置解决方案
配置IIS网站web服务器的安全策略配置解决方案 2019-05-23
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情 2019-06-22
Nginx服务器究竟是怎么执行PHP项目
Nginx服务器究竟是怎么执行PHP项目 2019-05-24
返回顶部