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;
