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源码和示例代码