I recently had a project where I needed to create a CAML query for a SharePoint website. The page was bringing back a list of items from a SharePoint List. The user then had the ability to choose to enter search terms and/or select items from dropdowns to whittle down the list of possible results returned.
If you’ve ever had the pleasure of trying to do this with CAML you probably already know, this is not an easy feat. You see, CAML query doesn’t let you interrogate a parameter being passed in to see if it’s null, and only use this in your Where clause when it contains a value.
So if I were querying a SQL table I could do something like this:
In the above code, the expression will return true if the parameter is either null, or the parameter is equal to the field value.
In CAML, you’re forced to create some crazy if…else if… else if… scenario to figure out all possible combinations of search parameters a user could choose so that you can create the proper query based only on those parameters chosen. In my project this turned out to be something like 12 different combinations of parameters. To me this was way over an acceptable amount of else if’s.
The following code is the way I came up with to deal with this problem.
The first thing we need to do is to set up and array to hold the parameter name and value for all search filters entered/selected by the user. I just used a simple array where the first item entered is the field name in the list, and the second item entered into the array is the value entered by the user.
Before we get to the next chunk of code, let’s look at the structure of the CAML queries Where clause so that we understand what we’re working towards.
As you can see in the code below, if we just have two parameters, we just need <And> tags surrounding the two expressions. Then every additional expression requires an <And> surrounding an <And> plus the additional expression.
Now, let’s check out the next block of code.
So based on the number of filters selected by the user we construct our query into a string. The first “if” will handle setting up the string if there is only 1 filter selected. The logic looks a little misleading because we’re looking for a length of 2 instead of 1, which you might have expected. However, that’s because we store the field name and the value for each filter in the same array. So in the case that there is only 1 filter selected we don’t need the <And> at all so we can simply set up the single expression.
The second part of the “if” handles the case where there are only 2 filters selected. Remember if there are two expressions they will simply be surrounded by the <And>’s.
Finally, the third part of the “if” statement handles if there are more than 2 filters. For this case, we set up the first two filters surrounded by the <And>’s, then for each additional filter we simply add one expression and surround the whole thing with a set of <And>’s.
There’s just one other piece to the puzzle left. That’s the function that returns the expression which is created with the passed in field name and value.
The function above is really pretty simple. You’ll notice that I have a Switch statement in the function. The only reason I have this, is because I have some lookups in my list. One of the lookups holds a single ID and the other holds multiple IDs.