Feeds:
Posts
Comments

Posts Tagged ‘DYNAMIC CAML’


Recently, I had to hand-code a complex dynamic CAML (Collaborative Application Markup Language) query. U2U CAML Builder has always come to my rescue in building queries. I never had to understand the pattern behind the CAML query. But this time around, U2U CAML Builder misguided me in a sense. Let see how.

The document library had couple of columns, one of which was a DateTime Column (MeetingDate). The requirement was to get list items from the document library which are in a specific year(s).

 

 Now, What if I want to get the items in the year 2001, 2008 and 2010 only? We don’t have IN() operator in CAML as in SQL. So the question is how do we retrieve specific records based on the year.

As we don’t have the IN() operator, I have used the below logic.

<And>
  <Geq>
     <FieldRef Name=’MeetingDate’ />
     <Value IncludeTimeValue=’FALSE’ Type=’DateTime’>2010-01-01T20:17:06Z</Value>
  </Geq>
  <Leq>
     <FieldRef Name=’MeetingDate’ />
     <Value IncludeTimeValue=’FALSE’ Type=’DateTime’>2010-12-31T20:17:06Z</Value>
  </Leq>
</And>

The logic is Meeting Date >= 01/01/2001 AND Meeting Date  <= 12/31/2001.

This is a static query built using U2U CAML Builder. It works great in this scenario. Now, if I want to get list items in the year 2001 and 2008 you will see U2U CAML builder spits out in-correct query for you. This is how it looks.

 <Where>
      <And>
         <Geq>
            <FieldRef Name=’MeetingDate’ />
            <Value IncludeTimeValue=’FALSE’ Type=’DateTime’>2008-01-01T20:17:06Z</Value>
         </Geq>
         <Or>
            <Leq>
               <FieldRef Name=’MeetingDate’ />
               <Value IncludeTimeValue=’FALSE’ Type=’DateTime’>2008-12-31T20:17:22Z</Value>
            </Leq>
            <And>
               <Geq>
                  <FieldRef Name=’MeetingDate’ />
                  <Value IncludeTimeValue=’FALSE’ Type=’DateTime’>2001-01-01T20:18:57Z</Value>
               </Geq>
               <Leq>
                  <FieldRef Name=’MeetingDate’ />
                  <Value IncludeTimeValue=’FALSE’ Type=’DateTime’>2001-12-31T20:19:09Z</Value>
               </Leq>
            </And>
         </Or>
      </And>
   </Where>

This query will not work. Instead the CAML query should have been like below.

<Or>
  <And>
    <Geq>
      <FieldRef Name=’MeetingDate’ />
      <Value IncludeTimeValue=’FALSE’ Type=’DateTime’>2008-01-01T00:00:00Z</Value>
    </Geq>
    <Leq>
      <FieldRef Name=’MeetingDate’ />
      <Value IncludeTimeValue=’FALSE’ Type=’Text’>2008-12-31T00:00:00Z</Value>
    </Leq>
  </And>
  <And>
   <Geq>
    <FieldRef Name=’MeetingDate’ />
    <Value IncludeTimeValue=’FALSE’ Type=’DateTime’>2001-01-01T00:00:00Z</Value>
  </Geq>
  <Leq>
    <FieldRef Name=’MeetingDate’ />
    <Value IncludeTimeValue=’FALSE’ Type=’Text’>2001-12-31T00:00:00Z</Value>
  </Leq>
 </And>
</Or>

After spending many hours figured out that I will have to code differently depending on number of year(s) as input.

This is how it goes.

# If the input given is just one year i,e 2001

  customQuery.Append(“<And><Geq><FieldRef Name=’MeetingDate’ /><Value IncludeTimeValue=’FALSE’ Type=’DateTime’>”+  SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(“01/01/”+year)) + “</Value></Geq><Leq><FieldRef Name=’MeetingDate’ /><Value IncludeTimeValue=’FALSE’ Type=’Text’>”+ SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(“12/31/”+year)) +“</Value></Leq></And>”);

 # If the input given is exactly two years i.e 2001 and 2008

customQuery.Append(“<Or>”);
foreach (string year in documentYear.Split(‘;’))
{
  if (!string.IsNullOrEmpty(year))
  {
    customQuery.Append(“<And><Geq><FieldRef Name=’MeetingDate’ /><Value IncludeTimeValue=’FALSE’ Type=’DateTime’>”+ SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(“01/01/” + year)) + “</Value></Geq><Leq><FieldRef Name=’MeetingDate’ /><Value IncludeTimeValue=’FALSE’ Type=’Text’>” + SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(“12/31/” + year)) + “</Value></Leq></And>“);
  }
}
customQuery.Append(“</Or>”);

# If the input is more than two years

int counter = 1;
StringBuilder queryBuilder = new StringBuilder();
foreach (string year in documentYear.Split(‘;’))
{
   if (totalYearsSelected – 2 >= counter)
   {
       customQuery.Append(“<Or><And><Geq><FieldRef Name=’MeetingDate’ /><Value IncludeTimeValue=’FALSE’ Type=’DateTime’>”+ SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(“01/01/” + year)) + “</Value></Geq><Leq><FieldRef Name=’MeetingDate’ /><Value IncludeTimeValue=’FALSE’ Type=’Text’>” + SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(“12/31/” + year)) + “</Value></Leq></And>”);
   }
   else
   {
      if (!string.IsNullOrEmpty(year))
      {
         queryBuilder.Append(“<And><Geq><FieldRef Name=’MeetingDate’ /><Value IncludeTimeValue=’FALSE’ Type=’DateTime’>”+ SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(“01/01/” + year)) + “</Value></Geq><Leq><FieldRef Name=’MeetingDate’ /><Value IncludeTimeValue=’FALSE’ Type=’Text’>” + SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(“12/31/” + year)) + “</Value></Leq></And>”);
      }
   }
counter++;
}
customQuery.Append(“<Or>”+ queryBuilder.ToString() + “</Or>”);

NOTE: I had a multi-select listbox for meeting date. So, I wasn’t sure in advance the number of years as selected input. If your requirement is the same then you will have to use the queries for all the 3 different scenarios in your code.

Advertisements

Read Full Post »