c# sql where in 参数化传值

第一种：传统Sql的Where IN（）值拼接

不推荐，有sql注入风险

//传统Sql的Where IN（）值拼装
 string str = "1,2,3"; 
 StringBuilder sql = new StringBuilder();
 sql.Append("select * from user where userId in(");
 sql.Append(str);
 sql.Append(")");
 string sqlStr = sql.ToString();
 Console.WriteLine(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", SqlDbType.Int) { Value = 1},
                new SqlParameter("@UserID2", SqlDbType.Int) { Value = 2},
                new SqlParameter("@UserID3", SqlDbType.Int) { Value = 3},
                new SqlParameter("@UserID4", SqlDbType.Int) { 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（）的拼接工厂 

/// <summary>
        ///  Sql的Where IN（）的拼接工厂
        /// </summary>
        /// <param name="parameter">parameter数组</param>
        /// <param name="IdArray">ID数组 如：1,2,3</param>
        /// <param name="sqlWhereInValue">接收生成的sql字符串变量</param>
        private static DbParameter[] WhereInFactory(DbParameter[] parameter, string IdArray, out string sqlWhereInValue)
        {

            string[] strArray = IdArray.Split(',');
            int p_length = parameter.Count();
            int s_length = strArray.Count();
            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 ? ",":"";
                    sqlWhereIn.Append("@sqlWhereInValue" + j+ dot);
                    dbParameters[i] = new SqlParameter("@sqlWhereInValue" + j, Convert.ToInt32(strArray[j]));
                    j++;
                }
            }
            sqlWhereInValue = $"({sqlWhereIn.ToString()})";
            return dbParameters;
        }

---

完整代码

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace test1
{
    /// <summary>
    /// Sql的Where IN（）Test
    /// </summary>
    public class SqlWhereInTest
    {
        /// <summary>
        /// 执行
        /// </summary>
        public static void Exe()
        {
            //数组长度测试
            DbParameter[] sqlParameters6 = new DbParameter[6];
            DbParameter[] sqlParameters10 = new DbParameter[10];
            sqlParameters6 = sqlParameters10;

            {
                //传统Sql的Where IN（）值拼装
                string str = "1,2,3"; 
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from user where userId in(");
                sql.Append(str);
                sql.Append(")");
                string sqlStr = sql.ToString();
                Console.WriteLine(sqlStr);
            }

            {
                //Sql的Where IN（）参数化值拼装 
                string strArray = "1,2,3";
                string sql = "select * from user where userId in";

                //sqlParameters有值传递
                DbParameter[] sqlParameters1 = {
                new SqlParameter("@UserID1", SqlDbType.Int) { Value = 1},
                new SqlParameter("@UserID2", SqlDbType.Int) { Value = 2},
                new SqlParameter("@UserID3", SqlDbType.Int) { Value = 3},
                new SqlParameter("@UserID4", SqlDbType.Int) { 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;

                Console.WriteLine(strArray);
            }

          

        }

        /// <summary>
        ///  Sql的Where IN（）的拼接工厂
        /// </summary>
        /// <param name="parameter">parameter数组</param>
        /// <param name="IdArray">ID数组 如：1,2,3</param>
        /// <param name="sqlWhereInValue">接收生成的sql字符串变量</param>
        private static DbParameter[] WhereInFactory(DbParameter[] parameter, string IdArray, out string sqlWhereInValue)
        {

            string[] strArray = IdArray.Split(',');
            int p_length = parameter.Count();
            int s_length = strArray.Count();
            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 ? ",":"";
                    sqlWhereIn.Append("@sqlWhereInValue" + j+ dot);
                    dbParameters[i] = new SqlParameter("@sqlWhereInValue" + j, Convert.ToInt32(strArray[j]));
                    j++;
                }
            }
            sqlWhereInValue = $"({sqlWhereIn.ToString()})";
            return dbParameters;
        }
    }
}