Create dynamic caml query in sharepoint

Description

This article will show you; how to create dynamic caml query in sharepoint.

Detail description:-

I have done lots of project in my career and most of the time we require to create dynamic caml query and hit sharepoint list to get the results.

For an example; you have custom search page where you have few filter criteria; may be in drop down. Every dropdown has approx 10 items. Now when you select “All” in each drop down that means we need to take every element of dropdown with or condition and create caml query.
Also put “And” condition between different dropdown values.
That means we have several filter with “Or” and “And” conditions.

To make it generic I have created 2 classes which help us to generate dynamic caml query.

Before we start developing code; we have to understand how CAML works in brief:-

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
If you have 1 query then it will be like this
<Where>
  <Eq>
    <FieldRef Name="Title" />
    <Value Type="Text">as</Value>
  </Eq>
</Where>
 
If you have 2 query then it will be like this
<Where>
  <Or>
    <Eq>
      <FieldRef Name="Title" />
      <Value Type="Text">as</Value>
    </Eq>
    <Eq>
      <FieldRef Name="ID" />
      <Value Type="Counter">w</Value>
    </Eq>
  </Or>
</Where>
 
And now for every new item (I mean more than 2 items) we will add 1 query and put join 
(<or>) in start (row no 27) and in end (row no 42). See below:-
<Where>
  <Or>
    <Or>
      <Eq>
        <FieldRef Name="Title" />
        <Value Type="Text">as</Value>
      </Eq>
      <Eq>
        <FieldRef Name="ID" />
        <Value Type="Counter">w</Value>
      </Eq>
    </Or>
    <Eq>
      <FieldRef Name="HR" />
      <Value Type="Text">w</Value>
    </Eq>
  </Or>
</Where>
Similarly we can create query for n number of items


The Code:
 

Below are 2 classes CamlQueryElements and CamlQuery.

CamlQueryElements: This class holds the property to build caml query.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
using System;
using System.Text;
 
namespace SharePoint.UI
{
    public class CamlQueryElements
    {
        public string LogicalJoin { get; set; } // like <Or>, <And>
        public string ComparisonOperators { get; set; } // like <Eq>, <Contains>
        public string FieldName { get; set; } // Like Title
        public string FieldType { get; set; } // Like Text
        public string FieldValue { get; set; } // some value
    }
}


CamlQuery
: This class has 2 methods.

1st method for adding elements and another for generating query.

Add element:- you can modify as you wish but at the end you have to
return IList<CamlQueryElements> object.

Generating query:- Here you need to pass IList<CamlQueryElements> object and then function will return required caml query.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
using System;
using System.Text;
using System.Collections.Generic;
 
namespace SharePoint.UI
{
    public class CamlQuery
    {
        public IList<CamlQueryElements> AddElement()
        {
            IList<CamlQueryElements> lstOfElement = new List<CamlQueryElements>();
 
            lstOfElement.Add(new CamlQueryElements
            {
                ComparisonOperators = "Eq",
                FieldName = "Title",
                FieldType = "Text",
                FieldValue = "SomeTitle",
                LogicalJoin = "Or"
            });
            lstOfElement.Add(new CamlQueryElements
            {
                ComparisonOperators = "Eq",
                FieldName = "Title",
                FieldType = "Text",
                FieldValue = "SomeTitle",
                LogicalJoin = "Or"
            });
            lstOfElement.Add(new CamlQueryElements
            {
                ComparisonOperators = "Contains",
                FieldName = "Title",
                FieldType = "Text",
                FieldValue = "SomeTitle",
                LogicalJoin = "And"
            });
            /*
              similarly we can add n number of elements
              You can change this code to fill your criteria as per your web app.
              But at the end you will return back "IList of CamlQueryElements" object.
	    */
 
            return lstOfElement;
        }
	public string GetDynamicQuery()
	{
		IList<CamlQueryElements> camlIlist = AddElement();
		string dyanmicCamlQuery = GenerateQuery(camlIlist);
		return dyanmicCamlQuery;
	}
 
	// This function loop List of camlqueryelments which has our filter criteria
	// Then generate query in required format.
	// At end it return string which holds caml query.
        public string GenerateQuery(IList<CamlQueryElements> lstOfElement)
        {
            StringBuilder queryJoin = new StringBuilder();
            string query = @"<{0}><FieldRef Name='{1}' /><Value {2} Type='{3}'>{4}</Value></{5}>";
            if (lstOfElement.Count > 0)
            {
                int itemCount = 0;
                foreach (CamlQueryElements element in lstOfElement)
                {
                    itemCount++;
                    string date = string.Empty;
                    // Display only Date
                    if (String.Compare(element.FieldType, "DateTime", true) == 0)
                        date = "IncludeTimeValue='false'";
                    queryJoin.AppendFormat
                   (string.Format(query, element.ComparisonOperators,element.FieldName, 
                       date, element.FieldType, element.FieldValue, element.ComparisonOperators));
 
                    if (itemCount >= 2)
                    {
                        queryJoin.Insert(0, string.Format("<{0}>", element.LogicalJoin));
                        queryJoin.Append(string.Format("</{0}>", element.LogicalJoin));
                    }
                }
                queryJoin.Insert(0, "<Where>");
                queryJoin.Append("</Where>");
            }
            return queryJoin.ToString();
        }
    }
}


Summary

The main point to learn here is: What’s the structure of caml query if it’s less than 3 items and if it’s more than 2 items?
Once we are clear with this; we can easily create methods to return caml query as we required.

Hope it helps.

Thanks!
Avinash

calendarMarch 8, 2012 · cardInfoyen · comments17 Comments
tagTags: , , , , , , , , ,  · Posted in: CAML, MOSS, SharePoint

17 Responses

  1. khilitchandra prajapati - March 8, 2012

    Avinash, really nice article. Keep it up :) Tc
    Khilit

  2. avinashdad - March 9, 2012

    thanks dude

  3. Neel - April 3, 2012

    Nice Article Avinash. Thanks for sharing.

  4. Sonja - June 2, 2012

    Hi Avinash,

    Thank you so much for this. It saved me a lot of trouble. Only thing I changed was in the GenerateQuery method to allow for the last “” in string query to be replaced as well.

    :)
    Sonja

  5. Sonja - June 2, 2012

    oops.. it cleaned up the closing Eq tag in my comment. I changed the closing Eq tag to be replaced as well by a comparison operator. Otherwise it could start off as Leq but end with Eq

  6. Infoyen - June 3, 2012

    Nice to see that it save your lots time. This is just an example to generate dynamic caml query. You can enhance easily as per your project requirement.
    Thanks!

  7. lex - July 24, 2012

    Thank you very much, Avinash. Alex

  8. Sukanta Saha - October 14, 2012

    Thank.Its realy a nice article.I just need this for my project.
    Thanks a lot.

  9. jc_dls1 - January 30, 2013

    great thanks

  10. anisia - April 3, 2013

    Great article I just have one question: Does it work for multiple and/or ? If I want to get this result how should I add the CAMLQueryElements?

  11. Infoyen - April 3, 2013

    Yes it works for multiple or/and.
    read my example and code carefully. I have explained all scenerio.

    In brief: if you want to add some condition with or/and then add entry in ” AddElement()” function. like
    lstOfElement.Add(new CamlQueryElements
    {
    ComparisonOperators = “Eq”,
    FieldName = “Title”,
    FieldType = “Text”,
    FieldValue = “anisia”,
    LogicalJoin = “Or”
    });

  12. Tib - July 9, 2013

    Cool !

    but be carreful, caml is case sensitive.
    In last field exemple, logical join is And not AND

  13. Infoyen - August 11, 2013

    Thank you for remark.

  14. Riyaz - January 25, 2014

    Very helpful article,

    What if I want to put LookupId=’TRUE’ ? I am using it to query the user field by ID. For ex., UserID”;

  15. Tom Atwell - April 11, 2014

    Is there a way to incorporate the value of an infopath 2010 field into a CAML Query of a SharePoint 2010 list?

    For example instead of

    Tom

    I would like:

    +The Value of the EmployeeName field in my infopath from

  16. Infoyen - June 18, 2014

    Hi Tom Atwell,

    I am late to reply. Let me know if you havnt got solution.
    In my view its should be possible by writing custom code behind infopath form.

    For example it can be like this
    XmlNamespaceManager nsm = this.NamespaceManager;
    XPathNavigator xnEmployeeName = e.SelectSingleNode(“@EmployeeName”, nsm);
    XPathNavigator xnID = e.SelectSingleNode(“@ID”, nsm);
    System.Xml.XmlElement elementUpdates = doc.CreateElement(“Batch”);
    elementUpdates.InnerXml = string.Format(@”” +
    ” + xnID.Value + “” +
    ” + xnEmployeeName .Value + “” + “
    “);

    //Update the list

    XPathNavigator xnListGUID = this.MainDataSource.CreateNavigator().SelectSingleNode(“/my:myFields/my:ListName”, nsm);
    XmlNode node = myList.UpdateListItems(xnListGUID.Value, elementUpdates);

    Hope it helps.
    Thanks!!

  17. Panoone - August 18, 2014

    Nice example,

    It would be great to query the filter fields for their type so that the query field value type can be adjusted accordingly.

Leave a Reply

Spam Protection: , required

myworldmaps infoyen