c#sqlwherein参数化传值

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

c#sqlwherein参数化传值第⼀种：传统Sql的Where IN（）值拼接不推荐，有sql注⼊风险 //传统Sql的Where IN（）值拼装 string str = "1,2,3";

StringBuilder sql = new StringBuilder(); ("select * from user where userId in("); (str); (")"); string sqlStr = ng(); ine(sqlStr);结果select * from user where userId in(1,2,3)第⼆种：Sql的Where IN（）参数化传值可以有效防⽌sql注⼊公⽤变量string strArray = "1,2,3";string sql = "select * from user where userId in";sqlParameters有值传递DbParameter[] sqlParameters1 = { new SqlParameter("@UserID1", ) { Value = 1}, new SqlParameter("@UserID2", ) { Value = 2}, new SqlParameter("@UserID3", ) { Value = 3}, new SqlParameter("@UserID4", ) { Value = 4} };

string sqlWhereInValue1 = "";DbParameter[] dbParameters1 = WhereInFactory(sqlParameters1, strArray, out sqlWhereInValue1);string sql1 = sql + sqlWhereInValue1;结果select * from user where userId in(@sqlWhereInValue0,@sqlWhereInValue1,@sqlWhereInValue2)

sqlParameters为空传递 string sqlWhereInValue2 = ""; DbParameter[] sqlParameters2 = { }; DbParameter[] dbParameters2 = WhereInFactory(sqlParameters2, strArray, out sqlWhereInValue2); string sql2 = sql + sqlWhereInValue2;结果select * from user where userId in(@sqlWhereInValue0,@sqlWhereInValue1,@sqlWhereInValue2)

Sql的Where IN（）的拼接⼯⼚ ///

/// Sql的Where IN（）的拼接⼯⼚ ///

/// parameter数组 /// ID数组 如：1,2,3 /// 接收⽣成的sql字符串变量 private static DbParameter[] WhereInFactory(DbParameter[] parameter, string IdArray, out string sqlWhereInValue) { string[] strArray = (','); int p_length = (); int s_length = (); int length = p_length + s_length; DbParameter[] dbParameters = new DbParameter[length]; StringBuilder sqlWhereIn = new StringBuilder(); int j = 0; for (int i = 0; i < length; i++) { if (i < p_length) { dbParameters[i] = parameter[i]; } else { string dot = i + 1 != length ? ",":""; ("@sqlWhereInValue" + j+ dot); dbParameters[i] = new SqlParameter("@sqlWhereInValue" + j, 32(strArray[j])); j++; } } sqlWhereInValue = $"({ng()})"; return dbParameters; }完整代码 using System;using c;using ;using ;using ent;using ;using ;using ;namespace test1{ ///

/// Sql的Where IN（）Test ///

public class SqlWhereInTest { ///

/// 执⾏ ///

public static void Exe() { //数组长度测试 DbParameter[] sqlParameters6 = new DbParameter[6]; DbParameter[] sqlParameters10 = new DbParameter[10]; DbParameter[] sqlParameters10 = new DbParameter[10]; sqlParameters6 = sqlParameters10; { //传统Sql的Where IN（）值拼装 string str = "1,2,3";

StringBuilder sql = new StringBuilder(); ("select * from user where userId in("); (str); (")"); string sqlStr = ng(); ine(sqlStr); } { //Sql的Where IN（）参数化值拼装

string strArray = "1,2,3"; string sql = "select * from user where userId in"; //sqlParameters有值传递 DbParameter[] sqlParameters1 = { new SqlParameter("@UserID1", ) { Value = 1}, new SqlParameter("@UserID2", ) { Value = 2}, new SqlParameter("@UserID3", ) { Value = 3}, new SqlParameter("@UserID4", ) { Value = 4} };

string sqlWhereInValue1 = ""; DbParameter[] dbParameters1 = WhereInFactory(sqlParameters1, strArray, out sqlWhereInValue1); string sql1 = sql + sqlWhereInValue1; //sqlParameters为空传递 string sqlWhereInValue2 = ""; DbParameter[] sqlParameters2 = { }; DbParameter[] dbParameters2 = WhereInFactory(sqlParameters2, strArray, out sqlWhereInValue2); string sql2 = sql + sqlWhereInValue2; ine(strArray); }

} ///

/// Sql的Where IN（）的拼接⼯⼚ ///

/// parameter数组 /// ID数组 如：1,2,3 /// 接收⽣成的sql字符串变量 private static DbParameter[] WhereInFactory(DbParameter[] parameter, string IdArray, out string sqlWhereInValue) { string[] strArray = (','); int p_length = (); int s_length = (); int length = p_length + s_length; DbParameter[] dbParameters = new DbParameter[length]; StringBuilder sqlWhereIn = new StringBuilder(); int j = 0; for (int i = 0; i < length; i++) { if (i < p_length) { dbParameters[i] = parameter[i]; dbParameters[i] = parameter[i]; } else { string dot = i + 1 != length ? ",":""; ("@sqlWhereInValue" + j+ dot); dbParameters[i] = new SqlParameter("@sqlWhereInValue" + j, 32(strArray[j])); j++; } } sqlWhereInValue = $"({ng()})"; return dbParameters; } }}

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

c#sqlwherein参数化传值第⼀种：传统Sql的Where IN（）值拼接不推荐，有sql注⼊风险 //传统Sql的Where IN（）值拼装 string str = "1,2,3";

StringBuilder sql = new StringBuilder(); ("select * from user where userId in("); (str); (")"); string sqlStr = ng(); ine(sqlStr);结果select * from user where userId in(1,2,3)第⼆种：Sql的Where IN（）参数化传值可以有效防⽌sql注⼊公⽤变量string strArray = "1,2,3";string sql = "select * from user where userId in";sqlParameters有值传递DbParameter[] sqlParameters1 = { new SqlParameter("@UserID1", ) { Value = 1}, new SqlParameter("@UserID2", ) { Value = 2}, new SqlParameter("@UserID3", ) { Value = 3}, new SqlParameter("@UserID4", ) { Value = 4} };

string sqlWhereInValue1 = "";DbParameter[] dbParameters1 = WhereInFactory(sqlParameters1, strArray, out sqlWhereInValue1);string sql1 = sql + sqlWhereInValue1;结果select * from user where userId in(@sqlWhereInValue0,@sqlWhereInValue1,@sqlWhereInValue2)

sqlParameters为空传递 string sqlWhereInValue2 = ""; DbParameter[] sqlParameters2 = { }; DbParameter[] dbParameters2 = WhereInFactory(sqlParameters2, strArray, out sqlWhereInValue2); string sql2 = sql + sqlWhereInValue2;结果select * from user where userId in(@sqlWhereInValue0,@sqlWhereInValue1,@sqlWhereInValue2)

Sql的Where IN（）的拼接⼯⼚ ///

/// Sql的Where IN（）的拼接⼯⼚ ///

/// parameter数组 /// ID数组 如：1,2,3 /// 接收⽣成的sql字符串变量 private static DbParameter[] WhereInFactory(DbParameter[] parameter, string IdArray, out string sqlWhereInValue) { string[] strArray = (','); int p_length = (); int s_length = (); int length = p_length + s_length; DbParameter[] dbParameters = new DbParameter[length]; StringBuilder sqlWhereIn = new StringBuilder(); int j = 0; for (int i = 0; i < length; i++) { if (i < p_length) { dbParameters[i] = parameter[i]; } else { string dot = i + 1 != length ? ",":""; ("@sqlWhereInValue" + j+ dot); dbParameters[i] = new SqlParameter("@sqlWhereInValue" + j, 32(strArray[j])); j++; } } sqlWhereInValue = $"({ng()})"; return dbParameters; }完整代码 using System;using c;using ;using ;using ent;using ;using ;using ;namespace test1{ ///

/// Sql的Where IN（）Test ///

public class SqlWhereInTest { ///

/// 执⾏ ///

public static void Exe() { //数组长度测试 DbParameter[] sqlParameters6 = new DbParameter[6]; DbParameter[] sqlParameters10 = new DbParameter[10]; DbParameter[] sqlParameters10 = new DbParameter[10]; sqlParameters6 = sqlParameters10; { //传统Sql的Where IN（）值拼装 string str = "1,2,3";

StringBuilder sql = new StringBuilder(); ("select * from user where userId in("); (str); (")"); string sqlStr = ng(); ine(sqlStr); } { //Sql的Where IN（）参数化值拼装

string strArray = "1,2,3"; string sql = "select * from user where userId in"; //sqlParameters有值传递 DbParameter[] sqlParameters1 = { new SqlParameter("@UserID1", ) { Value = 1}, new SqlParameter("@UserID2", ) { Value = 2}, new SqlParameter("@UserID3", ) { Value = 3}, new SqlParameter("@UserID4", ) { Value = 4} };

string sqlWhereInValue1 = ""; DbParameter[] dbParameters1 = WhereInFactory(sqlParameters1, strArray, out sqlWhereInValue1); string sql1 = sql + sqlWhereInValue1; //sqlParameters为空传递 string sqlWhereInValue2 = ""; DbParameter[] sqlParameters2 = { }; DbParameter[] dbParameters2 = WhereInFactory(sqlParameters2, strArray, out sqlWhereInValue2); string sql2 = sql + sqlWhereInValue2; ine(strArray); }

} ///

/// Sql的Where IN（）的拼接⼯⼚ ///

/// parameter数组 /// ID数组 如：1,2,3 /// 接收⽣成的sql字符串变量 private static DbParameter[] WhereInFactory(DbParameter[] parameter, string IdArray, out string sqlWhereInValue) { string[] strArray = (','); int p_length = (); int s_length = (); int length = p_length + s_length; DbParameter[] dbParameters = new DbParameter[length]; StringBuilder sqlWhereIn = new StringBuilder(); int j = 0; for (int i = 0; i < length; i++) { if (i < p_length) { dbParameters[i] = parameter[i]; dbParameters[i] = parameter[i]; } else { string dot = i + 1 != length ? ",":""; ("@sqlWhereInValue" + j+ dot); dbParameters[i] = new SqlParameter("@sqlWhereInValue" + j, 32(strArray[j])); j++; } } sqlWhereInValue = $"({ng()})"; return dbParameters; } }}