Thursday, March 27, 2014

Taming CAML



As a SharePoint developer, CAML is a fact of life. It can be a pain to deal with, but there are ways to make your life easier. I'm going to discuss a few ways of constructing CAML queries. Some are better than others.


Producing a String of CAML

There are many ways to produce a string of CAML that you can include in your codebase. You can write it by hand or use a tool to spit out a string of CAML that you can copy/paste.


Writing By Hand

I recommend against writing CAML by hand. As it's based on XML, which is not exactly the basis for a succinct query language, strings of CAML get long and unwieldy. I mean, this is gross, right? Who wants to write this by hand?

activeProductsByCategory.Query = "<GroupBy Collapse='FALSE' GroupLimit='30'><FieldRef Name='Category' /></GroupBy><OrderBy><FieldRef Name='Title' /></OrderBy><Where><Eq><FieldRef Name='Discontinued' /><Value Type='Boolean'>0</Value></Eq></Where>";

We can do better.


U2U CAML Query Builder

There are several free third-party CAML builder tools that allow you to interactively construct queries via a GUI designer. U2U's tool is probably the most well known as it's been around the longest.




Tools like these are certainly much better than hand-rolling CAML queries. I'll break out U2U CAML Query Builder once in a while for quick, simple scenarios, but there's a much more comprehensive interactive CAML builder that I turn to time and time again. And this one's made by Microsoft themselves.


SharePoint's Web Form

It's easy to forget that the vast majority of the things we do as SharePoint developers are also possible by end users via SharePoint's built-in web forms. There's no shame in prototyping your ideas via SharePoint's own screens, and then reproducing the prototype in code. I do this often.

Let's say we need a new view to be added to a list. As a developer, for repeatability's sake, we want to create this view via code in a feature. But we can lean on SharePoint's out-of-the-box view creation screen to write the CAML query for us.




Once we've designed the view, we can fire up SharePoint Manager (or the SharePoint Management Shell) and copy the CAML that resulted.




I think this is the best way to produce a string of CAML that you can paste into your code, as you have the full power of Microsoft's own view design interface.


No CAML


What if you tired of having unsightly blobs of CAML littered throughout your codebase? Outside of the SharePoint world, CAML is replaced by SQL. And for years people have avoided having blobs of SQL in their codebases by using tools that generate SQL dynamically. Why not use a tool that generates your CAML dynamically?


LINQ to SharePoint

I touched on LINQ to SharePoint in a previous post. Just as LINQ to SQL takes a "snapshot" of your database schema and generates .NET classes that map to it, LINQ to SharePoint takes a snapshot of the lists in a site and generates .NET classes against it.

LINQ to SharePoint was very exciting to me when I first heard about it, as I was familiar with tools like LINQ to SQL and how convenient they made querying for data from code. Unfortunately, that excitement faded as I tried to press LINQ to SharePoint into action in a real-world application.

I'll invite you to read Bjørn Furuknap's post (especially "The Ugly" section), as he does a pretty good job of running down the practical concerns of using LINQ to SharePoint. For projects I've worked on, the biggest problem is that LINQ to SharePoint is just too static for a dynamic environment like SharePoint. It's too easy for the generated code to go out of sync with the reality of a particular site. I've always worked on SharePoint projects in teams of multiple developers, and when we've tried to use LINQ to SharePoint, we ran into constant problems with the sites in our local development machines going out of sync with the LINQ to SharePoint model and causing blow-ups at runtime. This situation is compounded when you have different developers working on different SharePoint features that make list changes and that may or may not be active on any given site.

LINQ to SharePoint may be great for some scenarios, but from my own personal experience, it's never been a practical choice.


Camlex.NET

My current favorite is a fantastic open source project called Camlex.NET. This project hits a sweet spot for me in that it alleviates the need to have large CAML strings in my codebase, while still being dynamic.

Here I'm setting up the query for a view on the Products list from SPMeta-Northwind called "Active Products By Category":

activeProductsByCategory.Query = "<GroupBy Collapse='FALSE' GroupLimit='30'><FieldRef Name='Category' /></GroupBy><OrderBy><FieldRef Name='Title' /></OrderBy><Where><Eq><FieldRef Name='Discontinued' /><Value Type='Boolean'>0</Value></Eq></Where>";

And here I'm setting up the same query with Camlex.NET:

activeProductsByCategory.Query = Camlex.Query().Where(x => (bool)x["Discontinued"] == false).OrderBy(x => x["Title"]).GroupBy(x => x["Category"], false, 30).ToString();

Although, to my eye, the Camlex.NET version here is much nicer, this is a pretty basic example of its power. Imagine situations where you're dynamically building up a WHERE clause containing an arbitrary number of ANDs/ORs based on variable conditions. It's times like that when you're concatenating large strings of CAML and playing String.Format() bingo that you really see the power of a tool like Camlex.NET.

As a bonus, the authors of Camlex.NET have a website where you can paste in CAML strings and see how you'd build their equivalents with Camlex.NET. It's pretty handy when you're first learning the tool.




Closing

As a SharePoint developer, you're going to need CAML queries. You can either go the path of having strings of CAML in your codebase, or having a tool dynamically generate CAML.

If you don't mind having blobs of CAML intermingled with your C#/VB code, then I recommend using Microsoft's own add/edit view form to build your CAML.

If you're like me, and you prefer to have your CAML built dynamically, then I recommend the amazing Camlex.NET project, which I believe is a more practical choice over LINQ to SharePoint.

1 comment:

  1. Hi there,
    I am knew to SharePoint and am trying to build a form that users can use to query through lists that I have set up (similar to an access database form that you would use to query tables). Is this possible with Camel, or are these just used to perform one static query?

    ReplyDelete