c# sql where in 参数化传值

90 篇文章 7 订阅
27 篇文章 2 订阅

第一种:传统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;
        }
    }
}

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

橙-极纪元JJY.Cheng

客官,1分钱也是爱,给个赏钱吧

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值