在oracle中碰到需要in(:xx)的时候就直接传一个数组并且循坏加载就可以了。
将获取到类似("1,12,4,3,15")的数据作为条件传入查询语句中,可以先将该字符串转int[]数组,然后作为条件传参即可。
字符数组转Int数组
1 2 |
|
在查询语句那直接一个for循环依次加载数据。
private const string sqlQueryFunctionById = "select ID,name,url,isdel,ISEXTERNAL,JUMPLINK,XSSX from BAZS_CYGNSZ where id=:ids order by xssx desc";
public static List<FunctionSetting> QueryFunctionSettingByIds(int[] Ids)
{
List<FunctionSetting> list = new List<FunctionSetting>();
using (OracleConnection dbCon = OracleHelper.OpenConnection())
{
try
{
for (int i = 0; i < Ids.Length; i++)
{
OracleParameter[] para = new OracleParameter[1] { new OracleParameter(":ids", Ids[i]) };
IDataReader reader = OracleHelper.ExecuteReader(dbCon, CommandType.Text, sqlQueryFunctionById, para);
while (reader.Read())
{
FunctionSetting functionSetting = new FunctionSetting();
functionSetting.Id = reader.GetDecimal(0);
functionSetting.Name = reader.GetString(1);
functionSetting.Url = reader.GetString(2);
functionSetting.IsDel = reader.GetDecimal(3);
functionSetting.IsExternal = reader.GetDecimal(4);
functionSetting.JumpLink = reader.GetString(5);
int order = 0;
if (!reader.IsDBNull(6))
{
int.TryParse(reader[6].ToString(), out order);
}
functionSetting.DisplayOrder = order;
list.Add(functionSetting);
}
}
}
catch (Exception exc)
{
OracleLogWriter.WriteSystemLog($"{exc.Source}发生异常,异常信息{exc.Message},位置{exc.StackTrace}", "Error");
}
}
return list;
}
--------------------------------------------------------
如果查询有为null的字段想转换为0的话,可以用
NVl(a1,0) a1为null 返回0 否则为a1