CPQuery 解决拼接SQL的新方法
我一直都不喜欢在访问数据库时采用拼接SQL的方法，原因有以下几点： 1. 不安全：有被SQL注入的风险。 2. 可能会影响性能：每条SQL语句都需要数据库引擎执行[语句分析]之类的开销。
<p>我一直都不喜欢在访问数据库时采用拼接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并不是万能的，代码的可维护性与技术的选择无关，与架构的设计有关。任何优秀的技术都可能写出难以维护的代码来，这就是我的观点。 改造现有的拼接语句 还是说动态查询，假设我有这样一个查询界面： <img alt src="http://img.558idc.com/uploadfile/allimg/210405/114QT109-0.png"></p>显然，在设计程序时，不可能知道用户会输入什么样的过滤条件。 因此，喜欢手写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
为了方便的使用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
