C# sqlserver 参数化 in、like的传值方法
C#写的程序中,与sqlserver数据库链接,我们为了防止sql注入,要将sql语句参数化。
但in 和like 因为符号问题,要处理一下
参考了一个大佬的教程,完全拿过来用 没问题!
链接:C# 参数化SQL语句中的like和in (shuzhiduo.com) https://www.shuzhiduo.com/A/x9J2xY6Vd6/
IN:
string ids = "1,2,33,32";
string sql = "exec('SELECT * FROM [dbo].[UserGroup] WHERE id in('+@IDs+')')";
SqlParameter[] parameters = {
new SqlParameter("@IDs", SqlDbType.NVarChar,-1)};
parameters[0].Value = ids;
DataSet ds= DbHelperSQL.Query(sql,parameters);
LIKE方法一:
string strSql = "SELECT * FROM [dbo].[UserGroup] WHERE id like @id+ '%'";
SqlParameter[] parameters = {
new SqlParameter("@id", SqlDbType.NVarChar,-1)};
parameters[0].Value = "1";
DataSet ds = DbHelperSQL.Query(strSql, parameters);
上边是like ‘key%’
如果是like ‘%key%’,就用string strSql = "SELECT * FROM [dbo].[UserGroup] WHERE id like '%' + @add + '%'";
LIKE方法二:
string sql=“SELECT * FROM [dbo].[UserGroup] WHERE id like @id ”;
List<SqlParameter> ilistStr = new List<SqlParameter>();
ilistStr.Add(new SqlParameter("@id ", "%" + ID+ "%"));
DataSet ds = DbHelperSQL.Query(sql, ilistStr.ToArray());