• 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的人们通常会这样写查询: 

    如果使用这种方式,本文开头所说的前二个缺点肯定是存在的。

    我想很多人应该是知道参数化查询的,最终放弃或许有以下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 已有新版本,点击此处进入下载页面

     
    分类: Ado.netClownFish
  • 相关阅读:
    pdfobject (前台展示PDF插件)
    ERROR 19608 --- [ost-startStop-1] c.atomikos.persistence.imp.LogFileLock : ERROR: the specified log seems to be in use already: tmlog in D: ools omcatapache-tomcat-8.5.51in ransaction-logs
    文件上传下载(四) 读 txt 文本 ajaxfileupload
    1129
    centos服务器上部署项目(二) -tomcat
    Guns 打包
    centos服务器上部署项目(一) -jdk,mysql
    layui 学习笔记(四) 复杂表头前台Excel导出
    SpringCloud项目搭建(四) zuul
    sql的基本查询语句
  • 原文地址:https://www.cnblogs.com/Leo_wl/p/2678664.html
Copyright © 2020-2023  润新知