需求

已封装了sql操作类，实现了防注入功能：

Excete("select * from table where id = :id",new {id = 3});

但实际使用没有拼接字符串方便，现在想写一个 SQLHelper 类对此进行封装

效果

原本执行 Excete方法需要传入以下参数：

1. select * from table where id = :123_1 and eid = :123_2 and sbu = :1111_3

2. new { 123_1 = 123, 123_2 = 123, 1111_3 = 1111 }

现在只需像拼接字符串一样书写即可：

select * from table where id = {123} and eid = {123} and sbu = {1111}

代码

using System.Dynamic;

namespace sqlHelper
{

    public static class SQLHelper
    {
        public static object GetParams(string sql)
        {
            int start = -1;
            int end = -1;
            int index = 1;
            bool flag = false;
            List<string> infos = new List<string>();

            for (int i = 0; i < sql.Length; i++)
            {

                if ('{'.Equals(sql[i]))
                {
                    if (i == sql.Length - 1)
                    {
                        throw new Exception("sql 格式错误：最后一位不能为【 { 】");
                    }
                    start = i;
                }
                else if ('}'.Equals(sql[i]))
                {
                    if (start == -1)
                    {
                        throw new Exception("sql 格式错误：【 } 】不能出现在 【 { 】 之前");
                    }
                    end = i;
                }
                if ((start >= 0 && end > 0) && end > start)
                {
                    flag = true;
                    HandleSQL(ref sql,start, end, index++,ref infos,ref i);
                    start = -1;
                    end = -1;
                }
            }
            if (flag)
            {
                Dictionary<string, object> values = new Dictionary<string, object>();
                ReplaceSQL(ref sql, infos, ref values);
                object obj = GetParamObj(values);

                return new
                {
                    sql = sql,
                    parameters = obj
                };
            }
            else
            {
                return new
                {
                    sql = sql,
                    parameters = new { }
                };
            }

            
        }


        /// <summary>
        /// 字典类型转化为对象
        /// </summary>
        /// <param name="dic"></param>
        /// <returns></returns>
        #region 将字典集合转换为动态对象
        /// <summary>
        /// 将字典集合转换为动态对象
        /// </summary>
        /// <param name="propertyDics">属性/属性值字典集合</param>
        /// <returns></returns>
        public static dynamic GetParamObj(IDictionary<string, object> propertyDics)
        {
            if (propertyDics == null) return null;

            IDictionary<string, object> expandoObject = new ExpandoObject();
            foreach (var d in propertyDics)
            {
                expandoObject.Add(d);
            }

            return expandoObject as ExpandoObject;
        }
        #endregion

        private static void ReplaceSQL(ref string sql, List<string> infos, ref Dictionary<string,object> values)
        {
            for(int i=0;i<infos.Count(); i++)
            {
                string key = infos[i];
                string value = GetParam(infos[i]).Trim();
                string index = key.Split('}')[1];
                sql = sql.Replace(key, ":"+ value + index);
                values.Add(value + "_" + index, value);
            }

        }

        private static string GetParam(string str)
        {
            int start = 0;
            int end = str.IndexOf("}");
            string temp = str.Substring(start + 1,end-1);
            return temp;
        }

        private static void HandleSQL(ref string sql,int start, int end, int index,ref List<string> list,ref int i)
        {
            string before = sql.Substring(0, end + 1);
            string after = sql.Substring(end + 1, sql.Length - end - 1);
            sql = before + "_" + index + after;
            list.Add(sql.Substring(start, end - start + 1) + "_" + index);
            i = i+ 1 + index.ToString().Length;
        }


        private static string GetValue(int start, int end, string value)
        {
            return value.Substring(start, end - start + 1);
        }
    }
}