private Hashtable method_2()
{
Hashtable hashtable = new Hashtable();
SearchInfo info = null;
foreach (DictionaryEntry entry in this.hashtable_0)
{
info = (SearchInfo)entry.Value;
if (!(string.IsNullOrEmpty(info.GroupName) || hashtable.Contains(info.GroupName)))
{
hashtable.Add(info.GroupName, info.GroupName);
}
}
return(hashtable);
}
/// <summary>
/// 获取给定条件集合的组别对象集合
/// </summary>
/// <returns></returns>
private Hashtable GetGroupNames()
{
Hashtable htGroupNames = new Hashtable();
SearchInfo searchInfo = null;
foreach (DictionaryEntry de in this.conditionTable)
{
searchInfo = (SearchInfo)de.Value;
if (!string.IsNullOrEmpty(searchInfo.GroupName) && !htGroupNames.Contains(searchInfo.GroupName))
{
htGroupNames.Add(searchInfo.GroupName, searchInfo.GroupName);
}
}
return(htGroupNames);
}
/// <summary>
/// 建立分组条件
/// </summary>
/// <returns></returns>
private string BuildGroupCondiction(DatabaseType dbType)
{
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)
{
searchInfo = (SearchInfo)de.Value;
TypeCode typeCode = Type.GetTypeCode(searchInfo.FieldValue.GetType());
//如果选择ExcludeIfEmpty为True,并且该字段为空值的话,跳过
if (searchInfo.ExcludeIfEmpty &&
(searchInfo.FieldValue == null || string.IsNullOrEmpty(searchInfo.FieldValue.ToString())))
{
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.FieldValue));
}
else if (searchInfo.SqlOperator == SqlOperator.NotLike)
{
sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("%{0}%", searchInfo.FieldValue));
}
else if (searchInfo.SqlOperator == SqlOperator.LikeStartAt)
{
sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("{0}%", searchInfo.FieldValue));
}
else
{
if (dbType == DatabaseType.Oracle)
{
#region Oracle分组
if (IsDate(searchInfo.FieldValue.ToString()))
{
sb.AppendFormat(" OR {0} {1} to_date('{2}','YYYY-MM-dd')", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
}
else if (IsDateHourMinute(searchInfo.FieldValue.ToString()))
{
sb.AppendFormat(" OR {0} {1} to_date('{2}','YYYY-MM-dd HH:mi')", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
}
else if (!searchInfo.ExcludeIfEmpty)
{
//如果要进行空值查询的时候
if (searchInfo.SqlOperator == SqlOperator.Equal)
{
sb.AppendFormat(" OR ({0} is null or {0}='')", searchInfo.FieldName);
}
else if (searchInfo.SqlOperator == SqlOperator.NotEqual)
{
sb.AppendFormat(" OR {0} is not null", searchInfo.FieldName);
}
}
else
{
sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
}
#endregion
}
else if (dbType == DatabaseType.Access)
{
#region Access分组
if (typeCode == TypeCode.DateTime)
{
sb.AppendFormat(" OR {0} {1} #{2}#", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
}
else if (typeCode == TypeCode.Byte || typeCode == TypeCode.Decimal || typeCode == TypeCode.Double ||
typeCode == TypeCode.Int16 || typeCode == TypeCode.Int32 || typeCode == TypeCode.Int64 ||
typeCode == TypeCode.SByte || typeCode == TypeCode.Single || typeCode == TypeCode.UInt16 ||
typeCode == TypeCode.UInt32 || typeCode == TypeCode.UInt64)
{
//数值类型操作
sb.AppendFormat(" OR {0} {1} {2}", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
}
else
{
sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
}
#endregion
}
else //if (dbType == DatabaseType.SqlServer)
{
#region SqlServer分组
if (searchInfo.SqlOperator == SqlOperator.Like)
{
sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("%{0}%", searchInfo.FieldValue));
}
else
{
sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
}
#endregion
}
}
}
}
if (!string.IsNullOrEmpty(sb.ToString()))
{
tempSql = string.Format(tempSql, sb.ToString().Substring(3));//从第一个Or开始位置
sql += tempSql;
}
}
return(sql);
}
/// <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 BuildConditionSql(DatabaseType dbType)
{
string sql = " Where (1=1) ";
string fieldName = string.Empty;
SearchInfo searchInfo = null;
StringBuilder sb = new StringBuilder();
sql += BuildGroupCondiction(dbType);
foreach (DictionaryEntry de in this.conditionTable)
{
searchInfo = (SearchInfo)de.Value;
TypeCode typeCode = Type.GetTypeCode(searchInfo.FieldValue.GetType());
//如果选择ExcludeIfEmpty为True,并且该字段为空值的话,跳过
if (searchInfo.ExcludeIfEmpty &&
(searchInfo.FieldValue == null || string.IsNullOrEmpty(searchInfo.FieldValue.ToString())))
{
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.FieldValue));
}
else if (searchInfo.SqlOperator == SqlOperator.NotLike)
{
sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("%{0}%", searchInfo.FieldValue));
}
else if (searchInfo.SqlOperator == SqlOperator.LikeStartAt)
{
sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("{0}%", searchInfo.FieldValue));
}
else if (searchInfo.SqlOperator == SqlOperator.In)
{
sb.AppendFormat(" AND {0} {1} {2}", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("({0})", searchInfo.FieldValue));
}
else
{
if (dbType == DatabaseType.Oracle)
{
#region 特殊Oracle操作
if (IsDate(searchInfo.FieldValue.ToString()))
{
sb.AppendFormat(" AND {0} {1} to_date('{2}','YYYY-MM-dd')", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
}
else if (IsDateHourMinute(searchInfo.FieldValue.ToString()))
{
sb.AppendFormat(" AND {0} {1} to_date('{2}','YYYY-MM-dd HH:mi')", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
}
else if (!searchInfo.ExcludeIfEmpty)
{
//如果要进行空值查询的时候
if (searchInfo.SqlOperator == SqlOperator.Equal)
{
sb.AppendFormat(" AND ({0} is null or {0}='')", searchInfo.FieldName);
}
else if (searchInfo.SqlOperator == SqlOperator.NotEqual)
{
sb.AppendFormat(" AND {0} is not null", searchInfo.FieldName);
}
}
else
{
sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
}
#endregion
}
else if (dbType == DatabaseType.Access)
{
#region 特殊Access操作
if (searchInfo.SqlOperator == SqlOperator.Equal &&
typeCode == TypeCode.String && string.IsNullOrEmpty(searchInfo.FieldValue.ToString()))
{
sb.AppendFormat(" AND ({0} {1} '{2}' OR {0} IS NULL)", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
}
else
{
if (typeCode == TypeCode.DateTime)
{
sb.AppendFormat(" AND {0} {1} #{2}#", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
}
else if (typeCode == TypeCode.Byte || typeCode == TypeCode.Decimal || typeCode == TypeCode.Double ||
typeCode == TypeCode.Int16 || typeCode == TypeCode.Int32 || typeCode == TypeCode.Int64 ||
typeCode == TypeCode.SByte || typeCode == TypeCode.Single || typeCode == TypeCode.UInt16 ||
typeCode == TypeCode.UInt32 || typeCode == TypeCode.UInt64)
{
//数值类型操作
sb.AppendFormat(" AND {0} {1} {2}", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
}
else
{
sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
}
}
#endregion
}
else //if (dbType == DatabaseType.SqlServer)
{
sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);
}
}
}
}
sql += sb.ToString();
return(sql);
}
public string BuildConditionSql(DatabaseType dbType)
{
string str = " Where (1=1) ";
SearchInfo info = null;
StringBuilder builder = new StringBuilder();
str = str + this.method_1(dbType);
foreach (DictionaryEntry entry in this.hashtable_0)
{
info = (SearchInfo)entry.Value;
TypeCode typeCode = Type.GetTypeCode(info.FieldValue.GetType());
if ((!info.ExcludeIfEmpty || ((info.FieldValue != null) && !string.IsNullOrEmpty(info.FieldValue.ToString()))) && string.IsNullOrEmpty(info.GroupName))
{
if (info.SqlOperator == SqlOperator.Like)
{
builder.AppendFormat(" AND {0} {1} '{2}'", info.FieldName, this.method_3(info.SqlOperator), string.Format("%{0}%", info.FieldValue));
continue;
}
if (info.SqlOperator == SqlOperator.NotLike)
{
builder.AppendFormat(" AND {0} {1} '{2}'", info.FieldName, this.method_3(info.SqlOperator), string.Format("%{0}%", info.FieldValue));
continue;
}
if (info.SqlOperator == SqlOperator.LikeStartAt)
{
builder.AppendFormat(" AND {0} {1} '{2}'", info.FieldName, this.method_3(info.SqlOperator), string.Format("{0}%", info.FieldValue));
continue;
}
if (info.SqlOperator == SqlOperator.In)
{
builder.AppendFormat(" AND {0} {1} {2}", info.FieldName, this.method_3(info.SqlOperator), string.Format("({0})", info.FieldValue));
continue;
}
if (dbType == DatabaseType.Oracle)
{
if (typeCode == TypeCode.DateTime)
{
DateTime time = Convert.ToDateTime(info.FieldValue);
if ((time.Hour > 0) || (time.Minute > 0))
{
builder.AppendFormat(" AND {0} {1} to_date('{2}','YYYY-MM-dd HH:mi')", info.FieldName, this.method_3(info.SqlOperator), time.ToString("yyyy-MM-dd HH:mm"));
}
else
{
builder.AppendFormat(" AND {0} {1} to_date('{2}','YYYY-MM-dd')", info.FieldName, this.method_3(info.SqlOperator), time.ToString("yyyy-MM-dd"));
}
}
else if (!info.ExcludeIfEmpty)
{
if (info.SqlOperator == SqlOperator.Equal)
{
builder.AppendFormat(" AND ({0} is null or {0}='')", info.FieldName);
}
else if (info.SqlOperator == SqlOperator.NotEqual)
{
builder.AppendFormat(" AND {0} is not null", info.FieldName);
}
}
else
{
builder.AppendFormat(" AND {0} {1} '{2}'", info.FieldName, this.method_3(info.SqlOperator), info.FieldValue);
}
continue;
}
if (dbType == DatabaseType.Access)
{
if (((info.SqlOperator == SqlOperator.Equal) && (typeCode == TypeCode.String)) && string.IsNullOrEmpty(info.FieldValue.ToString()))
{
builder.AppendFormat(" AND ({0} {1} '{2}' OR {0} IS NULL)", info.FieldName, this.method_3(info.SqlOperator), info.FieldValue);
}
else if (typeCode == TypeCode.DateTime)
{
builder.AppendFormat(" AND {0} {1} #{2}#", info.FieldName, this.method_3(info.SqlOperator), info.FieldValue);
}
else if ((((((typeCode == TypeCode.Byte) || (typeCode == TypeCode.Decimal)) || ((typeCode == TypeCode.Double) || (typeCode == TypeCode.Int16))) || (((typeCode == TypeCode.Int32) || (typeCode == TypeCode.Int64)) || ((typeCode == TypeCode.SByte) || (typeCode == TypeCode.Single)))) || ((typeCode == TypeCode.UInt16) || (typeCode == TypeCode.UInt32))) || (typeCode == TypeCode.UInt64))
{
builder.AppendFormat(" AND {0} {1} {2}", info.FieldName, this.method_3(info.SqlOperator), info.FieldValue);
}
else
{
builder.AppendFormat(" AND {0} {1} '{2}'", info.FieldName, this.method_3(info.SqlOperator), info.FieldValue);
}
continue;
}
if (dbType == DatabaseType.SQLite)
{
if (typeCode == TypeCode.DateTime)
{
builder.AppendFormat(" AND {0} {1} '{2}' ", info.FieldName, this.method_3(info.SqlOperator), Convert.ToDateTime(info.FieldValue).ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
builder.AppendFormat(" AND {0} {1} '{2}'", info.FieldName, this.method_3(info.SqlOperator), info.FieldValue);
}
continue;
}
builder.AppendFormat(" AND {0} {1} '{2}'", info.FieldName, this.method_3(info.SqlOperator), info.FieldValue);
}
}
return(str + builder.ToString());
}
private string method_1(DatabaseType databaseType_0)
{
Hashtable hashtable = this.method_2();
SearchInfo info = null;
StringBuilder builder = new StringBuilder();
string str = string.Empty;
string format = string.Empty;
foreach (string str3 in hashtable.Keys)
{
builder = new StringBuilder();
format = " AND ({0})";
foreach (DictionaryEntry entry in this.hashtable_0)
{
info = (SearchInfo)entry.Value;
TypeCode typeCode = Type.GetTypeCode(info.FieldValue.GetType());
if ((!info.ExcludeIfEmpty || ((info.FieldValue != null) && !string.IsNullOrEmpty(info.FieldValue.ToString()))) && str3.Equals(info.GroupName, StringComparison.OrdinalIgnoreCase))
{
if (info.SqlOperator == SqlOperator.Like)
{
builder.AppendFormat(" OR {0} {1} '{2}'", info.FieldName, this.method_3(info.SqlOperator), string.Format("%{0}%", info.FieldValue));
continue;
}
if (info.SqlOperator == SqlOperator.NotLike)
{
builder.AppendFormat(" OR {0} {1} '{2}'", info.FieldName, this.method_3(info.SqlOperator), string.Format("%{0}%", info.FieldValue));
continue;
}
if (info.SqlOperator == SqlOperator.LikeStartAt)
{
builder.AppendFormat(" OR {0} {1} '{2}'", info.FieldName, this.method_3(info.SqlOperator), string.Format("{0}%", info.FieldValue));
continue;
}
if (databaseType_0 == DatabaseType.Oracle)
{
if (typeCode == TypeCode.DateTime)
{
DateTime time = Convert.ToDateTime(info.FieldValue);
if ((time.Hour > 0) || (time.Minute > 0))
{
builder.AppendFormat(" OR {0} {1} to_date('{2}','YYYY-MM-dd HH:mi')", info.FieldName, this.method_3(info.SqlOperator), time.ToString("yyyy-MM-dd HH:mm"));
}
else
{
builder.AppendFormat(" OR {0} {1} to_date('{2}','YYYY-MM-dd')", info.FieldName, this.method_3(info.SqlOperator), time.ToString("yyyy-MM-dd"));
}
}
else if (!info.ExcludeIfEmpty)
{
if (info.SqlOperator == SqlOperator.Equal)
{
builder.AppendFormat(" OR ({0} is null or {0}='')", info.FieldName);
}
else if (info.SqlOperator == SqlOperator.NotEqual)
{
builder.AppendFormat(" OR {0} is not null", info.FieldName);
}
}
else
{
builder.AppendFormat(" OR {0} {1} '{2}'", info.FieldName, this.method_3(info.SqlOperator), info.FieldValue);
}
continue;
}
if (databaseType_0 == DatabaseType.Access)
{
if (typeCode == TypeCode.DateTime)
{
builder.AppendFormat(" OR {0} {1} #{2}#", info.FieldName, this.method_3(info.SqlOperator), info.FieldValue);
}
else if ((((((typeCode == TypeCode.Byte) || (typeCode == TypeCode.Decimal)) || ((typeCode == TypeCode.Double) || (typeCode == TypeCode.Int16))) || (((typeCode == TypeCode.Int32) || (typeCode == TypeCode.Int64)) || ((typeCode == TypeCode.SByte) || (typeCode == TypeCode.Single)))) || ((typeCode == TypeCode.UInt16) || (typeCode == TypeCode.UInt32))) || (typeCode == TypeCode.UInt64))
{
builder.AppendFormat(" OR {0} {1} {2}", info.FieldName, this.method_3(info.SqlOperator), info.FieldValue);
}
else
{
builder.AppendFormat(" OR {0} {1} '{2}'", info.FieldName, this.method_3(info.SqlOperator), info.FieldValue);
}
continue;
}
if (databaseType_0 == DatabaseType.SQLite)
{
if (typeCode == TypeCode.DateTime)
{
builder.AppendFormat(" OR {0} {1} '{2}' ", info.FieldName, this.method_3(info.SqlOperator), Convert.ToDateTime(info.FieldValue).ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
builder.AppendFormat(" OR {0} {1} '{2}'", info.FieldName, this.method_3(info.SqlOperator), info.FieldValue);
}
continue;
}
if (info.SqlOperator == SqlOperator.Like)
{
builder.AppendFormat(" OR {0} {1} '{2}'", info.FieldName, this.method_3(info.SqlOperator), string.Format("%{0}%", info.FieldValue));
}
else
{
builder.AppendFormat(" OR {0} {1} '{2}'", info.FieldName, this.method_3(info.SqlOperator), info.FieldValue);
}
}
}
if (!string.IsNullOrEmpty(builder.ToString()))
{
format = string.Format(format, builder.ToString().Substring(3));
str = str + format;
}
}
return(str);
}