As a sharepoint developer most of the people might have used SPQuery and CAML atleast once.

Writing a Basic Query

SPContext ctx = SPContext.Current;

using (SPSite site = new SPSite(ctx.Site.ID))

{

using (SPWeb web = site.OpenWeb(webid))

{

SPList list = web.Lists["MyTestList];

SPQuery query = new SPQuery();

//You have to call GetItems function of SPlist to execute the CAML query

SPListItemCollection items = list.GetItems(query);

}

Here it will return All the Items.But in real time we need to specify the conditions and attributes to fine tune the Results. We need to have basic understanding of CAML syntax inorder to take full advantage of SPQuery Object.If you have a CAMLBuilder tool then it will be always handy in writing CAML queries.You could douwnload CAML builder from here

Understanding SPQuery Properties

1. SPQuery.RowLimit

This Property allows you to set how many rows you need in your result.Its quiet common that your Query could return more than 500 rows so its better to limit the number of rows in a result and use paging to retrive the rest of the result.I will explain paging below.

2. spquery.ViewAttributes

View attribute is one of the important attribute which is veryusefull to set how you are going to retrieve the Data.

Scenario 1: You want retrieve all the items including items in folder and subfolders then set it like the following

//If you want to return only file

SPQuery.ViewAttributes = "Scope='Recursive'"; //If you want to return All items

SPQuery.ViewAttributes = "Scope='RecursiveAll'";

Scenerio 2 : You want retrive only files

spquery.ViewAttributes = "Scope=\"FilesOnly\"";

Scenario 3: Only Approved Items

spquery.ViewAttributes = "ModerationType='HideUnapproved'"

3. SPQuery.Folder

This property is usefull when you want to retrieve the data only from specific folder.In real world scenario you need to retrive the items from specific folder rather than retrieving all items in the list.

SPQuery.Folder = folder; //It should be a SPFolder Object

4.SPQuery.ViewFields

This property helps to set which are the fields /columns you want in the result.There is no point in returning all the coloumns.So its better practice to specify the fields you want to return.

//Name is the coloumn name / internal name of the field

SPQuery.ViewFields = "";

5. SPQuery.Query

This property used to set the real CAML query.Build your CAML string and assign to this property.

SPQuery.Query = String.Format("<Where>

<Eq>

</Eq>

</Where>", searchText);

//searchText is name of the file you want to search

Note : One thing here we have to remember is no need to add query tag in your XML.

If you are using Date time in your query then set includetimevalue = true. See Below

Query = "<Where><Eq><FieldRef Name='Modified'/><Value Type='DateTime' IncludeTimeValue='TRUE'>"+ LastCheckedDate + "</Value></Eq>

</Where>"

CAML Notations

•Eq = equal to

•Neq = not equal to

•BeginsWith = begins with

•Contains = contains

•Lt = less than

•Leq = less than or equal to

•Gt = greater than

•Geq = greater than or equal to

•IsNull = is null

•IsNotNull = is not null

Paging In SPQuery

Using paging with SPQuery is good practice.Its better to write code that will perform efficient way than giving bad user experience.

      using (SPSite site = new SPSite(SiteCollectionURL))
         {

           using (SPWeb web = site.OpenWeb(WebName))
             {

               SPQuery query = new SPQuery();
               query.ViewAttributes = "Scope=\"Recursive\"";
               query.Folder = list.RootFolder;

do
{

                   SPListItemCollection listItems = list.GetItems(query);

                  //Perform the operations you want

query.ListItemCollectionPosition = listItems.ListItemCollectionPosition;
//If you are showing in a grid view then stor the listItems.ListItemCollectionPosition in

viewstate or something like that

               } while (query.ListItemCollectionPosition != null);
             }
         }

Limitation of SPQuery Object

You cannot use the SPquery object to query across multiple site collection instead use SPSiteDataQuery

Create dynamic SPQuery in Sharepoint

string myDynamicQuery = "<Eq><FieldRef Name=\"Author\" /><Value Type=\"User\">" + web.CurrentUser.Name.ToString() + "</Value></Eq>";

object[] distinctPostIds = GetDistinctPostIds();

for (int i = 0; i < distinctPostIds.Length; i++)
{
     strDynamicQuery = "<Or>" + myDynamicQuery + "<Eq><FieldRef Name=\"ID\" /><Value Type=\"Counter\">" + distinctPostIds[i].ToString() + "</Value></Eq></Or>";
}

myDynamicQuery = "<Where>" + myDynamicQuery + "</Where><OrderBy><FieldRef Name=\"Created\" Ascending=\"False\" /></OrderBy>";

// Sample for multiple OR conditon:
//<Where>
//<Or>
//<Or>
//<Or>
//<Or>
//        <Eq><FieldRef Name=\"ID\" /><Value Type=\"Counter\">1</Value></Eq>
//        <Eq><FieldRef Name=\"ID\" /><Value Type=\"Counter\">2</Value></Eq>
//</Or>
//        <Eq><FieldRef Name=\"ID\" /><Value Type=\"Counter\">3</Value></Eq>
//</Or>
//        <Eq><FieldRef Name=\"ID\" /><Value Type=\"Counter\">4</Value></Eq>
//</Or>
//        <Eq><FieldRef Name=\"ID\" /><Value Type=\"Counter\">6</Value></Eq>
//</Or>
//</Where>

SPQuery myQuery = new SPQuery();
myQuery.Query = myDynamicQuery;

Check for null values in CAML query

I have a custom list named "Custom" which has the following columns.

1.) Title
2.) Test

I need to use CAML query and get only the items which has null values in "Test" column.

Code snippet:

 
using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.SharePoint;

using System.Data;

using System.Globalization;




namespace ISNULL

{

    class Program

    {

        static void Main(string[] args)

        {

            using (SPSite site = new SPSite("http://serverName/sites/Vijai"))

            {

                using (SPWeb web = site.OpenWeb())

                {

                    SPList list = web.Lists.TryGetList("Custom");

                    SPQuery query = new SPQuery();

                    query.Query = "<Where><IsNull><FieldRef Name='Test' /></IsNull></Where>";

                    SPListItemCollection itemColl = list.GetItems(query);

                    foreach (SPListItem item in itemColl)

                    {

                        Console.WriteLine(item["Title"]);

                    }

                    Console.ReadLine();                  

                }

            }

        }

    }

}

CAML Query Template

Get Top 5 list Items from Sharepoint List using CAML

This is a sample code for retrieving top 5 items from the SharePoint List.

SPQuery is the SharePoint class to initialize the CAML query.

This following Query will display the top 5 items OrderBy ID as Ascending is false.

using (SPSite objSite = new SPSite("<Site URL>"))

{

using (SPWeb objWeb = objSite.OpenWeb())

{

SPList spList = objWeb.GetList("<List URL>");

SPQuery spQuery = new SPQuery();

spQuery.Query = "<Query>

</OrderBy> </Query> ";

spQuery.RowLimit = 5;

SPListItemCollection spListItemCollection = spList.GetItems(spQuery);

}