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 = ; 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 - )
{
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 = ; 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 - )
{
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 = ;
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-)
{
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 = "";
wherePart.Name = "Test";
List<SqlParameter> listPar2 = GetSqlParameters.From(sql, wherePart);
foreach (var item in listPar2)
{
Console.WriteLine(item.ParameterName + ":" + item.Value);
} Console.ReadKey();
}
}
}