Friday, June 25, 2010

Build dynamic CAML queries based on query string parameters

In this article I would like to show one of the practical usage of free and open source Camlex.NET library in real life scenarios of Sharepoint development. In various Sharepoint forums I often see questions where people ask how they achieve mentioned goal, i.e. how they can build dynamic CAML queries based on set of parameters specified e.g. in query strings. In most cases I answered by giving a link to Camlex.NET project on codeplex with some brief example. But with this I would like to have more descriptive blog post which will help people in their every-day work. This is one of the purpose of this post. Another purpose is to show how easy such tasks can be achieved with Camlex.NET – because this library was created exactly for simplifying dynamic CAML queries building (see also my previous articles which cover dynamic CAML queries: (this article for Camlex.NET 1.0. One the moment of writing this post version 2.0 is current and recommended release which solves mentioned problems more simpler) and

So lets start from the more or less common scenario: there is number of fields and we want to be able to retrieve items from Sharepoint list based on values specified in query string, e.g. if we will enter the following URL we want to see only those items which have Title field equal to “Meeting”. Assume that all query string parameters are optional – i.e. if some parameter is not specified in query string search engine should ignore it. Also if several query string parameters are specified we will treat them as single boolean expression joined using logical And (&&), i.e. means that we want to retrieve item which have Title=Meeting && Description=Sharepoint. This is quite common scenario where we applied useful pattern when dealing with web search: search is performed using HTTP GET request so users will be able to bookmark search result in their browsers (that’s how most popular search engines works also). Note that in real life you probably want to join conditions using logical Or (||) – it is not problem also.

At first download Camlex.NET assembly from, and install Camlex.NET.dll into GAC. Now you are ready to use Camlex.NET library in your project. For test purposes I created simple Sharepoint list “TestList” based on Custom list template and added one single line text field Description in addition to existing Title field. And then I added several test items in it. In order to test results I created new application _layouts page Search.aspx in 12/template/layouts/custom folder with included server code (application _layouts pages are good tool for testing because they are compiled on the fly and don’t require reinstall assemblies in GAC with app pool recycling). I will provide full code of Search.aspx page, but the real work is only 2 lines of code – the rest is just infrastructure stuff:

   1: <%@ Page Language="C#" %>
   2: <%@ Assembly Name="Camlex.NET, Version=, Culture=neutral,
   3: PublicKeyToken=831792d54d5285b7" %>
   4: <%@ Import Namespace="CamlexNET" %>
   5: <%@ Import Namespace="System.Linq" %>
   6: <%@ Import Namespace="System.Linq.Expressions" %>
   7: <%@ Import Namespace="Microsoft.SharePoint" %>
   8: <%@ Import Namespace="System.Text" %>
  10: <html xmlns="" >
  11: <head>
  12:     <title>Camlex.NET search example</title>
  13: </head>
  14: <body>
  15:     <form id="form1" runat="server">
  16:     <script runat="server">
   2:         protected override void OnLoad(EventArgs e)
   3:         {
   4:             var queryString = HttpContext.Current.Request.QueryString;
   6:             if (queryString.Count == 0)
   7:                 return;
   9:             var conditions =
  10:                 queryString.AllKeys.Select<string, Expression<Func<SPListItem, bool>>>(
  11:                     field => x => (string) x[field] == queryString[field]);
  12:             string queryText = Camlex.Query().WhereAll(conditions).ToString(false);
  15:             using (var site = new SPSite(""))
  16:             {
  17:                 using (var web = site.OpenWeb())
  18:                 {
  19:                     var list = web.Lists["TestList"];
  21:                     var query = new SPQuery { Query = queryText };
  22:                     var items = list.GetItems(query);
  24:                     showListItems(items);
  25:                 }
  26:             }
  27:         }
  29:         private void showListItems(SPListItemCollection items)
  30:         {
  31:             var sb = new StringBuilder();
  32:             foreach (SPListItem item in items)
  33:             {
  34:                 sb.AppendFormat("{0}<br/>", item.Title);
  35:             }
  36:             this.lit.Text = sb.ToString();
  37:         }
  18:       <asp:Literal ID="lit" runat="server" />
  20:     </div>
  21:   </form>
  22: </body>
  23: </html>

Note the following lines of code:

   1: var conditions =
   2:     queryString.AllKeys.Select<string, Expression<Func<SPListItem, bool>>>(
   3:         field => x => (string) x[field] == queryString[field]);
   4: string queryText = Camlex.Query().WhereAll(conditions).ToString(false);

All work is done in these lines: at first we construct list of expressions based on query string parameters (i.e. we build expression tree representation for each key value pair in query string) and then we call WhereAll() method of Camlex.NET library which joins expressions using logical And (&&). So when we specify URL Camlex.NET will build the following CAML query:

   1: <Where>
   2:   <And>
   3:     <Eq>
   4:       <FieldRef Name="Title" />
   5:       <Value Type="Text">Meeting</Value>
   6:     </Eq>
   7:     <Eq>
   8:       <FieldRef Name="Description" />
   9:       <Value Type="Text">Sharepoint</Value>
  10:     </Eq>
  11:   </And>
  12: </Where>

It is scallable code, i.e. if we will add new field (Foo) and append it to URL: then Camlex.NET will automatically build valid CAML query with this new condition:

   1: <Where>
   2:   <And>
   3:     <And>
   4:       <Eq>
   5:         <FieldRef Name="Title" />
   6:         <Value Type="Text">Meeting</Value>
   7:       </Eq>
   8:       <Eq>
   9:         <FieldRef Name="Description" />
  10:         <Value Type="Text">Sharepoint</Value>
  11:       </Eq>
  12:     </And>
  13:     <Eq>
  14:       <FieldRef Name="Foo" />
  15:       <Value Type="Text">bar</Value>
  16:     </Eq>
  17:   </And>
  18: </Where>

That’s how building of dynamic CAML queries can be achieved with Camlex.NET library. In the future posts I will show how to use another data types (i.e. not just strings) and various operations (like >, <, Contains and other).

1 comment: