Friday, May 18, 2012

How to create a dynamic caml query on runtime with multiple Or or And.

Suppose you want to get all the items from a Sharepoint list where one column which is named as city is equal  to 'X', 'Y' or 'Z', you can get it by adding OR again and again to query.  But suppose you don't know that through which values you are going to compare it or going to get it on run time then you need to create your query at run time. So here is the way to create a CAML query in this scenario. Here I am going to generate it for so many OR and with one AND. Here you can get query for all the items from a list where Country is India and City can be any as from a List<string> which you can populate based on your conditions or requirements.

public StringBuilder GetQueryForAllDaysTCodes(List<string> Cities)
        {
string startingQuery = "<Where><And><Eq><FieldRef Name='Countries' /><Value Type='Text'>India</Value></Eq>";
            StringBuilder stbQuery = new StringBuilder();
            stbQuery.Append(startingQuery);
            bool first = true;
            foreach (string City in Cities)
            {
                if (Cities.Length > 0)
                {
                    if (first)
                    {
                        stbQuery.Insert(startingQuery.Length, "<Eq><FieldRef Name='Cities' /><Value Type='Text'>").Append(City.Trim()).Append("</Value></Eq>");
                        first = false;
                    }
                    else
                    {
                        stbQuery.Insert(startingQuery.Length, "<Or>");
                        stbQuery.Append("<Eq><FieldRef Name='Cities' /><Value Type='Text'>").Append(City.Trim()).Append("</Value></Eq>");
                        stbQuery.Append("</Or>");
                    }
                }
            }
            stbQuery.Append("</And></Where>");
            return stbQuery;
        }

Note: Remove highlighted text to get only an OR condition query. 

No comments:

Post a Comment