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 _parameters = new Dictionary(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 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(CPQuery query)
public static T ExecuteScalar(CPQuery query, DbContext dbContext)
public static T GetDataItem(CPQuery query)
public static T GetDataItem(CPQuery query, DbContext dbContext)
public static List FillList(CPQuery query)
public static List FillList(CPQuery query, DbContext dbContext)
public static List FillScalarList(CPQuery query)
public static List FillScalarList(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源码和示例代码