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

2023年6月21日发(作者：)

SQL参数化查询⾃动⽣成SqlParameter列表string sql = @"INSERT INTO stu VALUES (@id,@name) ";参数化查询是经常⽤到的，它可以有效防⽌SQL注⼊。但是需要⼿动去匹配参数@id，@name。数据量⼤时很繁琐，下⾯是⾃动填充SqlParameter列表的实现。⽀持泛型，Object和ExpandoObject动态类型using System;

using c;

using ent;

using ;

using tion;

using ;

using rExpressions;

using c;

namespace Comm

{

///

/// 作者:徐晓硕

/// 邮箱:xuxiaoshuo@

/// 版本:v1.0.0

///

public class GetSqlParameters

{

///

/// 过滤参数的规则

///

private static Regex reg = new Regex(@"@S{1,}?(,|s|;|--|)|$)");

private static char[] filterChars = new char[] { ' ', ',', ';', '-',')' };

///

/// 根据sql语句和实体对象⾃动⽣成参数化查询SqlParameter列表

///

/// 实体对象类型

/// sql语句

/// 实体对象

/// SqlParameter列表

public static List From(String sqlStr, T obj)

{

List parameters = new List();

List listStr = new List();

Match mymatch = (sqlStr);

while (s)

{

(d(filterChars).TrimStart('@'));

mymatch = tch();

}

Type t = typeof(T);

PropertyInfo[] pinfo = perties();

foreach (var item in listStr)

{

for (int i = 0; i < ; i++)

{

if ((pinfo[i].Name, lIgnoreCase))

{

(new SqlParameter() { ParameterName = "@" + item, Value = pinfo[i].GetValue(obj, null) });

break;

}

else else

{

if (i == - 1)

{

throw new Exception("查询参数@" + item + "在类型" + ng() + "中未找到赋值属性");

}

}

}

}

return parameters;

}

///

/// 根据sql语句和实体对象⾃动⽣成参数化查询SqlParameter列表

///

/// sql语句

/// 实体对象

/// SqlParameter列表

public static List From(String sqlStr, object obj)

{

List parameters = new List();

List listStr = new List();

Match mymatch = (sqlStr);

while (s)

{

(d(filterChars).TrimStart('@'));

mymatch = tch();

}

Type t = e();

PropertyInfo[] pinfo = perties();

foreach (var item in listStr)

{

for (int i = 0; i < ; i++)

{

if ((pinfo[i].Name, lIgnoreCase))

{

(new SqlParameter() { ParameterName = "@" + item, Value = pinfo[i].GetValue(obj, null) });

break;

}

else

{

if (i == - 1)

{

throw new Exception("查询参数@" + item + "在类型" + ng() + "中未找到赋值属性");

}

}

}

}

return parameters;

}

///

/// 根据sql语句和ExpandoObject对象⾃动⽣成参数化查询SqlParameter列表

///

/// sql语句

/// ExpandoObject对象

/// SqlParameter列表

public static List From(String sqlStr, ExpandoObject obj)

{

List parameters = new List();

List listStr = new List(); List listStr = new List();

Match mymatch = (sqlStr);

while (s)

{

(d(filterChars).TrimStart('@'));

mymatch = tch();

}

IDictionary dic=(IDictionary)obj;

foreach (var item in listStr)

{

int reachCount = 0;

foreach (var property in dic)

{

if ((, lIgnoreCase))

{

(new SqlParameter() { ParameterName = "@" + item, Value = });

break;

}

else

{

if (reachCount == -1)

{

throw new Exception("查询参数@" + item + "在类型ExpandoObject中未找到赋值属性");

}

}

reachCount++;

}

}

return parameters;

}

}

}

Demo代码 using System;

using c;

using ;

using ;

using tion;

using ;

using ;

using ;

using ent;

using c;

using Comm;

namespace 数据层

{

class Program

{

static void Main(string[] args)

{

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

dynamic wherePart = new ExpandoObject();

= "1";

= "Test";

List listPar2 = (sql, wherePart);

foreach (var item in listPar2)

{

ine(terName + ":" + );

}

y();

}

}

}

2023年6月21日发(作者：)

SQL参数化查询⾃动⽣成SqlParameter列表string sql = @"INSERT INTO stu VALUES (@id,@name) ";参数化查询是经常⽤到的，它可以有效防⽌SQL注⼊。但是需要⼿动去匹配参数@id，@name。数据量⼤时很繁琐，下⾯是⾃动填充SqlParameter列表的实现。⽀持泛型，Object和ExpandoObject动态类型using System;

using c;

using ent;

using ;

using tion;

using ;

using rExpressions;

using c;

namespace Comm

{

///

/// 作者:徐晓硕

/// 邮箱:xuxiaoshuo@

/// 版本:v1.0.0

///

public class GetSqlParameters

{

///

/// 过滤参数的规则

///

private static Regex reg = new Regex(@"@S{1,}?(,|s|;|--|)|$)");

private static char[] filterChars = new char[] { ' ', ',', ';', '-',')' };

///

/// 根据sql语句和实体对象⾃动⽣成参数化查询SqlParameter列表

///

/// 实体对象类型

/// sql语句

/// 实体对象

/// SqlParameter列表

public static List From(String sqlStr, T obj)

{

List parameters = new List();

List listStr = new List();

Match mymatch = (sqlStr);

while (s)

{

(d(filterChars).TrimStart('@'));

mymatch = tch();

}

Type t = typeof(T);

PropertyInfo[] pinfo = perties();

foreach (var item in listStr)

{

for (int i = 0; i < ; i++)

{

if ((pinfo[i].Name, lIgnoreCase))

{

(new SqlParameter() { ParameterName = "@" + item, Value = pinfo[i].GetValue(obj, null) });

break;

}

else else

{

if (i == - 1)

{

throw new Exception("查询参数@" + item + "在类型" + ng() + "中未找到赋值属性");

}

}

}

}

return parameters;

}

///

/// 根据sql语句和实体对象⾃动⽣成参数化查询SqlParameter列表

///

/// sql语句

/// 实体对象

/// SqlParameter列表

public static List From(String sqlStr, object obj)

{

List parameters = new List();

List listStr = new List();

Match mymatch = (sqlStr);

while (s)

{

(d(filterChars).TrimStart('@'));

mymatch = tch();

}

Type t = e();

PropertyInfo[] pinfo = perties();

foreach (var item in listStr)

{

for (int i = 0; i < ; i++)

{

if ((pinfo[i].Name, lIgnoreCase))

{

(new SqlParameter() { ParameterName = "@" + item, Value = pinfo[i].GetValue(obj, null) });

break;

}

else

{

if (i == - 1)

{

throw new Exception("查询参数@" + item + "在类型" + ng() + "中未找到赋值属性");

}

}

}

}

return parameters;

}

///

/// 根据sql语句和ExpandoObject对象⾃动⽣成参数化查询SqlParameter列表

///

/// sql语句

/// ExpandoObject对象

/// SqlParameter列表

public static List From(String sqlStr, ExpandoObject obj)

{

List parameters = new List();

List listStr = new List(); List listStr = new List();

Match mymatch = (sqlStr);

while (s)

{

(d(filterChars).TrimStart('@'));

mymatch = tch();

}

IDictionary dic=(IDictionary)obj;

foreach (var item in listStr)

{

int reachCount = 0;

foreach (var property in dic)

{

if ((, lIgnoreCase))

{

(new SqlParameter() { ParameterName = "@" + item, Value = });

break;

}

else

{

if (reachCount == -1)

{

throw new Exception("查询参数@" + item + "在类型ExpandoObject中未找到赋值属性");

}

}

reachCount++;

}

}

return parameters;

}

}

}

Demo代码 using System;

using c;

using ;

using ;

using tion;

using ;

using ;

using ;

using ent;

using c;

using Comm;

namespace 数据层

{

class Program

{

static void Main(string[] args)

{

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

dynamic wherePart = new ExpandoObject();

= "1";

= "Test";

List listPar2 = (sql, wherePart);

foreach (var item in listPar2)

{

ine(terName + ":" + );

}

y();

}

}

}