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

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();  
        }  
    }      
}  

转载：http://blog.csdn.net/xxs77ch/article/details/51513722