Build Dynamically CAML Query in SharePoint 2013 Client Object Model :-
public class CamlQueryElements
{
public string LogicalJoin { get; set; }
public string ComparisonOperators { get; set; }
public string FieldName { get; set; }
public string FieldType { get; set; }
public string FieldValue { get; set; }
}
internal DataTable GetReturnTable(ClientContext clientContext, string strKeyWordSearch)
{
bool isExist = false;
string status = string.Empty;
string strWhere = string.Empty;
DataTable dtReturn = new DataTable();
DataRow dr;
Microsoft.SharePoint.Client.List returnList = clientContext.Web.Lists.GetByTitle("LIST_NAME");
clientContext.Load(returnList);
clientContext.ExecuteQuery();
IList<CamlQueryElements> lstOfElement = new List<CamlQueryElements>();
lstOfElement.Add(new CamlQueryElements {
ComparisonOperators = "Contains", FieldName = "Column1", FieldType = "Text", FieldValue = strKeyWordSearch, LogicalJoin = "Or" });
lstOfElement.Add(new CamlQueryElements {
ComparisonOperators = "Eq", FieldName = "Column2", FieldType = "DateTime", FieldValue = strKeyWordSearch, LogicalJoin = "Or" });
lstOfElement.Add(new CamlQueryElements {
ComparisonOperators = "Contains", FieldName = "Column3", FieldType = "Text", FieldValue = strKeyWordSearch, LogicalJoin = "Or" });
lstOfElement.Add(new CamlQueryElements {
ComparisonOperators = "Contains", FieldName = "Column4", FieldType = "Text", FieldValue = strKeyWordSearch, LogicalJoin = "Or"
});
lstOfElement.Add(new CamlQueryElements {
ComparisonOperators = "IsNotNull", FieldName = "ID", FieldType = "", FieldValue = "", LogicalJoin = "And" });
lstOfElement.Add(new CamlQueryElements {
ComparisonOperators = "Eq", FieldName = "Column5", FieldType = "Text", FieldValue = "Open", LogicalJoin = "And" });
strWhere =
GenerateQuery(lstOfElement);
if (returnList != null && returnList.ItemCount > 0 &&
strWhere != null && strWhere.ToString() != "")
{
Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml =
@"<View>" +
"<Query>" +
strWhere +
"<OrderBy><FieldRef Name='ID'
Ascending='FALSE' /></OrderBy>" +
"</Query>" +
"<ViewFields>" +
"<FieldRef Name='ID' />" +
"<FieldRef Name='ReqeuestID' />" +
"</ViewFields>" +
"<RowLimit>30</RowLimit>" +
"</View>";
SPClient.ListItemCollection
returnListItems = returnList.GetItems(camlQuery);
clientContext.Load(returnListItems);
clientContext.ExecuteQuery();
if (returnListItems != null && returnListItems.Count > 0)
{
foreach (var field in
returnListItems[0].FieldValues.Keys)
{
dtReturn.Columns.Add(field);
}
isExist =
IsFieldExists(dtReturn, "Days");
if (!isExist)
{
dtReturn.Columns.Add("Days");
}
foreach (var item in returnListItems)
{
dr = dtReturn.NewRow();
foreach (var obj in item.FieldValues)
{
if (obj.Key == "ID")
{
}
if (obj.Value != null)
{
string type =
obj.Value.GetType().FullName;
if (type == "Microsoft.SharePoint.Client.FieldLookupValue")
{
dr[obj.Key]
= ((FieldLookupValue)obj.Value).LookupValue;
}
else if (type == "Microsoft.SharePoint.Client.FieldUserValue")
{
dr[obj.Key]
= ((FieldUserValue)obj.Value).LookupValue;
}
else if (type == "Microsoft.SharePoint.Client.FieldUserValue[]")
{
FieldUserValue[]
multValue = (FieldUserValue[])obj.Value;
foreach (FieldUserValue
fieldUserValue in multValue)
{
dr[obj.Key] += (fieldUserValue).LookupValue + "<br>";
}
}
else if (type == "System.DateTime")
{
if (obj.Value.ToString().Length
> 0)
{
var date =
obj.Value.ToString().Split(' ');
if (date[0].Length > 0)
{
if (obj.Key == "ReturnDate")
{
dr[obj.Key] = date[0];
DateTime
dtReturnDate = DateTime.Parse(date[0]);
DateTime TodayDate = Common.getTodayDateTime();
TimeSpan
ts = TodayDate - dtReturnDate;
dr["Days"]
= ts.Days.ToString();
}
else
{
dr[obj.Key] = date[0];
}
}
}
}
else
{
dr[obj.Key]
= obj.Value;
}
}
else
{
dr[obj.Key] = null;
}
}
dtReturn.Rows.Add(dr);
}
}
}
return dtReturn;
}
internal static string GenerateQuery(IList<CamlQueryElements> camlIlist)
{
StringBuilder queryJoin = new StringBuilder();
string query;
if (camlIlist.Count > 0)
{
int itemCount = 0;
foreach (CamlQueryElements element in camlIlist)
{
if
(element.ComparisonOperators.ToString() != "IsNotNull")
{
query = @"<{0}><FieldRef Name='{1}' /><Value
{2} Type='{3}'>{4}</Value></{5}>";
}
else
{
query = @"<{0}><FieldRef Name='{1}'
/></{5}>";
}
itemCount++;
string date = string.Empty;
if (String.Compare(element.FieldType,
"DateTime", true) == 0)
{
date = "IncludeTimeValue='false'";
DateTime dt = new DateTime();
if (element.FieldValue.IndexOf("/") != -1)
{
DateTime.TryParse(element.FieldValue,
out dt);
element.FieldValue
= dt.ToString("yyyy-MM-dd");
}
}
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 Convert.ToString(queryJoin);
}
internal static bool IsFieldExists(DataTable dt, string FieldName)
{
bool IsFieldExists = false;
for (int I = 0; I < dt.Columns.Count; I++)
{
if (dt.Columns[I].ColumnName.ToUpper() ==
FieldName.ToUpper())
{
IsFieldExists = true;
}
}
return IsFieldExists;
}
