private string BuildSqlFilterString() { if (this._camlXmlQuery == null || this._camlXmlQuery.Query == null) { return(string.Empty); } if (this._camlXmlQuery.Query.Where == null) { return(string.Empty); } ILogicalOperators operatorOne = this._camlXmlQuery.Query.Where.LogicalOperatorOne; return(GetSqlString(operatorOne)); }
public void SetUp() { operators = new LogicalOperators(stackManipulation); }
private static string GetSqlString(ILogicalOperators operatorOne) { const string tableNameFormat = Constants.SqlScriptTablePrefix + ".[{0}]"; string sqlstring = ""; string valueFormat; switch (operatorOne.OperatorName) { case OperatorType.And: CamlXmlAnd camlXmlAnd = operatorOne as CamlXmlAnd; if (camlXmlAnd == null) { break; } string sqlStringXmlAndOperatorOne = GetSqlString(camlXmlAnd.LogicalOperatorOne); string sqlStringXmlAndOperatorTwo = GetSqlString(camlXmlAnd.LogicalOperatorTwo); sqlstring += string.Format("({0} AND {1})", sqlStringXmlAndOperatorOne, sqlStringXmlAndOperatorTwo); break; case OperatorType.Or: CamlXmlOr camlXmlOr = operatorOne as CamlXmlOr; if (camlXmlOr == null) { break; } string sqlStringXmlOrOperatorOne = GetSqlString(camlXmlOr.LogicalOperatorOne); string sqlStringXmlOrOperatorTwo = GetSqlString(camlXmlOr.LogicalOperatorTwo); sqlstring += string.Format("({0} OR {1})", sqlStringXmlOrOperatorOne, sqlStringXmlOrOperatorTwo); break; case OperatorType.Membership: // CamlXmlMembership camlXmlMembership = operatorOne as CamlXmlMembership; throw new NotImplementedException(); case OperatorType.DateRangesOverlap: // CamlXmlDateRangesOverlap camlXmlDateRangesOverlap = operatorOne as CamlXmlDateRangesOverlap; throw new NotImplementedException(); // IFieldRefValueBase START case OperatorType.BeginsWith: IFieldRefValueBase camlXmlBeginsWith = operatorOne as IFieldRefValueBase; if (camlXmlBeginsWith == null) { break; } valueFormat = GetSqlOperationFormatByFieldType(camlXmlBeginsWith.Value.Type); sqlstring += string.Format(tableNameFormat + camlXmlBeginsWith.SqlOperator + valueFormat, camlXmlBeginsWith.FieldRef.Name, camlXmlBeginsWith.Value.Value + "%"); break; case OperatorType.Contains: case OperatorType.Includes: IFieldRefValueBase camlXmlContainsIncludes = operatorOne as IFieldRefValueBase; if (camlXmlContainsIncludes == null) { break; } valueFormat = GetSqlOperationFormatByFieldType(camlXmlContainsIncludes.Value.Type); sqlstring += string.Format(tableNameFormat + camlXmlContainsIncludes.SqlOperator + valueFormat, camlXmlContainsIncludes.FieldRef.Name, "%" + camlXmlContainsIncludes.Value.Value + "%"); break; case OperatorType.Eq: case OperatorType.Geq: case OperatorType.Gt: case OperatorType.IsNotNull: case OperatorType.IsNull: case OperatorType.Leq: case OperatorType.Lt: case OperatorType.Neq: IFieldRefValueBase camlXmlFieldRefValueBase = operatorOne as IFieldRefValueBase; if (camlXmlFieldRefValueBase == null) { break; } valueFormat = GetSqlOperationFormatByFieldType(camlXmlFieldRefValueBase.Value.Type); string value = camlXmlFieldRefValueBase.Value.Value == null && camlXmlFieldRefValueBase.Value.UserID != null ? SPUserTemplate : camlXmlFieldRefValueBase.Value.Value; sqlstring += string.Format(tableNameFormat + camlXmlFieldRefValueBase.SqlOperator + valueFormat, camlXmlFieldRefValueBase.FieldRef.Name, value); break; // IFieldRefValueBase END case OperatorType.In: CamlXmlIn camlXmlIn = operatorOne as CamlXmlIn; if (camlXmlIn == null) { break; } string inSqlString = string.Empty; foreach (CamlXmlValue camlXmlValue in camlXmlIn.Values.Value) { if (!string.IsNullOrWhiteSpace(inSqlString)) { inSqlString += " OR "; } inSqlString += string.Format("([{0}] LIKE '%{1}%')", camlXmlIn.FieldRef.Name, camlXmlValue.Value); } sqlstring += inSqlString; break; case OperatorType.NotIncludes: CamlXmlNotIncludes camlXmlNotIncludes = operatorOne as CamlXmlNotIncludes; if (camlXmlNotIncludes == null) { break; } sqlstring += string.Format("NOT ([{0}] LIKE '%{1}%')", camlXmlNotIncludes.FieldRef.Name, camlXmlNotIncludes.Value); break; } return(sqlstring); }
/// <summary> /// Add Not Equal Operator to query /// </summary> /// <param name="logicalOperators">Logical Operators instance</param> /// <param name="value">Value</param> /// <returns></returns> public static IComparisonOperators NotEqualTo(this ILogicalOperators logicalOperators, string value) => logicalOperators.To <ComparisonOperators>().NotEqualTo(value);
/// <summary> /// Add Less Than Or Equal Operator to query /// </summary> /// <param name="logicalOperators">Logical Operators instance</param> /// <param name="value">Value</param> /// <returns></returns> public static IComparisonOperators LessThanOrEqualTo(this ILogicalOperators logicalOperators, int value) => logicalOperators.To <ComparisonOperators>().LessThanOrEqualTo(value);
/// <summary> /// Add Greater Than Operator to query /// </summary> /// <param name="logicalOperators">Logical Operators instance</param> /// <param name="value">Value</param> /// <returns></returns> public static IComparisonOperators GreaterThan(this ILogicalOperators logicalOperators, int value) => logicalOperators.To <ComparisonOperators>().GreaterThan(value);
/// <summary> /// Add Equal Operator to query /// </summary> /// <param name="logicalOperators">Logical Operators instance</param> /// <param name="value">Value</param> /// <returns></returns> public static IComparisonOperators EqualTo(this ILogicalOperators logicalOperators, string value) => logicalOperators is IComparisonOperators command?command.EqualTo(value) : null;