Wednesday, November 26, 2008

Tip! Yet another CAML query tool

CAML (Collaborative Application Markup Language) is an XML based markup language used with the family of Microsoft SharePoint technologies (Windows Sharepoint Services and Office SharePoint Server). Unlike plain XML, CAML contains specific groups of tags to both define and display (render) data. [wikipedia]

With other words CAML defines various aspects in SharePoint like: views, fields, site definitions, ...

With CAML you can also define queries, which is useful to extract some data from SharePoint lists. Now... writing a simple CAML query isn't that difficult. Given the following example:





It can be difficult if you have more conditional clauses:







Now I probably hear you say: "Sven, what are you talking about? I use the CAML query builder from U2U to construct my queries. That's pretty easy!". I know... that tool is really helpful but what if you have to construct your query dynamically? You can build a flexible query class to simplify the process of creating queries, but it's not necessary. Carlos Segura Sanz (MVP) did this already with his "Yet another CAML Query tool (ala SQL).

With that tool you can construct your query ala SQL. Later on, you can transform the SQL syntax to CAML syntax. Example:
using IdeSeg.SharePoint.Caml.ParseQuery;

string query = @" WHERE ContentType='sharepoint'
AND Firstname = 'Sven'
AND Lastname = 'Gillis'
GROUPBY Title DESC
ORDERBY _Author"
;
try
{
string camlQuery = CAMLParser.Query(query);
SPList list = null;
SPQuery query = SPQuery();
query.Query = camlQuery;
SPItemCollection col = list.getitems(query);
}
catch (ParserException ex) {}
Quite easy, isn't it?

Use "=null" as CAML IsNull operator and "<>null" as IsNotNull also you can use dates enclosed into sharp characters #01/01/1990# the sql Like operator is replaced by CAML Contains and the at "@" operator for the CAML BeginsWith. Also support parentheses for operator precedence.

Oh... btw if you download YACAMLQT you can also use a windows application to support your development. It looks something like this:


















I recently used it in a project and it works like a charm!

Resources:

No comments: