How to add new filters to CAML queries in SharePoint 2007
- by uruit
Normal
0
21
false
false
false
ES-UY
X-NONE
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
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);
}
Normal
0
21
false
false
false
ES-UY
X-NONE
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
Normal
0
21
false
false
false
ES-UY
X-NONE
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
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
Normal
0
21
false
false
false
ES-UY
X-NONE
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;
mso-fareast-language:EN-US;}
written by Sebastian Rodriguez - Portals and
Collaboration Solutions @ UruIT
