public SearchCondition Add(string field, QueryMethod method, object val, string orGroup = "", string baseOrGroup = "baseOrGroup") { //处理日期型数据 if (method == QueryMethod.LessThan || method == QueryMethod.LessThanOrEqual) { if (val.GetType() == typeof(DateTime)) { DateTime t = (DateTime)val; val = t.Date.AddHours(23).AddMinutes(59).AddSeconds(59).ToString("yyyy-MM-dd HH:mm:ss"); } } ConditionItem item = new ConditionItem(field, method, val); item.OrGroup = orGroup; item.BaseOrGroup = baseOrGroup; Items.Add(item); return(this); }
public SearchCondition AddBetweenCondition(string field, object beginVal, object endVal, string orGroup = "Group1", string baseOrGroup = "baseOrGroup") { //处理日期型数据 if (endVal.GetType() == typeof(DateTime)) { DateTime t = (DateTime)endVal; endVal = t.Date.AddHours(23).AddMinutes(59).AddSeconds(59); } ConditionItem item = new ConditionItem(); item.Field = field; item.Method = QueryMethod.Between; item.Value = new[] { beginVal, endVal }; item.OrGroup = orGroup; item.BaseOrGroup = baseOrGroup; Items.Add(item); return(this); }
private string GetWhereString(ConditionItem item) { try { string str = ""; string value = "'" + item.Value + "'"; switch (item.Method) { case QueryMethod.Contains: case QueryMethod.StdIn: case QueryMethod.DateTimeLessThanOrEqual: break; case QueryMethod.Equal: str = string.Format("{0}={1}", item.Field, value); break; case QueryMethod.LessThan: str = string.Format("{0}<{1}", item.Field, value); break; case QueryMethod.GreaterThan: str = string.Format("{0}>{1}", item.Field, value); break; case QueryMethod.LessThanOrEqual: str = string.Format("{0}<={1}", item.Field, value); break; case QueryMethod.GreaterThanOrEqual: str = string.Format("{0}>={1}", item.Field, value); break; case QueryMethod.Like: str = string.Format("{0} LIKE '%{1}%'", item.Field, item.Value); break; case QueryMethod.NotIn: case QueryMethod.In: string strInValue = ""; if (item.Value is ICollection) { ICollection <string> collection = item.Value as ICollection <string>; strInValue = string.Join("','", collection.ToArray <string>()); } else { if (item.Value.ToString().Contains("{0}")) //连续枚举的标签查询,如时间字段枚举的标签查询 { str = item.Value.ToString().Replace("'{0}'", item.Field).Replace("{0}", item.Field) .Replace(",", " OR ") //下拉框传递过来的in查询直是逗号隔开的 .Replace("==", "=").Replace("&&", "AND").Replace("||", "OR"); break; } else if (item.Value.ToString().StartsWith("(") && item.Value.ToString().EndsWith(")")) //支持子查询的in,例如在数据权限授权对象中写子查询 { str = string.Format("{0} in {1}", item.Field, item.Value); break; } strInValue = item.Value.ToString().Replace(",", "','"); } if (item.Method == QueryMethod.In) { str = string.Format("{0} in ('{1}')", item.Field, strInValue); } else { str = string.Format("{0} not in ('{1}')", item.Field, strInValue); } break; case QueryMethod.InLike: string[] arr = null; if (item.Value is ICollection) { ICollection <string> collection = item.Value as ICollection <string>; arr = collection.ToArray <string>(); } else { arr = item.Value.ToString().Split(',', ','); } foreach (string s in arr) { str += string.Format("OR {0} LIKE '%{1}%'", item.Field, s); } str = "(" + str.Substring(2) + ")"; break; case QueryMethod.DateBlock: DateTime dt = DateTime.Now; if (!DateTime.TryParse(item.Value.ToString(), out dt)) { throw new Exception("查询条件不能转化为日期时间"); } string start = dt.Date.ToString("yyyy-MM-dd"); string end = dt.Date.AddDays(1).ToString("yyyy-MM-dd"); str = string.Format("{0} BETWEEN '{1}' AND '{2}'", item.Field, start, end); break; case QueryMethod.NotEqual: str = string.Format("{0}<>'{1}'", item.Field, item.Value); break; case QueryMethod.StartsWith: str = string.Format("{0} LIKE '{1}%'", item.Field, item.Value); break; case QueryMethod.EndsWith: str = string.Format("{0} LIKE '%{1}'", item.Field, item.Value); break; case QueryMethod.Between: object[] objs = item.Value as object[]; str = string.Format("{0} BETWEEN '{1}' AND '{2}'", item.Field, objs[0], objs[1]); break; case QueryMethod.IsEmpty: str = string.Format("(LEN({0})=0 OR LEN({0}) is null)", item.Field); break; } return(str); } catch (Exception ex) { throw new Exception(ex.Message); } }