/// <summary> /// return where condition combine with param name /// add param to command /// </summary> /// <param name="condition"></param> /// <param name="command"></param> /// <returns></returns> public static DbCommand BuildWhereCondition(this IEnumerable<SearchCondition> conditions, DbCommand command, DALContext context) { StringBuilder sb = new StringBuilder(); foreach (var condition in conditions) { if (sb.Length == 0) { sb.Append(" where "); } sb.Append(string.Format(conditionFormat, condition.Name, GetConditionStringBySearchType(condition, command, context))); } var reg = new Regex(positionReg, RegexOptions.IgnoreCase); string strParam = sb.ToString().TrimEnd("and".ToCharArray()); if (reg.IsMatch(command.CommandText)) { command.CommandText = reg.Replace(command.CommandText, strParam); } else { command.CommandText = command.CommandText + strParam; } return command; }
private static string GetConditionStringBySearchType(SearchCondition condition, DbCommand command, DALContext context) { var searchType = condition.SearchType; string name = condition.Name.Replace(".", "_"); //add by wucui,用于&的情况 name = name.Replace("&", "_"); List<KeyValuePair<string, object>> values = new List<KeyValuePair<string, object>>(); string columnName = "@" + name; values.Add(new KeyValuePair<string, object>(columnName, condition.Value)); string symbol = "="; string format = symbolFormat; switch (searchType) { case SerarchType.GreaterThan: symbol = ">"; break; case SerarchType.GreaterThanOrEqual: symbol = ">="; break; case SerarchType.LessThan: symbol = "<"; break; case SerarchType.LessThanOrEqual: symbol = "<="; break; case SerarchType.Like: symbol = "like"; format = "{0} '%'+{1}+'%'"; break; case SerarchType.NotEqual: symbol = "<>"; break; case SerarchType.In: symbol = "in"; values = new List<KeyValuePair<string, object>>(); var conditions = condition.Value.ToString().Split(','); for (var i = 0; i < conditions.Length; i++) { var conObj = conditions[i]; var conName = columnName + i.ToString(); values.Add(new KeyValuePair<string, object>(conName, conObj)); } format = "{0} ({1})"; break; case SerarchType.Equal: default: break; } StringBuilder returnValue = new StringBuilder(); foreach (var value in values) { var key = GetKey(value.Key, context, command); context.DB.AddInParameter(command, key, condition.dbType, value.Value); returnValue.Append(key + ","); } return string.Format(format, symbol, returnValue.ToString().Trim(',')); }
private static string GetKey(string key, DALContext context, DbCommand command) { try { if (command.Parameters[key] != null) { string[] strs = key.Split('_'); string cIndex = strs[strs.Length - 1]; int index = 0; int.TryParse(cIndex.ToString(), out index); key = key.Replace("_" + index, "") + "_" + ++index; return GetKey(key, context, command); } else { return key; } } catch (IndexOutOfRangeException) { return key; } }