利用formatter原理自动化参数化查询

前言：对于经常忙于服务端开发的小伙伴来说，与DB层打交道是在正常不过的事了，但是每次页面的查询条件新增往往意味着后端代码参数化同比增长，当然你可以不使用sqlhelper自带的参数化条件查询，可以直接传递参数，这样一来，可能你写的代码就变少了，但是存在一个隐藏的问题就是sql注入，对于sql注入我想大家都并不陌生，相关资源和预防措施网上千篇一律，有兴趣可以自己去了解，常用的注入工具是sqlmapper，有兴趣的可以一并去了解。

那么你写代码既要保持代码的优雅，语句条数的少量，有想要保证代码的安全性能，不被轻易注入，有没有一种2种都能兼容的方式呢，在对于一般习惯拘泥于原有代码思维的人来看，可能并没有，但是习惯于从不同角度思考问题的人来说，条条大路通罗马，比如我下面说的这种就是基于.NET 自带的stirngfromatter原理来实现的一种自动化参数化查询~~

由于口头表述不是很好，我直接贴图来说明了，请看

这是原先的参数化查询

public PayOrderEntity GetPayOrderInfoById(int id)        {            PayOrderEntity parorderModel = new PayOrderEntity();            List<SqlParameter> paramList = new List<SqlParameter>();            string sql = @"select p.Id as pid                    ,c.Id                    ,c.UserId                    ,p.OrderId                    ,p.TransactionId                    ,p.PayType                    ,c.TotalPrice as orderprice                    ,p.TotalPrice as payprice                    ,c.[Status] as orderstatus                    ,p.[Status] as paystatus                    ,c.CreateTime                     ,p.PayTime                    ,c.Remark as orderremark                    ,p.Remark as payremark                    from t_ContentOrder(nolock) c                    left join t_Payment(nolock) p                    on c.Id=p.OrderId where c.IsDeleted=0 and  p.IsDeleted=0 and p.Id= {0}";            paramList.Add(new SqlParameter("@id", id));            try            {                var dt = SqlHelper.ExecuteDataset(write_connstring, CommandType.Text, sql, paramList.ToArray());                //使用重写后的DB驱动查询方法调用  by:xuja  2017-05-27 10:34:19                //var dt = SqlQuery(sql, id);                parorderModel = DbTableConvertor<PayOrderEntity>.ConvertToEntity(dt.Tables[0]);            }            catch (Exception ex)            {                Core.Log.TraceLogger.Error(ex);            }            return parorderModel;        }

View Code

 

步骤一 先实例化一个sqlparams实体

步骤二 将满足条件的参数传入定义好的参数实体并赋值（可能需要条件验证）

步骤三 将填充完成后的参数对象传入调用的sqlhelper查询接口中

这样四步即可实现整个查询流程

看着流程也并不复杂，但是大家想过一个问题没，如果一个页面有6个以上的查询条件，是不是定义起来会很吃力，这种感觉我相信大家都会有，我也不列外 囧 ，接下来利用formatter原理我们在看看重构后的参数化查询代码

/// <summary>        /// sql参数自动化拼接方法        /// 创建人：xujiangan        /// 2017-05-27 10:35:40        /// </summary>        /// <param name="sqlCommand">需要查询的sql</param>        /// <param name="param">需要拼接的参数列表</param>        /// <returns></returns>        public Tuple<string, SqlParameter[]> ProcessSqlCommand(string sqlCommand, params object[] param)        {            var tempKVDic = param.Select((item, i) => new KeyValuePair<string, object>("@an" + i, item)).ToDictionary(k => k.Key, v => v.Value);            var tempSqlCommand = string.Format(sqlCommand, tempKVDic.Keys.ToArray());            var tempParams = tempKVDic.Select(t => new SqlParameter(t.Key, t.Value)).ToArray();            return Tuple.Create(tempSqlCommand, tempParams);        }

方法的返回值因为不确定到具体类型，所以用了元组来定义了，元组的能力类似于dynamic T类型，这里就不多介绍了，参数的条件只有2个，1.要执行查询的sql语句 2.需要传递的参数化条件

代码逻辑：1.将参数列表利用键值对的方法一一对应组合，参数可以自动增长，组成参数列表字典；2.使用format方法将sql语句和参数字典拼接；3.将拼接好的查询字符串返回给调用方

原先接口并没有这次参数条件的重载 接下来，我们还学要重写一下sqlhelper执行查询的接口，如下：

/// <summary>        /// 重写ExcuteQuery方法，便于自动话添加参数        /// 创建人：xujiangan        /// 2017-05-27 10:35:07        /// </summary>        /// <param name="sqlCommand">执行sql语句</param>        /// <param name="param">需要新增的查询参数</param>        /// <returns></returns>        public DataSet SqlQuery(string sqlCommand, params object[] param)        {            var dt = new DataSet();            if (param == null || param.Length == 0)            {                dt = SqlHelper.ExecuteDataset(write_connstring, sqlCommand, CommandType.Text);            }            var temp = ProcessSqlCommand(sqlCommand, param);            dt = SqlHelper.ExecuteDataset(write_connstring, CommandType.Text, temp.Item1, temp.Item2);            //object entity = DbTableConvertor<object>.ConvertToEntity(dt.Tables[0]);            return dt;        }

代码大家都能看懂，无非就是有参和无参两种情况做了区分，我就不解释了...

俗话说，欲善其事，必先利其器，有了上面的准备之后，我们就可以调用我们刚才重写的接口啦

调用请看下面：

public PayOrderEntity GetPayOrderInfoById(int id)        {            PayOrderEntity parorderModel = new PayOrderEntity();            string sql = @"select p.Id as pid                    ,c.Id                    ,c.UserId                    ,p.OrderId                    ,p.TransactionId                    ,p.PayType                    ,c.TotalPrice as orderprice                    ,p.TotalPrice as payprice                    ,c.[Status] as orderstatus                    ,p.[Status] as paystatus                    ,c.CreateTime                     ,p.PayTime                    ,c.Remark as orderremark                    ,p.Remark as payremark                    from t_ContentOrder(nolock) c                    left join t_Payment(nolock) p                    on c.Id=p.OrderId where c.IsDeleted=0 and  p.IsDeleted=0 and p.Id= {0}";            try            {                //使用重写后的DB驱动查询方法调用  by:xuja  2017-05-27 10:34:19                var dt = SqlQuery(sql, id);                parorderModel = DbTableConvertor<PayOrderEntity>.ConvertToEntity(dt.Tables[0]);            }            catch (Exception ex)            {                Core.Log.TraceLogger.Error(ex);            }            return parorderModel;        }

是不是发现少了什么？没错，我们此时不再需要定义繁杂的sqlparams对象拉，直接传递参数即可，有多少传多少，如果太多可以定义数组或者参数实体传递哦~

PS:注意sql代码语句中参数位置改为占位符！！！

到此为止，整个自动化参数化查询基本上就结束了，有问题可以给我留言指出，共勉

项目目前已经应用到 admin.kk.net 支付订单详情页面查询,经多番测试，并没有发现什么问题，看来是可以用的。

建议sql代码大小写一致，保持只解析一次。

高山仰止，景行行止

by:Jsonxu