正文
c#解决数据库用in的时候大于1000报错问题
小程序:扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序
【扫一扫了解最新限行尾号】
复制小程序
问题:
//oracle数据库报错
delete from DD_ORDER_DETAIL where ORDERID=in(0,1,2,3,4,5,6,7.....1001);
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 > )
{
StringBuilder sb = new StringBuilder();
for (int i = ; i < ids.Length; i++)
{
if ((i % ) == && i > )
{
sb.Remove(sb.Length - , );
sb.Append(") or " + str + "in(" + ids[i] + ",");
}
else
{
sb.Append(ids[i] + ",");
}
}
sb.Remove(sb.Length - , ); return str + "in(" + sb.ToString() + ")";
}
return "";
}
调用如下
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 = ; i < ; i++)
{
list.Add(i.ToString());
}
s = list.ToArray(); ;
Console.WriteLine(s.Length);
string ss = get(str, s);
Console.ReadKey();
}
效果如下
第二种:
//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 = ; i < ids.Length; i++)
{
if (i != && i % num == )
{
string s1 = sb.ToString();
s1 = s1.Remove(s1.Length - ) + ")";
lt.Add(s1);
sb.Length = ;
sb.Append(sqlStr);
sb.Append(ids[i] + ",");
if (i == ids.Length - )
{
string s2 = sb.ToString();
s2 = s2.Remove(s2.Length - ) + ")";
lt.Add(s2);
}
}
else
{
sb.Append(ids[i] + ",");
if (i == ids.Length - )
{
string s = sb.ToString();
s = s.Remove(s.Length - ) + ")";
lt.Add(s);
}
}
}
sqls = lt.ToArray();
return sqls;
}
调用
static void Main(string[] args)
{
List<string> list = new List<string>();
for (int i = ; i < ; i++)
{
list.Add(i.ToString());
}
string[] sqls = CreateSQL(list.ToArray(), "select * from tb_spkc where id in(", );
Console.ReadKey();
}
效果