Dynamically create a CAML request

Question

I have a serach page that previously worked on the basis of SQL DB, but now we have moved the back-en to SharePoint, I'm trying to create a query based on the selection of the user from the drop-down list.

Existing SQL query:

string SQLquery "Select companyname,phone,email from Tab where Approved = 1" If (country.selectedindex != "") { SQLquery += "AND (country LIKE '%" + country.SelectedValue + "%')" } If (functional.selectedindex != "") { SQL += "AND (country LIKE '%" + country.SelectedValue + "%')" } If (state.selectedindex != "") { SQL += "AND (state LIKE '%" + state.SelectedValue + "%') OR ( businessareaState like '%" + state.SelectedValue + "%'))" } 

It was easy, but I have to create the same request in CAML based on the selection of the user from the drop-down lists. Somehow I cannot give it a dynamic form, since the structure completely changes in CAML as soon as you add, and the criteria in it.

Help would be greatly appreciated.

Answer 1

You can use this free tool to help create CAML queries.

http://www.u2u.be/res/tools/camlquerybuilder.aspx

Make a few variations of your query and you will see how the structure changes. Basically, you will have to create an XML document, rather than using string concatenation (although this may also work, it will probably be easier to assemble it in an XML parser)

Answer 2

I developed C # code to create a dynamic query. It looks like

  public string GenerateQuery(IList<CamlQueryElements> lstOfElement) { StringBuilder queryJoin = new StringBuilder(); string query = @"<{0}><FieldRef Name='{1}' /><Value {2} Type='{3}'>{4}</Value></Eq>"; 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)); 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(); } 

IList lstOfElement is a custom object that contains filter elements. You can create your own object and move on to this method.