c#SQL参数化查询自动生成SqlParameter列表

来源：互联网发布：计算机技术与软件报名编辑：程序博客网时间：2024/06/06 17:29

string sql = @"INSERT INTO stu VALUES (@id,@name) ";

参数化查询是经常用到的，它可以有效防止SQL注入。但是需要手动去匹配参数@id，@name。数据量大时很繁琐，下面是自动填充SqlParameter列表的实现。

支持泛型，Object和ExpandoObject动态类型

using System;using System.Collections.Generic;using System.Data.SqlClient;using System.Linq;using System.Reflection;using System.Text;using System.Text.RegularExpressions;using System.Dynamic;namespace Comm{    /// <summary>    /// 作者:徐晓硕    /// 邮箱:xuxiaoshuo@fang.com    /// 版本:v1.0.0    /// </summary>    public class GetSqlParameters    {        /// <summary>        /// 过滤参数的规则        /// </summary>        private static Regex reg = new Regex(@"@\S{1,}?(,|\s|;|--|\)|$)");        private static char[] filterChars = new char[] { ' ', ',', ';', '-',')' };        /// <summary>        /// 根据sql语句和实体对象自动生成参数化查询SqlParameter列表        /// </summary>        /// <typeparam name="T">实体对象类型</typeparam>        /// <param name="sqlStr">sql语句</param>        /// <param name="obj">实体对象</param>        /// <returns>SqlParameter列表</returns>        public static List<SqlParameter> From<T>(String sqlStr, T obj)        {            List<SqlParameter> parameters = new List<SqlParameter>();            List<string> listStr = new List<string>();            Match mymatch = reg.Match(sqlStr);            while (mymatch.Success)            {                listStr.Add(mymatch.Value.TrimEnd(filterChars).TrimStart('@'));                mymatch = mymatch.NextMatch();            }            Type t = typeof(T);            PropertyInfo[] pinfo = t.GetProperties();            foreach (var item in listStr)            {                for (int i = 0; i < pinfo.Length; i++)                {                    if (item.Equals(pinfo[i].Name, StringComparison.OrdinalIgnoreCase))                    {                        parameters.Add(new SqlParameter() { ParameterName = "@" + item, Value = pinfo[i].GetValue(obj, null) });                        break;                    }                    else                    {                        if (i == pinfo.Length - 1)                        {                            throw new Exception("查询参数@" + item + "在类型" + t.ToString() + "中未找到赋值属性");                        }                    }                }            }            return parameters;        }        /// <summary>        /// 根据sql语句和实体对象自动生成参数化查询SqlParameter列表        /// </summary>        /// <param name="sqlStr">sql语句</param>        /// <param name="obj">实体对象</param>        /// <returns>SqlParameter列表</returns>        public static List<SqlParameter> From(String sqlStr, object obj)        {            List<SqlParameter> parameters = new List<SqlParameter>();            List<string> listStr = new List<string>();            Match mymatch = reg.Match(sqlStr);            while (mymatch.Success)            {                listStr.Add(mymatch.Value.TrimEnd(filterChars).TrimStart('@'));                mymatch = mymatch.NextMatch();            }            Type t = obj.GetType();            PropertyInfo[] pinfo = t.GetProperties();            foreach (var item in listStr)            {                for (int i = 0; i < pinfo.Length; i++)                {                    if (item.Equals(pinfo[i].Name, StringComparison.OrdinalIgnoreCase))                    {                        parameters.Add(new SqlParameter() { ParameterName = "@" + item, Value = pinfo[i].GetValue(obj, null) });                        break;                    }                    else                    {                        if (i == pinfo.Length - 1)                        {                            throw new Exception("查询参数@" + item + "在类型" + t.ToString() + "中未找到赋值属性");                        }                    }                }            }            return parameters;        }        /// <summary>        /// 根据sql语句和ExpandoObject对象自动生成参数化查询SqlParameter列表        /// </summary>        /// <param name="sqlStr">sql语句</param>        /// <param name="obj">ExpandoObject对象</param>        /// <returns>SqlParameter列表</returns>        public static List<SqlParameter> From(String sqlStr, ExpandoObject obj)        {            List<SqlParameter> parameters = new List<SqlParameter>();            List<string> listStr = new List<string>();            Match mymatch = reg.Match(sqlStr);            while (mymatch.Success)            {                listStr.Add(mymatch.Value.TrimEnd(filterChars).TrimStart('@'));                mymatch = mymatch.NextMatch();            }            IDictionary<String, Object> dic=(IDictionary<String, Object>)obj;                      foreach (var item in listStr)            {                int reachCount = 0;                foreach (var property in dic)                {                    if (item.Equals(property.Key, StringComparison.OrdinalIgnoreCase))                    {                        parameters.Add(new SqlParameter() { ParameterName = "@" + item, Value = property.Value });                        break;                    }                    else                    {                        if (reachCount == dic.Count-1)                        {                            throw new Exception("查询参数@" + item + "在类型ExpandoObject中未找到赋值属性");                        }                    }                    reachCount++;                }            }                      return parameters;        }    }}

Demo代码

using System;using System.Collections.Generic;using System.Data.Common;using System.Linq;using System.Reflection;using System.Text;using Framework.Data;using System.Data;using System.Data.SqlClient;using System.Dynamic;using Comm;namespace 数据层{    class Program    {        static void Main(string[] args)        {                       string sql = @"INSERT INTO stu VALUES (@id,@name) ";                     dynamic wherePart = new ExpandoObject();            wherePart.ID = "1";            wherePart.Name = "Test";            List<SqlParameter> listPar2 = GetSqlParameters.From(sql, wherePart);            foreach (var item in listPar2)            {                Console.WriteLine(item.ParameterName + ":" + item.Value);            }            Console.ReadKey();        }    }    }

1 0