CPQuery, 解决拼接SQL的新方法
CPQuery, 解决拼接SQL的新方法
阅读目录
开始 CPQuery是什么？ 参数化的SQL语句 改造现有的拼接语句 揭秘原因 CPQuery源码 CPQuery的已知问题以及解决方法 更多CPQuery示例 友情提示我一直都不喜欢在访问数据库时采用拼接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的人们通常会这样写查询：
如果使用这种方式，本文开头所说的前二个缺点肯定是存在的。
我想很多人应该是知道参数化查询的，最终放弃或许有以下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) ，这个方法的实现代码我后面再说。
这个示例的主要关键代码如下：
我们来看一下程序运行的结果：
根据前面给出的调试代码：
// 输出调试信息。
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，因为拼接会涉及多种语句的代码混合在一起， 这种做法会给代码的可维护性产生负面影响。
点击此处下载CPQuery源码和示例代码
ClownFish 已有新版本， 点击此处进入下载页面
如果，您认为阅读这篇博客让您有些收获，不妨点击一下右下角的 【 推荐 】 按钮。
如果，您希望更容易地发现我的新博客，不妨点击一下右下角的 【 关注 Fish Li 】 。
因为，我的写作热情也离不开您的肯定支持。
感谢您的阅读，如果您对我的博客所讲述的内容有兴趣，请继续关注我的后续博客，我是Fish Li 。
分类: Ado.net , ClownFish
作者： Leo_wl
出处： http://www.cnblogs.com/Leo_wl/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
版权信息查看更多关于CPQuery, 解决拼接SQL的新方法的详细内容...