SQL查询使用的IN条件字段很多的时候,会造成SQL语句很长,大概SQL语句不能超过8K个字符,也有说IN不能超过1000个条件,总之太长了不行,需要拆分条件分批处理。下面提供一个将Int类型的条件字段值进行字符串拼接的方法。看代码:
class Program
{
static void Main(string[] args)
{
int[] source = { 1,2,3,4,5,6,7,8,9,10};
ShowSource(source);
List<string> list = BatchJoinArray2String(source, 5).ToList();
ShowList(list);
List<string> list2 = BatchJoinArray2String(source, 3).ToList();
ShowList(list2);
Console.Read();
}
static void ShowSource(int[] source)
{
string sourceStr = string.Join(",", source);
Console.WriteLine("Source Arrar:{0}", sourceStr);
}
static void ShowList(List<string> lst)
{
foreach (string item in lst)
Console.WriteLine("\""+item+"\"");
Console.WriteLine("--------------------");
}
static IEnumerable<string> BatchJoinArray2String(int[] arrSource,int batchSize)
{
if (batchSize <= 1) throw new ArgumentOutOfRangeException("batchSize 批处理大小不能小于1");
if (arrSource.Length > batchSize)
{
int[] arr10 = new int[batchSize];
int j = 0;
for (int i = 0; i < arrSource.Length; i++)
{
if (j < batchSize)
{
arr10[j++] = arrSource[i];
}
else
{
j = 0;
string str = string.Join(",", arr10);
arr10[j++] = arrSource[i];
yield return str;
}
}
if (j > 0) //还有剩余
{
int[] arr0 = new int[j];
Array.Copy(arr10, arr0, j);
string str = string.Join(",", arr0);
yield return str;
}
}
else
{
string str = string.Join(",", arrSource);
yield return str;
}
}
}
运行这个示例程序,得到下面输出:
Source Arrar:1,2,3,4,5,6,7,8,9,10
"1,2,3,4,5"
"6,7,8,9,10"
--------------------
"1,2,3"
"4,5,6"
"7,8,9"
"10"
--------------------
在你的程序中,可以像下面这样使用:
string sql_update=@"
update t2 set
AA =1 ,
BB ='2222'
FROM [MyTable] as t2
WHERE t2.[ID] in ( @IDs );
";
//每次更新50条记录
using (SqlConnection conn = new SqlConnection(DefaultConnectionString))
{
conn.Open();
foreach (string ids in BatchJoinArray2String(XXXIds.ToArray(), 50))
{
string sql = sql_update.Replace("@IDs", ids);
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
}
conn.Close();
}
该功能将集成在SOD框架中,敬请期待。