/// <summary> /// Converts the given comparison operator to /// an SQL operator formatted as a string. /// </summary> /// <param name="value">Value to convert.</param> /// <returns>String SQL operator matching the value.</returns> public static string ToSqlOperator(this ComparisonOperatorEnum value) { switch (value) { case ComparisonOperatorEnum.Equal: return("="); case ComparisonOperatorEnum.Different: return("<>"); case ComparisonOperatorEnum.Greater: return(">"); case ComparisonOperatorEnum.GreaterOrEqual: return(">="); case ComparisonOperatorEnum.Less: return("<"); case ComparisonOperatorEnum.LessOrEqual: return("<="); default: throw new ArgumentException( string.Format("Unknown operator: {0}.", value)); } }
/// <summary> /// Create a FilterExpression with the indicated values /// </summary> /// <param name="fieldName">The name of the Field to filter on</param> /// <param name="searchVal">The Field value to filter on</param> /// <param name="equality">The Equality operator to use in the value comparison</param> /// <param name="matchType">The match type, eg. MatchType.Exact</param> /// <param name="isNot">Operator negation</param> /// public FilterExpression(string fieldName, object searchVal, ComparisonOperatorEnum equality, MatchTypeEnum matchType, bool isNot) { FieldName = fieldName; SearchVal = searchVal; MatchType = matchType; Equality = equality; IsNot = isNot; }
static void parseExpression(string filter, ref int pos, FilterExpressionGroup parentSrchExpGrp) { ParseState state = ParseState.Left; bool hasBracket = false, inString = false, isNot = false; string fieldName = null; object searchVal = null; var sbTemp = new StringBuilder(); ComparisonOperatorEnum comparisonOp = ComparisonOperatorEnum.Equal; MatchTypeEnum matchType = MatchTypeEnum.UseCase; BoolOpEnum curBoolOp = BoolOpEnum.And; int startPos = pos; // skip past any leading spaces while (pos < filter.Length && char.IsWhiteSpace(filter[pos])) { pos++; } for ( ; pos < filter.Length;) { ////////////////////////////////////////////////////////// #region Left if (state == ParseState.Left) { if (filter[pos] == '[') // field names with ' ' in them must be wrapped with brackets { hasBracket = true; pos++; startPos = pos; } if (hasBracket) { // look for ending bracket if (filter[pos] == ']') { fieldName = filter.Substring(startPos, pos - startPos).Trim(); pos++; // skip past bracket } } else // no bracket - look for non-alpha { if (filter[pos] == '(') { // start of a new FilterExpressionGroup pos++; var newSrchExpGrp = new FilterExpressionGroup(); parentSrchExpGrp.Add(curBoolOp, newSrchExpGrp); parseExpression(filter, ref pos, newSrchExpGrp); state = ParseState.BoolOp; } else if (filter[pos] == '~') // eg. ~LastName { matchType = MatchTypeEnum.IgnoreCase; } else if (char.IsWhiteSpace(filter[pos]) || (!char.IsLetterOrDigit(filter[pos]) && filter[pos] != '_' && filter[pos] != '~')) // field names with spaces in them must be wrapped with brackets { fieldName = filter.Substring(startPos, pos - startPos).Trim(); } } if (fieldName != null) { if (fieldName[0] == '~') { fieldName = fieldName.Substring(1); matchType = MatchTypeEnum.IgnoreCase; } state = ParseState.CompareOp; } else { pos++; } } #endregion Left ////////////////////////////////////////////////////////// #region CompareOp else if (state == ParseState.CompareOp) { // skip whitespace while (pos < filter.Length && char.IsWhiteSpace(filter[pos])) { pos++; } if (char.IsLetter(filter[pos])) // REGEX { // should be CONTAINS, REGEX, IN or NOT //if( pos + 4 >= filter.Length ) // throwInvalidFilterConstruct( filter, pos ); try { // NOT if (char.ToUpper(filter[pos]) == 'N' && char.ToUpper(filter[pos + 1]) == 'O' && char.ToUpper(filter[pos + 2]) == 'T' && char.IsWhiteSpace(filter[pos + 3])) { pos += 3; isNot = true; continue; } // IN else if (char.ToUpper(filter[pos]) == 'I' && char.ToUpper(filter[pos + 1]) == 'N' && (char.IsWhiteSpace(filter[pos + 2]) || filter[pos + 2] == '(')) { pos += 2; if (char.IsWhiteSpace(filter[pos])) // skip whitespace { while (pos < filter.Length && char.IsWhiteSpace(filter[pos])) { pos++; } if (filter[pos] != '(') { throwInvalidFilterConstruct(filter, pos - 2); } } comparisonOp = ComparisonOperatorEnum.In; } // REGEX else if (char.ToUpper(filter[pos]) == 'R' && char.ToUpper(filter[pos + 1]) == 'E' && char.ToUpper(filter[pos + 2]) == 'G' && char.ToUpper(filter[pos + 3]) == 'E' && char.ToUpper(filter[pos + 4]) == 'X' && char.IsWhiteSpace(filter[pos + 5])) { pos += 5; comparisonOp = ComparisonOperatorEnum.Regex; } // CONTAINS else if (char.ToUpper(filter[pos]) == 'C' && char.ToUpper(filter[pos + 1]) == 'O' && char.ToUpper(filter[pos + 2]) == 'N' && char.ToUpper(filter[pos + 3]) == 'T' && char.ToUpper(filter[pos + 4]) == 'A' && char.ToUpper(filter[pos + 5]) == 'I' && char.ToUpper(filter[pos + 6]) == 'N' && char.ToUpper(filter[pos + 7]) == 'S' && char.IsWhiteSpace(filter[pos + 8])) { pos += 8; comparisonOp = ComparisonOperatorEnum.Contains; } else { throwInvalidFilterConstruct(filter, pos - 2); } } catch (Exception ex) { throwInvalidFilterConstruct(filter, pos - 2); } } // alternative way to specify ignore case search (other way is to prefix a fieldname with ~) else if (filter[pos] == '~') // ~= { matchType = MatchTypeEnum.IgnoreCase; if (++pos >= filter.Length) { throwInvalidFilterConstruct(filter, pos); } // next char must be = if (filter[pos] != '=') { throwInvalidFilterConstruct(filter, pos); } comparisonOp = ComparisonOperatorEnum.Equal; } else if (filter[pos] == '!') // != { if (++pos >= filter.Length) { throwInvalidFilterConstruct(filter, pos); } // next char must be = if (filter[pos] != '=') { throwInvalidFilterConstruct(filter, pos); } comparisonOp = ComparisonOperatorEnum.Equal; isNot = true; } else if (filter[pos] == '=') { comparisonOp = ComparisonOperatorEnum.Equal; } else if (filter[pos] == '<') // <, <= or <> { if (pos + 1 >= filter.Length) { throwInvalidFilterConstruct(filter, pos); } if (filter[pos + 1] == '>') { pos++; comparisonOp = ComparisonOperatorEnum.Equal; isNot = true; } else if (filter[pos + 1] == '=') { pos++; comparisonOp = ComparisonOperatorEnum.LessThanOrEqual; } else { comparisonOp = ComparisonOperatorEnum.LessThan; } } else if (filter[pos] == '>') // > or >= { if (pos + 1 >= filter.Length) { throwInvalidFilterConstruct(filter, pos); } if (filter[pos + 1] == '=') { pos++; comparisonOp = ComparisonOperatorEnum.GreaterThanOrEqual; } else { comparisonOp = ComparisonOperatorEnum.GreaterThan; } } else { throwInvalidFilterConstruct(filter, pos); } pos++; state = ParseState.Right; } #endregion CompareOp ////////////////////////////////////////////////////////// #region Right else if (state == ParseState.Right) { if (comparisonOp == ComparisonOperatorEnum.In) //|| comparisonOp == EqualityEnum.NotIn ) { // skip whitespace while (pos < filter.Length && char.IsWhiteSpace(filter[pos])) { pos++; } // filter[pos] should look like this now: (val1, val2, val3) // or like this: ('val1', 'val2', 'val3') if (filter[pos] == '(') { pos++; } // find the end int endPos = pos; while (endPos < filter.Length && filter[endPos] != ')') { endPos++; } if (endPos >= filter.Length) { throw new FileDbException(string.Format(FileDbException.InvalidFilterConstruct, filter.Substring(pos)), FileDbExceptionsEnum.InvalidFilterConstruct); } string inVals = filter.Substring(pos, endPos - pos); searchVal = parseInVals(inVals, matchType); pos = endPos; } else { if (!inString) { // skip whitespace only if we haven't found anything yet if (sbTemp.Length == 0) { while (pos < filter.Length && char.IsWhiteSpace(filter[pos])) { pos++; } } // look for end of ExpressionGroup if (sbTemp.Length > 0 && (filter[pos] == ')' || char.IsWhiteSpace(filter[pos]))) { // Expression completed searchVal = sbTemp.ToString(); sbTemp.Length = 0; var srchExp = new FilterExpression(fieldName, searchVal, comparisonOp, matchType, isNot); parentSrchExpGrp.Add(curBoolOp, srchExp); if (filter[pos] == ')') { return; } fieldName = null; state = ParseState.BoolOp; } else if (sbTemp.Length == 0 && filter[pos] == '\'') { // just starting to get the value inString = /*isString=*/ true; } else { sbTemp.Append(filter[pos]); } } else // inString == true { if (filter[pos] == '\'') { //Debug.Assert( sbTemp.Length > 0 ); -- it could be empty, eg. myfield = '' // if the next char is NOT another ' (escaped) then the string is completed if ((pos + 1 < filter.Length) && filter[pos + 1] == '\'') { pos++; } else { inString = false; searchVal = sbTemp.ToString(); sbTemp.Length = 0; var srchExp = new FilterExpression(fieldName, searchVal, comparisonOp, matchType, isNot); parentSrchExpGrp.Add(curBoolOp, srchExp); fieldName = null; state = ParseState.BoolOp; goto Advance; } } sbTemp.Append(filter[pos]); } } Advance: // advance pos++; } #endregion Right ////////////////////////////////////////////////////////// #region Next else // if( state == ParseState.BoolOp ) { Debug.Assert(state == ParseState.BoolOp); if (sbTemp.Length == 0) { // skip whitespace while (pos < filter.Length && char.IsWhiteSpace(filter[pos])) { pos++; } } if (filter[pos] == ')') { return; // we must be finished } if (char.IsWhiteSpace(filter[pos])) { // we must be finished if (sbTemp.Length == 0) { throw new FileDbException(string.Format(FileDbException.InvalidFilterConstruct, filter.Substring(pos)), FileDbExceptionsEnum.InvalidFilterConstruct); } string sOp = sbTemp.ToString(); sbTemp.Length = 0; if (string.Compare(sOp, "AND", StringComparison.OrdinalIgnoreCase) == 0) { curBoolOp = BoolOpEnum.And; } else if (string.Compare(sOp, "OR", StringComparison.OrdinalIgnoreCase) == 0) { curBoolOp = BoolOpEnum.Or; } else { throw new FileDbException(string.Format(FileDbException.InvalidFilterConstruct, filter.Substring(pos)), FileDbExceptionsEnum.InvalidFilterConstruct); } state = ParseState.Left; // start over on next expression // skip whitespace while (pos < filter.Length && char.IsWhiteSpace(filter[pos])) { pos++; } // reset vars startPos = pos; hasBracket = false; } else { sbTemp.Append(filter[pos]); pos++; } } #endregion Next } // for... // did we just complete an Expression? if (state == ParseState.Right) { if (comparisonOp != ComparisonOperatorEnum.In) //&& comparisonOp != EqualityEnum.NotIn ) { searchVal = sbTemp.ToString(); if (!inString && string.Compare((string)searchVal, "null", StringComparison.OrdinalIgnoreCase) == 0) { searchVal = null; } sbTemp.Length = 0; } var srchExp = new FilterExpression(fieldName, searchVal, comparisonOp, matchType, isNot); parentSrchExpGrp.Add(curBoolOp, srchExp); } }
/// <summary> /// Create a FilterExpression with the indicated values /// </summary> /// <param name="fieldName">The name of the Field to filter on</param> /// <param name="searchVal">The Field value to filter on</param> /// <param name="equality">The Equality operator to use in the value comparison</param> /// public FilterExpression(string fieldName, object searchVal, ComparisonOperatorEnum equality) : this(fieldName, searchVal, equality, MatchTypeEnum.UseCase, false) { }
/// <summary> /// 多表联合查询 /// </summary> /// <param name="mainType">关联主表</param> /// <param name="fromType">关联从表</param> /// <param name="mainField">主表关联字段</param> /// <param name="fromField">从表关联字段</param> /// <param name="joInType">连接类型</param> /// <param name="isMultiTable">是否返回多表对象</param> /// <param name="comparisonOperator">判断运算符</param> /// <param name="joinFields">多字段对应的集合</param> /// <returns></returns> public virtual Sql MultiTableQuery(Type mainType, Type fromType, string mainField, string fromField, JoinTypeEnum joInType = JoinTypeEnum.Join, bool isMultiTable = false, ComparisonOperatorEnum comparisonOperator = ComparisonOperatorEnum.Equal, List <JoinField> joinFields = null) { _multiTables.Add(new JoinTable { MainType = mainType, FromType = fromType, MainField = mainField, FromField = fromField, JoInType = joInType, ComparisonOperator = comparisonOperator, JoinFields = joinFields }); if (_multiTables.Count <= 0) { return(this); } Clear(); Select(); var startMainType = _multiTables[0].MainType; var startMainTableName = startMainType.Name; var startMainProperties = startMainType.GetProperties(); foreach (var p in startMainProperties) { var asName = p.Name; if (isMultiTable) { asName = $"{startMainTableName}_{p.Name}"; } AddTf(startMainTableName, p.Name).AddBs("AS").AddBs(asName).Comma(); } foreach (var multiTable in _multiTables) { var fromTableName = multiTable.FromType.Name; var fromProperties = multiTable.FromType.GetProperties(); foreach (var p in fromProperties) { var asName = p.Name; if (isMultiTable) { asName = $"{fromTableName}_{p.Name}"; } AddTf(fromTableName, p.Name).AddBs("AS").AddBs(asName).Comma(); } } SqlText.Remove(SqlText.Length - 1, 1); From().Add(startMainTableName); foreach (var multiTable in _multiTables) { switch (multiTable.JoInType) { case JoinTypeEnum.Join: Join(); break; case JoinTypeEnum.InnerJoin: InnerJoin(); break; case JoinTypeEnum.LeftJoin: LeftJoin(); break; case JoinTypeEnum.RightJoin: RightJoin(); break; default: Join(); break; } var fromTableName = multiTable.FromType.Name; Add(fromTableName).On(); if (multiTable.JoinFields == null || multiTable.JoinFields.Count == 0) { AddTf(multiTable.MainType.Name, multiTable.MainField); switch (multiTable.ComparisonOperator) { case ComparisonOperatorEnum.Equal: Equal(); break; case ComparisonOperatorEnum.EqualNot: EqualNot(); break; default: Equal(); break; } AddTf(fromTableName, multiTable.FromField); } else { foreach (var f in multiTable.JoinFields) { switch (f.LogicalOperator) { case LogicalOperatorEnum.And: And(); break; case LogicalOperatorEnum.Or: Or(); break; } } } } return(this); }