您的位置: 首页 > IT文章 > c#解决数据库用in的时候大于1000报错问题

c#解决数据库用in的时候大于1000报错问题

分类: IT文章 • 2022-09-01 12:57:59

问题:

//oracle数据库报错
delete from DD_ORDER_DETAIL where ORDERID=in(0,1,2,3,4,5,6,7.....1001);

View Code

c#写了一个方法解决

/// <summary>
        /// 拼接sql
        /// </summary>
        /// <param name="str">主sql语句</param>
        /// <param name="ids">要拼接的in里面的参数</param>
        /// <param name="count">每次执行条数</param>
        /// <returns></returns>
        public static string get(string str, string[] ids)
        {

            if (ids.Length > 0)
            {
                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < ids.Length; i++)
                {
                    if ((i % 1000) == 0 && i > 0)
                    {
                        sb.Remove(sb.Length - 1, 1);
                        sb.Append(") or " + str + "in(" + ids[i] + ",");
                    }
                    else
                    {
                        sb.Append(ids[i] + ",");
                    }
                }
                sb.Remove(sb.Length - 1, 1);

                return str + "in(" + sb.ToString() + ")";
            }
            return "";
        }

View Code

调用如下

 static void Main(string[] args)
        {
            string str = "delete from DD_ORDER_DETAIL where ORDERID =";
            string[] s = { };
            List<string> list = new List<string>();
            for (int i = 0; i < 1001; i++)
            {
                list.Add(i.ToString());
            }
            s = list.ToArray(); ;
            Console.WriteLine(s.Length);
            string ss = get(str, s);
            Console.ReadKey();
        }

View Code

效果如下

c#解决数据库用in的时候大于1000报错问题

第二种:

//ids要插入in中的数据,sqlstr原始sql,num多少条执行一次
 public static string[] CreateSQL(string[] ids, string sqlStr, int num)
        {
            string[] sqls = null;
            StringBuilder sb = new StringBuilder();
            sb.Append(sqlStr);
            List<string> lt = new List<string>();
            for (int i = 0; i < ids.Length; i++)
            {
                if (i != 0 && i % num == 0)
                {
                    string s1 = sb.ToString();
                    s1 = s1.Remove(s1.Length - 1) + ")";
                    lt.Add(s1);
                    sb.Length = 0;
                    sb.Append(sqlStr);
                    sb.Append(ids[i] + ",");
                    if (i == ids.Length - 1)
                    {
                        string s2 = sb.ToString();
                        s2 = s2.Remove(s2.Length - 1) + ")";
                        lt.Add(s2);
                    }
                }
                else
                {
                    sb.Append(ids[i] + ",");
                    if (i == ids.Length - 1)
                    {
                        string s = sb.ToString();
                        s = s.Remove(s.Length - 1) + ")";
                        lt.Add(s);
                    }
                }
            }
            sqls = lt.ToArray();
            return sqls;
        }

View Code

调用

 static void Main(string[] args)
        {
            List<string> list = new List<string>();
            for (int i = 0; i < 550; i++)
            {
                list.Add(i.ToString());
            }
            string[] sqls = CreateSQL(list.ToArray(), "select * from tb_spkc where id in(", 500);
            Console.ReadKey();
        }

View Code

效果

c#解决数据库用in的时候大于1000报错问题

c#解决数据库用in的时候大于1000报错问题

相关推荐