where 1=1 and Dictionary[key1]=Dictionary[value1] and Dictionary[key2]=Dictionary[value3]。。。。
/// <summary>
/// 传入一个字段返回where条件
/// </summary>
/// <param>字段</param>
/// <param>可以为空</param>
/// <returns></returns>
protected string GetWhere(Dictionary<string, object> where, string tableAlias)
{
string sql = "";
string aliasName = string.Empty;
if (!string.IsNullOrEmpty(tableAlias))
{
aliasName = tableAlias + ".";
}
if (where == null)
{
return sql;
}
string joinList = string.Empty;
foreach (var item in where)
{
if (string.IsNullOrEmpty(sql))
{
sql += " where ";
}
else if (!string.IsNullOrEmpty(sql.Replace("where", "").Trim()))
{
sql += " and ";
}
string speCode = "┝┝┞┞├├┼┽┾┿╀╂┣─┐┎";
if (item.Value == null)
{
sql += "nvl(" + aliasName + item.Key + ",‘"+speCode+"‘) = ‘"+speCode+"‘";
}
else if (string.IsNullOrEmpty(item.Value.ToString()))
{
sql += "nvl(" + aliasName + item.Key + ",‘"+speCode+"‘) = ‘"+speCode+"‘";
}
else if (item.Value is List<string>)
{
List<string> valueList = (List<string>)item.Value;
string valueStr = "";
if (valueList.Count > 1)
{
//sql += " in (" + valueStr + ") ";
int strIndex = 0;
valueList = valueList.Distinct().ToList();
foreach (var str in valueList)
{
if (strIndex > 0)
{
valueStr += " union all ";
}
valueStr += " select ‘" + str + "‘ as TEMP" + item.Key + " from dual ";
strIndex++;
}
joinList += " inner join (" + valueStr + ") " + item.Key + "TABLE ON " + aliasName + item.Key +
"=" + item.Key + "TABLE.TEMP" + item.Key;
if (sql.EndsWith("and "))
{
sql = sql.Substring(0, sql.Length - 4);
}
}
else
{
foreach (var str in valueList)
{
valueStr += "‘" + str + "‘,";
}
valueStr = valueStr.TrimEnd(‘,‘);
sql += aliasName + item.Key;
sql += " = " + valueStr + " ";
}
}
else if (item.Value is List<int>)
{
List<int> valueList = (List<int>)item.Value;
string valueStr = "";
foreach (var str in valueList)
{
valueStr += str + ",";
}
valueStr = valueStr.TrimEnd(‘,‘);
sql += aliasName + item.Key;
if (valueList.Count > 1)
{
sql += " in (" + valueStr + ") ";
}
else
{
sql += " = " + valueStr + " ";
}
}
else if (item.Value is List<decimal>)
{
List<decimal> valueList = (List<decimal>)item.Value;
string valueStr = "";
foreach (var str in valueList)
{
valueStr += str + ",";
}
valueStr = valueStr.TrimEnd(‘,‘);
sql += aliasName + item.Key;
if (valueList.Count > 1)
{
sql += " in (" + valueStr + ") ";
}
else
{
sql += " = " + valueStr + " ";
}
}
else if (item.Value is string)
{
sql += aliasName + item.Key + " = ‘" + item.Value.ToString() + "‘";
}
else
{
sql += aliasName + item.Key + " = " + item.Value.ToString();
}
}
if (string.IsNullOrEmpty(sql.Replace("where", "").Trim()))
{
sql += " 1=1 ";
}
//选择是否需要比如分区控制之类的
//if (!where.ContainsKey("F_SITE_ID"))
//{
//sql += CommomBus.GetPatitionFilter(tableAlias);
//}
return joinList + " " + sql;
}