Riding CAML in Sharepoint

The way to query a List in the Sharepoint is made easy using a xml based query language called CAML Collaborative Application Marked up Language.

CAML provide key tags for applying Where condition,Sorting Grouping etc.
CAML is applied to a list using SPQuery Object, the query property of the spquery object

Get assigned the required CAML query as a string.

The following is a sample CAML query.

<Query>
   <Where>
      <Or>
         <Eq>
            <FieldRef Name="ContentType" />
            <Value Type="Text">My Content Type</Value>
         </Eq>
         <IsNotNull>
            <FieldRef Name="Description" />
         </IsNotNull>
      </Or>
   </Where>
   <GroupBy Collapse="TRUE">
      <FieldRef Ascending="FALSE" Name="Title" />
   </GroupBy>
   <OrderBy>
      <FieldRef Name="_Author" />
      <FieldRef Name="AuthoringDate" />
      <FieldRef Ascending="TRUE" Name="AssignedTo" />
   </OrderBy>
</Query>


We use this query as following in the C# code

SPQuery query = new SPQuery();
Query.Query = “<Query>..........</Query>”;
SPListItemCollection = list.GetItems(query);

There are few tips and tricks need to be noticed while using CAML quires
1.    You should exclude the <Query> tag from the xml since SPQuery object it self insert <Query> tag for you.
2.    You can set the no of rows returned by a query by using query.RowLimit = #;
3.    In case you want to use a date in a query you need to use SPUtility.CreateISO8601DateTimeFromSystemDateTime() method

Eg: The following query is used to query out list items those have
“Date Of Next Invoice” column is the next day.

string firstDay = SPUtility.CreateISO8601DateTimeFromSystemDateTime(System.DateTime.Today.AddDays(1));
string firstDayPlusOne = SPUtility.CreateISO8601DateTimeFromSystemDateTime(System.DateTime.Today.AddDays(2));
spqQuery.Query = string.Format("<Where><And><Geq><FieldRef Name=\"Date_x0020_of_x0020_Next_x0020_I\" /><Value Type=\"DateTime\">{0}</Value></Geq><Lt><FieldRef Name=\"Date_x0020_of_x0020_Next_x0020_I\" /><Value Type=\"DateTime\">{1}</Value></Lt></And></Where>", firstDay, firstDayPlusOne);

4.Always the static name of the column needs to be given in the name reference. You can get the static Name of the List column at the Url when you edit a column name.note a space in the column name need to be represented by _x0020_. Also Note that When you Name a column as “Date Of next Invoice” the Url will show the static name as “Date%5Fx0020%5FOf%5Fx0020%5FNext%5Fx0020%5FI”    And the corresponding Name will be        “Date_x0020_of_x0020_Next_x0020_I” , Note that the static name wont    take more than 32 chars.
    So what If you have a Column Like “Male Or Female?” The static name                  show in Url as “Male%5Fx0020%5FOr%5Fx0020%5FFemale%5Fx003f”
    so use in the CAML as “Male_x0020_Or_x0020_Female_x003f_”
Ie. Each special character in the column name is represented differently.

5.The following operators are used in the CAML quires

Eq            Equals
Neq           Not equal
Gt            Greater than
Geq           Greater than or equal
Lt            Lower than
Leq           Lower than
IsNull        Is null
BeginsWith    Begins with
Contains      Contains

6.If You are using more than two Or condition the <Or> tags need to be embedded with in

the following wont work

<Where>
 <Or>
 <Eq>
  <FieldRef Name="make" />
  <Value Type="Text">M</Value>
  </Eq>
 <Eq>
  <FieldRef Name="LinkTitle" />
  <Value Type="Text">M</Value>
  </Eq>
 <Eq>
  <FieldRef Name="Team" />
  <Value Type="Text">M</Value>
  </Eq>
  </Or>
  </Where>

Insted need to use the following

 <Where>
 <Or>
 <Or>
 <Eq>
  <FieldRef Name="make" />
  <Value Type="Text">M</Value>
  </Eq>
 <Eq>
  <FieldRef Name="LinkTitle" />
  <Value Type="Text">M</Value>
  </Eq>
  </Or>
 <Eq>
  <FieldRef Name="Team" />
  <Value Type="Text">M</Value>
  </Eq>
  </Or>
 </Where>

Similar case with <And> too

7. With the help of SPQuery object,you Can Run a CAML query on a specific View of a list. Thus restrict the user what they retrive.
8.When running a CamlQuery on an External List, we got an error "The given key was not present in the dictionary.".Apparently you have to specify the <ViewFields> tag in the CamlQuery.ViewXml property. So you have to specify the <ViewFields></ViewFields>
tag with the right fields in the ViewXml property.

9.You Can use Joins tag in CAML QUERY for joining lists applicable for sharepoint2010.