Monday 26 September 2011

What is CAML?Using CAML In SharePoint Object Model To Query List

The Collaborative Application Markup Language (known as CAML) is an XML-based query language that helps querying, building and customizing Web sites based on Windows SharePoint Services. The XML elements define various aspects of a WSS(Windows SharePoint Services) site.

The CAML language has been associated with SharePoint since the first version. It is based on a defined Extensible Markup Language (XML) document that will help to perform a data manipulation task in SharePoint. It is easy to relate a list to CAML if you compare it to a database table and query.

A CAML query must be a well-formed XML document.

Its root element is Query. Within the Query element two other elements are possible but not required: the “OrderBy” element and the “Where” element.

The “OrderBy” clause is not required and it consists of fields which you required to sort. There is a property “Ascending”. If it is set to “True” then result is sort in ascending order and if it is “False” then the result is sort in descending order if you not declare the “Ascending” property then the result is in ascending by default.

The Where clause is used to specify one or more filter criteria. This clause can be very simple but can end up being rather complex. In its most simple form you specify an operator, a field name for which you want to specify a criterion, and a value.

Example:

SQL where Clasue: ” Where Name=’Shoaib’ and RollNo=’28’ order by Name Desc ”

Now convert this into CAML:
<query>
 <where>
  <and>
   <eq>
    <fieldref name='Name'>
   <value type='Text'>Shoaib</value>
    </fieldref>
   </eq>
   <eq>
    <fieldref name='RollNo'>
    <value type='Text'>28</value>
    </fieldref>
  </eq>
 </and>
</where>
 <orderby>
  <fieldref ascending='False' name='Name'>
  </fieldref>
</orderby>
</query>

Now In Above example “Where” and “OrderBy” is the main clause after that “And” tag which is used to “And” both the result. Most important thing is “Eq” tag Eq(Equal to) is same as “=” operator. Similarly following are the different operators which we can use in CAML.

Eq:   Equals

Neq:   Not equal

Gt:   Greater than

Geq:   Greater than or equal

Lt:   Lower than

Leq:   Lower than or equal

IsNull:   Is null

BeginsWith:   Begins with

Contains:    Contains
Fields

“Value” element in above example is simple contains the data type of the attribute and value to compare with.

Now here is the simple example of console programming retrieving List Items with CAML using the SharePoint Object Model.


Example:
1) Open a visual studio2010 in higher privileges mode (Run as Administrator).It is required because to access sharepoint 2010 object models we need higher privilege mode.

2) Create new console C# project.

Note: please select .net 3.5 for targeting framework otherwise it is difficult to find the “Microsoft.Sharepoint” library which is referenced later.

3) Now add rederence to the “Microsoft.Sharepoint” found in .Net Tab.

4)It is important to build and run your application in 64 bit mode to access sharepoint 2010 object models. For this purpose right click your project in solution explorer and click properties.

In properties window click on build tab and select platform target as “x64” instead of “x86”.Save and close the property window.

5) Now type the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {
          //Create object of your site
            using (SPSite siteCollection = new SPSite("http://shoaibmuhammadk/"))
            {           

              //get tasks list from your website
                SPList Tasks = siteCollection.AllWebs[0].Lists["Tasks"];
                //create query object on View of your task list
                SPQuery query = new SPQuery(Tasks.DefaultView);
                //Here we define the fields which display in output of caml query
               query.ViewFields = "<FieldRef Name='Title'/><FieldRef Name='DueDate'/><FieldRef Name='Status'/>";
                query.Query = "<Where><Eq><FieldRef Name='Title'/>"
                    + "<Value Type='Text'>"
                    + "MyTask"
                    + "</Value></Eq></Where>";
               //Get all tsks item satisfy the CAML query
               SPListItemCollection mydata=Tasks.GetItems(query);
               //Now loop the obtained list items and display it
               foreach (SPListItem ListItem in mydata)
               {
                   Console.WriteLine("Title:"+ListItem["Title"].ToString()+" Due-date:"+ListItem["Due Date"].ToString()+" Progress:"+ListItem["Status"].ToString());

               }               
            }

            Console.Write("Press ENTER to continue");
            Console.ReadLine();
        }
    }
}

6) Build your application to ensure there is no error!

7) Before running the project. Open your sharepoint site and add some data to list which is queried by our code.




As you see in above image I add four rows to Tasks,Three rows with Title ”MyTask” and one row is with different Title.
We query to the list through CAML with where clause Title=’MyTask’

So three rows should display in output of our code.

6) Run the project.


Three rows in output so our CAML query tested successfully.

Hope It Helps!

Happy Coding :)

For further details about CAML visit: http://msdn.microsoft.com/en-us/library/ms467521.aspx

4 comments:

  1. nice one... i really didnt knw any thin about CAML b4 reading this article.. now i m aware and much understand about caml.. thanks bro 4 this beneficial article..

    ReplyDelete
  2. It's good...like me who doesn't know anything about Sharepoint. :)

    ReplyDelete
  3. Nice post Bro..

    ReplyDelete