/// <summary>
/// 建立分组条件
/// </summary>
/// <returns></returns>
private string BuildGroupParameterCondiction()
{
Hashtable ht = GetGroupNames();
SearchInfo searchInfo = null;
StringBuilder sb = new StringBuilder();
string sql = string.Empty;
string tempSql = string.Empty;
foreach (string groupName in ht.Keys)
{
sb = new StringBuilder();
tempSql = " AND ({0})";
foreach (DictionaryEntry de in this.conditionTable)
{
if (de.Value != null)
{
if (de.Value.GetType() != typeof(SearchInfo))
{
continue;
}
searchInfo = (SearchInfo)de.Value;
//如果选择ExcludeIfEmpty为True,并且该字段为空值的话,跳过
if (searchInfo.ExcludeIfEmpty &&
(searchInfo.FieldValue == null || string.IsNullOrEmpty(searchInfo.FieldValue.ToString().Trim())))
{
continue;
}
if (groupName.Equals(searchInfo.GroupName, StringComparison.OrdinalIgnoreCase))
{
if (searchInfo.SqlOperator == SqlOperator.Like)
{
sb.AppendFormat(" OR {0} {1} {2}", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format(":{0}", searchInfo.ParameterName));
}
else
{
sb.AppendFormat(" OR {0} {1} {2}", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format(":{0}", searchInfo.ParameterName));
}
}
}
}
if (!string.IsNullOrEmpty(sb.ToString()))
{
tempSql = string.Format(tempSql, sb.ToString().Substring(3));//从第一个Or开始位置
sql += tempSql;
}
}
return(sql);
}
public void SetParameterValue(ISQLQuery query)
{
foreach (DictionaryEntry de in this.ConditionTable)
{
if (de.Value != null)
{
if (de.Value.GetType() != typeof(SearchInfo))
{
continue;
}
SearchInfo searchInfo = (SearchInfo)de.Value;
if (searchInfo.FieldValue != null && !string.IsNullOrEmpty(searchInfo.FieldValue.ToString().Trim()))
{
query.SetParameter(string.Format("{0}", searchInfo.ParameterName), searchInfo.FieldValue);
}
}
}
}
/// <summary>
/// 获取给定条件集合的组别对象集合
/// </summary>
/// <returns></returns>
private Hashtable GetGroupNames()
{
Hashtable htGroupNames = new Hashtable();
SearchInfo searchInfo = null;
foreach (DictionaryEntry de in this.conditionTable)
{
if (de.Value != null)
{
if (de.Value.GetType() != typeof(SearchInfo))
{
continue;
}
searchInfo = (SearchInfo)de.Value;
if (!string.IsNullOrEmpty(searchInfo.GroupName) && !htGroupNames.Contains(searchInfo.GroupName))
{
htGroupNames.Add(searchInfo.GroupName, searchInfo.GroupName);
}
}
}
return(htGroupNames);
}
/// <summary>
/// 根据对象构造相关的条件语句（不使用参数），如返回的语句是:
/// <![CDATA[
/// Where (1=1) AND Test4 < 'Value4' AND Test6 >= 'Value6' AND Test7 <= 'value7' AND Test <> '1' AND Test5 > 'Value5' AND Test2 Like '%Value2%' AND Test3 = 'Value3'
/// ]]>
/// </summary>
/// <returns></returns>
public string BuildParameterConditionSql(bool withWhere)
{
string sql = "";
if (withWhere)
{
sql = " Where (1=1) ";
}
else
{
sql = "(1=1)";
}
string fieldName = string.Empty;
SearchInfo searchInfo = null;
StringBuilder sb = new StringBuilder();
sql += BuildGroupParameterCondiction();
foreach (DictionaryEntry de in this.conditionTable)
{
if (de.Value != null)
{
if (de.Value.GetType() != typeof(SearchInfo))
{
continue;
}
searchInfo = (SearchInfo)de.Value;
//如果只是作为参数，不参与sql条件的生成。
if (searchInfo.ParameterOnly)
{
continue;
}
//如果选择ExcludeIfEmpty为True,并且该字段为空值的话,跳过
if (searchInfo.ExcludeIfEmpty &&
(searchInfo.FieldValue == null || string.IsNullOrEmpty(searchInfo.FieldValue.ToString().Trim())))
{
continue;
}
//只有组别名称为空才继续，即正常的sql条件
if (string.IsNullOrEmpty(searchInfo.GroupName))
{
if (searchInfo.SqlOperator == SqlOperator.Like)
{
sb.AppendFormat(" AND {0} {1} {2}", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format(":{0}", searchInfo.ParameterName));
}
else if (searchInfo.SqlOperator == SqlOperator.In)
{
sb.AppendFormat(" AND {0} {1} {2}", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("(:{0})", searchInfo.ParameterName));
}
else
{
sb.AppendFormat(" AND {0} {1} {2}", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format(":{0}", searchInfo.ParameterName));
}
}
}
}
sql += sb.ToString();
return(sql);
}
