SPQuery & CAML Query in SharePoint

SPQuery used in SharePoint to retrieve/query the data from Lists  For that we need to use CAML Queries

CAML stands for Collaborative application markup language,which is basically xml format language query style.

Operators
Some list of operators used in SPQuery

1. Eq--Equals
2. Neq--Not equal
3. Gt--Greater than
4. Geq--Greater than or equal
5. Lt--Lower than
6. Leq--Lower than
7. IsNull--Is null
8. BeginsWith--Begins with
9. Contains--Contains

Below are some SPQuery operators with examples...

Let's create a SharePoint list, name it EmpList
1- Create Column EmpName of type 'SingleLineOfText'
2- Create Column EmpStatus of type 'SingleLineOfText'
3- Create Column Salary of type 'Number'

Simple SPQuery example using RowLimit and OrderBy:

using (SPSite _site = newSPSite("http://myServer"))

 {

  using (SPWeb _web = _site.OpenWeb())

  {

   SPListoList = _web.Lists["EmpList"];

   SPQuery _query = newSPQuery();

//Query to get max 10 employees whose EmpStatus is Active and order by ID desc

 _query.Query = "<Where><Eq><FieldRef Name='EmpStatus' /><Value Type='Text'>Active</Value></Eq><Where><OrderBy><FieldRef Name='ID' Ascending='FALSE' /></OrderBy>";

  //Set Row Limit to 10

  _query.RowLimit = 10;

  SPListItemCollection _itemCollection = oList.GetItems(_query);

  foreach (SPListItem _item in _itemCollection)

    {

      Response.Write("Employee Name: " + _item["EmpName"].ToString());

    }

  }

 }

Value Type used in SPQuery

1. Single line of text--Text
2. Multiple lines of text--Note
3. Choice (menu to choose from)--Choice
4. Number (1, 1.0, 100)--Number
5. Date and Time--DateTime
6. Lookup (information already on this site)--Lookup/LookupMulti
7. Yes/No (check box)--Boolean
8. Person or Group--User

2   SPQuery - using Single And Operator

//Modify above Query to get Employee details where EmpName='mohit' and EmpStatus='Active'

 _query.Query = "<Where><And><Eq><FieldRef Name='EmpName' /><Value Type='Text'>Mohit</Value></Eq><Eq><FieldRef Name='EmpStatus' /><Value Type='Text'>Active</Value></Eq></And><Where>" 

SPQuery - using OR Operator

//Query to get Employee details whose EmpStatus='Active' OR EmpStatus='InActive'

_query.Query = "<Where><Or><Eq><FieldRef Name='EmpStatus' /><Value Type='Text'>Active</Value></Eq><Eq><FieldRef Name='EmpStatus' /><Value Type='Text'>InActive</Value></Eq></Or><Where>"; 

1     SPQuery - using BeginsWith Operator

 //Query to get all Employees whose EmpName BeginsWith char 'M'

 _query.Query = "<Where><BeginsWith><FieldRef Name='EmpName' /><Value Type='Text'>m</Value></BeginsWith><Where>"; 

SPQuery - using Greater Than Equal to and Less Than Equal to Operator

//Query to get all Employees whose Salary>=25000 and Salary<=50000

_query.Query = "<Where><And><Geq><FieldRef Name='Salary'/><Value Type='Number'>25000</Value></Geq><Leq><FieldRef Name='Salary'/><Value Type='Number'>50000</Value></Leq></And></Where>"; 

SPQuery - using Contains Operator

//Query to get all the Employees where EmpName Contains 'Singh'

  _query.Query = "<Where><Contains><FieldRef Name='EmpName'/><Value Type='Text'>Singh</Value></Contains></Where>"; 

 SPQuery - using IsNull Operator

//Query to get all the Employees where EmpNameIsNull

   _query.Query = "<Where><IsNull><FieldRef Name='EmpName'></FieldRef></IsNull></Where>"; 

SPQuery to get all list items created by user

using (SPSite Site = newSPSite("http://myServer"))

 {

using (SPWeb Web = Site.OpenWeb())

 {

   SPList _list = Web.Lists["myCustomList"];

   SPQuery _query = newSPQuery();

   _query.Query = "<Where><Eq><FieldRef Name='Author' LookupId='TRUE'/><Value Type='Integer'><UserID /></Value></Eq></Where><OrderBy><FieldRef Name='ID' Ascending='FALSE'/></OrderBy>";

SPListItemCollection _ItemColl = _list.GetItems(_query);

if (ItemColl.Count> 0)

  {

        foreach (SPListItem _item in _ItemColl)

         {

            Response.Write( _item["Title"].ToString());

          }

  }

 }

}

SPQuery to get task of users who is member of current group

using (SPSite Site = newSPSite("http://myServer"))

 {

using (SPWeb Web = Site.OpenWeb())

 {

   SPList _list = Web.Lists["myCustomList"];

   SPQuery _query = newSPQuery();

   _query.Query = ""<Where><Membership Type='CurrentUserGroups'><FieldRef Name='AssignedTo'/></Membership></Where><OrderBy><FieldRef Name='ID' Ascending='FALSE' /></OrderBy>";

SPListItemCollection _ItemColl = _list.GetItems(_query);

if (ItemColl.Count> 0)

  {

        foreach (SPListItem _item in _ItemColl)

         {

            Response.Write( _item["Title"].ToString());

          }

  }

 }

}