Write Your CAML Queries in C# using CAML.NET

I’m always on the lookout for ways to work smarter, not harder. When developing SharePoint solutions, that means finding ways to reduce the overall “surface area” of the platform so I don’t have to spend so much time on the little details. You know – the things that should have taken only a few minutes but end up taking several hours? And by the time you’re done, you’ve forgotten the original train of thought!

Writing CAML queries is one of those things I always feel I’m spending too much time on. I guess I’ve been burned enough times by making simple little mistakes in the CAML – typos, or misplaced parameters – that I tend to over-examine the CAML when something goes wrong. I keep thinking “wouldn’t it be great if we had a CAML compiler or some other tool that would help ensure that the CAML is correct every time?”. That way, we could just write the CAML, compile it and then use it just like any other component. One less moving part => reduced surface area => greater productivity => mission accomplished. Working smarter.

I know what you’re thinking. We already have some great CAML generators. What about the CAML Query Builder from U2U? What about the Stramit SharePoint 2007 CAML Viewer? With these tools, you just point them at your SharePoint site, enter a few parameters, and voila! – instant CAML that you can validate right there in the tool.

Well, those tools are great when you’re working against an existing site, and the query parameters are relatively static, but there are a lot of situations where you need something more flexible – and more strongly typed.

I have to confess I’ve never been a big fan of cutting and pasting large blocks of generated CAML code. I’d rather define the query programmatically and add it to my arsenal of tools that I can reuse over and over. But there are a few other reasons why I think it’s problematic as a general development practice.

• CAML is hard to read. When converted to a literal C# string, it’s even harder. Even simple parameter substitutions are prone to errors that can be costly to track down, and the person doing the cutting and pasting is often not the same person tasked with finding them.
• CAML is not strongly typed. It’s easy to supply the wrong attribute to a CAML element, thereby causing the query to fail. This problem is reduced when using a CAML generator, but creeps back in when the generated CAML is applied to a different site.
• CAML generators require that you first create a mock-up of the lists and site columns on which the query will be based. If you already have a good idea of what you want the query to do, creating a site and defining the lists and columns is distracting and just feels like a waste of time.

So what to do? After playing around with it for awhile, I’ve come up with a simple technique that addresses many of these problems at least for the C# developer. It also works for VB, but certain keywords (like “and” and “or”) are a problem. I call this approach CAML.NET.

[ Note: I was going to call it “Dromedary”, but I felt that was a bit much. A dromedary is a specially-bred Arabian racing camel – faster than a normal camel with one hump instead of two – get it? racing camel? never mind ]

The basic idea is to leverage the power and flexibility of the .NET Common Language Runtime (CLR) to build CAML queries dynamically in code while preserving the syntactic structure of the native CAML language.

For example, the following CAML query retrieves a list of items grouped by title, whose content type is “My Content Type” and whose description field is not empty. It also specifies that the result set should be ordered by the _Author, AuthoringDate and AssignedTo fields and that the AssignedTo field shall be in ascending order.

To use this in C#, you would have to convert it to a literal string, taking care to handle the embedded quotation marks, as in:

string simpleQuery = @"<Query><Where><Or><Eq><FieldRef Name=""ContentType"" /><Value Type=""Text"">My Content Type</Value></Eq><IsNotNull><FieldRef Name=""Description"" /></IsNotNull></Or></Where><GroupBy Collapse=""TRUE""><FieldRef Ascending=""FALSE"" Name=""Title"" /></GroupBy><OrderBy><FieldRef Name=""_Author"" /><FieldRef Name=""AuthoringDate"" /><FieldRef Ascending=""TRUE"" Name=""AssignedTo"" /></OrderBy></Query>";

To reuse this query for another content type, or to add more fields to the result set, or to change the ordering, etc., you would have to either generate a new query, or painstakingly decode the string by hand, possibly introducing typos or other errors along the way.

Here is the same query written in CAML.NET:

This is not only easier to read and modify, but it has a number of other advantages.

• Avoids hand-editing of literal XML strings in your code.
• Eliminates query failures caused by typos and improper casing of elements and attributes.
• Each query component is processed as a separate statement with strongly-typed parameters.
• Operator and method overloading greatly simplifies the raw CAML schema.
• Enables the use of variables instead of literal text to specify query components.
• Visual Studio intellisense support is available while writing queries.
• Simplifies the construction of reusable CAML component libraries.

Note the CAML.SortType enumeration which allows you explicitly specify the desired sorting for a given field. Other similar enhancements make it easy to construct your CAML queries. And on top of that, you get intellisense, with helpful comments that provide additional context while writing your queries.

Once you’ve assigned the string to a variable, you can use it in the same way you would use any other XML query string. For instance, the following code snippet applies the query to retrieve matching list items from a site collection:

SPSiteDataQuery q = new SPSiteDataQuery();
q.Query = simpleQuery;
q.ViewFields = CAML.FieldRef("Title") + CAML.FieldRef("ID");
q.RowLimit = 10;
DataTable t = SPContext.Current.Web.GetSiteData(q);

---

Another Example

Here is another example, taken from the ECM team blog. It lists all .bmp files in the site collection that have a height or width over 200 pixels, in descending order by file size.

Again, “stringizing” for C# you have:

string queryBitmapImages = @"<Query><Where><And><Eq><FieldRef Name=""DocIcon"" /><Value Type=""Computed"">bmp</Value></Eq><Or><Gt><FieldRef Name=""ImageWidth"" /><Value Type=""Integer"">200</Value></Gt><Gt><FieldRef Name=""ImageHeight"" /><Value Type=""Integer"">200</Value></Gt></Or></And></Where><OrderBy><FieldRef Ascending=""FALSE"" Name=""FileSizeDisplay"" /></OrderBy></Query>";

And in CAML.NET:

Note the use of the overloaded CAML.NET Value constructor, which simply takes an integer value as an argument. The generated CAML sets the “value” attribute automatically depending on the type of argument you pass in. There are lots of other places in the CAML schema where the .NET CLR can help to reduce the overall surface area and greatly improve developer productivity.

A beta version of the CAML.NET assembly is available now on CodePlex at http://www.codeplex.com/camldotnet. Please feel free to download it – give it a spin – and provide feedback either here or in the discussion forums. The CAML query schema is the first step. Planned future releases will include the other CAML schemas. My goal is simply to make it easier to write CAML code and thereby increase the productivity of SharePoint developers.

Stay tuned.

Technorati : .net, c#, caml, csharp, moss, sharepoint, wss
Del.icio.us : .net, c#, caml, csharp, moss, sharepoint, wss