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

浏览数：39 / 时间：2015年06月12日

问题:

//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();
        }