How to add new filters to CAML queries in SharePoint 2007
One
flexibility SharePoint has is CAML (Collaborative Application Markup Language).
CAML it's a
markup language like html that allows developers to do queries against SharePoint
lists, it's syntax is very easy to understand and it allows to add logical
conditions like Where, Contains, And, Or, etc, just like a SQL Query.
For one of
our projects we have the need to do a filter on SharePoint views, the problem
here is that the view it's a list containing a CAML Query with the filters the
view may have, so in order to filter the view that's already been filtered
before, we need to append our filters to the existing CAML Query.
That's not a trivial task because the where statement in a CAML Query it's like this:
<Where>
<And>
<Filter1 />
<Filter2 />
</And>
</Where>
If we want to add a new logical operator, like an OR it's not just as
simple as to append the OR
expression like the following example:
<Where>
<And>
<Filter1 />
<Filter2 />
</And>
<Or>
<Filter3 />
</Or>
</Where>
But instead the correct query
would be:
<Where>
<Or>
<And>
<Filter1 />
<Filter2 />
</And>
<Filter3 />
</Or>
</Where>
Notice that the
<Filter# /> tags are for explanation purpose only.
In order to solve this problem we created a simple component, it has a
method that receives the current query (could be an empty query also) and
appends the expression you want to that query.
Example:
string
currentQuery = @"
<Where>
<And>
<Contains><FieldRef
Name='Title' /><Value Type='Text'>A</Value></Contains>
<Contains><FieldRef Name='Title' /><Value
Type='Text'>B</Value></Contains>
</And>
</Where>";
currentQuery
= CAMLQueryBuilder.AppendQuery(
currentQuery,
"<Contains><FieldRef
Name='Title' /><Value Type='Text'>C</Value></Contains>",
CAMLQueryBuilder.Operators.Or);
The fist parameter this function receives it's the actual query, the second
it's the filter you want to add, and the third it's the logical operator, so
basically in this query we want all the items that the title contains: the
character A and B or the ones that contains the character C.
The result
query is:
<Where>
<Or>
<And>
<Contains><FieldRef
Name='Title' /><Value Type='Text'>A</Value></Contains>
<Contains><FieldRef Name='Title' /><Value
Type='Text'>B</Value></Contains>
</And>
<Contains><FieldRef
Name='Title' /><Value Type='Text'>C</Value></Contains>
</Or>
</Where>
The code:
First of
all we have an enumerator inside the CAMLQueryBuilder class that has the two
possible
Options
And, Or.
public
enum Operators
{ And, Or }
Then we
have the main method that's the one that performs the append of the filters.
public
static string
AppendQuery(string containerQuery, string logicalExpression, Operators
logicalOperator){
In this method the first we do is create a new
XmlDocument and wrap the current query (that may be empty) with a
"<Query></Query>" tag, because the query that comes with the view
doesn't have a root element and the XmlDocument must be a well formatted xml.
XmlDocument queryDoc = new
XmlDocument();
queryDoc.LoadXml("<Query>" + containerQuery + "</Query>");
The next step is to create a new XmlDocument
containing the logical expression that has the filter needed.
XmlDocument logicalExpressionDoc = new XmlDocument();
logicalExpressionDoc.LoadXml("<root>" + logicalExpression + "</root>");
In these next four
lines we extract the expression from the recently created XmlDocument and
create an XmlElement.
XmlElement expressionElTemp = (XmlElement)logicalExpressionDoc.SelectSingleNode("/root/*");
XmlElement expressionEl =
queryDoc.CreateElement(expressionElTemp.Name);
expressionEl.InnerXml
= expressionElTemp.InnerXml;
Below are the main steps in the component
logic. The first "if" checks if the actual query doesn't contains a "Where"
clause. In case there's no "Where" we add it and append the expression.
In case that there's already a "Where" clause,
we get the entire statement that's inside the "Where" and reorder the query
removing and appending elements to form the correct query, that will finally
filter the list.
XmlElement whereEl;
if
(!containerQuery.Contains("Where"))
{
queryDoc.FirstChild.AppendChild(queryDoc.CreateElement("Where"));
queryDoc.SelectSingleNode("/Query/Where").AppendChild(expressionEl);
}
else
{
whereEl
= (XmlElement)queryDoc.SelectSingleNode("/Query/Where");
if
(!containerQuery.Contains("<And>")
&&
!containerQuery.Contains("<Or>"))
{
XmlElement
operatorEl = queryDoc.CreateElement(GetName(logicalOperator));
XmlElement existingExpression = (XmlElement)whereEl.SelectSingleNode("/Query/Where/*");
whereEl.RemoveChild(existingExpression);
operatorEl.AppendChild(existingExpression);
operatorEl.AppendChild(expressionEl);
whereEl.AppendChild(operatorEl);
}
else
{
XmlElement
operatorEl = queryDoc.CreateElement(GetName(logicalOperator));
XmlElement existingOperator = (XmlElement)whereEl.SelectSingleNode("/Query/Where/*");
whereEl.RemoveChild(existingOperator);
operatorEl.AppendChild(existingOperator);
operatorEl.AppendChild(expressionEl);
whereEl.AppendChild(operatorEl);
}
}
return
queryDoc.FirstChild.InnerXml
}
Finally the GetName method converts the Enum
option to his string equivalent.
private
static string GetName(Operators logicalOperator)
{
return
Enum.GetName(typeof(Operators), logicalOperator);
}
This component helped our team a lot using SharePoint 2007 and modifying
the queries, but now in SharePoint 2010; that wouldn't be needed because of the
incorporation of LINQ to SharePoint. This new feature enables the developers to
do typed queries against SharePoint lists without the need of writing any CAML
code. But there is still much development to
the 2007 version so I hope this information is useful for other members.
Post written by Sebastian Rodriguez - Portals and
Collaboration Solutions @ UruIT