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.
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:-
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.
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.
return IList<CamlQueryElements> object.
Generating query:- Here you need to pass IList<CamlQueryElements> object and then function will return required caml query.
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.
Once we are clear with this; we can easily create methods to return caml query as we required.
Hope it helps.
Thanks!
Avinash
Avinash
