SP Query in Sharepoint 2010

There are many ways to pull data out of SharePoint. You can use the object model and get hold of the SPList object, and run a for/each over SPListItems or you can create a CAML query and pass a SPquery object to retrieve the filtered list items from the SPListItems object.

CAML, the Collaborative Application Markup Language, is used for many purposes within SharePoint, one of which is extracting the very data you need and striking an excellent balance between speed, dependability, and accuracy. SharePoint Developers can write a CAML query specifying the fields they want and the filter condition for the items to be retrieved, and can assign that query to query property of the SPQuery object. This SPQuery object is then passed to the GetItems method on the list items, to retrieve the desired result. Lets looks at an example for writing a basic query using SPquery object.

Basic Query – Query to get all the Items from a list where SPVersion field is equal to “2010″

using (SPSite curSite = new SPSite(SPsiteUrl))
{
SPWeb curWeb = site.OpenWeb();
SPQuery curQry = new SPQuery();

//Write the query (I suggest using U2U Query Bulider Tool)
curQry.Query = “<Where><Eq><FieldRef Name=’SPVersion’ /><Value Type=’Text’>2010</Value></Eq></Where>”;

//Get the List
SPList myList = myWeb.Lists["ListName"];

//Get the Items using Query
SPListItemCollection curItems = myList.GetItems(curQry);

SPQuery enhancements in SharePoint 2010 - With the Introduction of Referential integrity implemented by Lookup columns in SharePoint 2010, developers now also have the ability to use some new fancy properties of the SPQuery object.
The new added enhancements in CAML and SPquery are :

1. <Joins> element in CAML and the SPQuery.Joins property
2. <ProjectedFields> element in CAML and the SPQuery.ProjectedFields.

Joins - Joins in SharePoint are driven off of lookup-based columns.
For e.g. if we a have List1 with following column. City being the Lookup column.

Name
Address
City <strong>-> Lookup Column</strong>
and List2 with following columns
City
ZipCode
and if we have to access the Zipcode column from List2 along with the name and address from our List1 we will use a Join element in Our CAML to specify the our foreign key column i.e. City and will assign the CAML to SPquery.query property. We will then create the ProjectedFields element for specifying the ZipCode column (the column we need from List2) and assign it to the SPquery.ProjectedFields property.

ProjectedFields - These are fields in the List2, which you can access using CAML’s ProjectedFields element tag.
Let’s look at an example. In the below query i am trying to retrieve all the items from List1 where name =”isha” and the related zipcode field from the List2.

using (SPSite site = new SPSite(SPsiteUrl))
{
SPWeb web = site.OpenWeb();
SPQuery query = new SPQuery();
query.Query =
@”<Where><Eq>
<FieldRef Name=’Name’ /><Value Type=’Text’>Isha</Value>
</Eq></Where>”
query.Joins =
@”<Join Type=’LEFT’ ListAlias=’List2′>
<Eq>
<FieldRef Name=’City’ RefType=’Id’/>
<FieldRef List=’List2′ Name=’ID’/>
</Eq>
</Join>”;
query.ProjectedFields =
@”<Field
Name=’Zipcode’
Type=’Lookup’
List=’List2’
ShowField=’Zipcode’ />”;
SPListItemCollection items = web.Lists["List1"].GetItems(query);that’s it!

Basic Query - Query to get all the Items from a list where Category field is equal to "Sp2007"

// Get SiteColl
SPSite curSite = new SPSite("http://myPortal");

//Get Web Application
SPWeb curWeb = curSite.OpenWeb();

// Create a SPQuery Object
SPQuery curQry = new SPQuery();

//Write the query (I suggest using U2U Query Bulider Tool)
curQry.Query = "<Where><Eq><FieldRef Name='Category' />
<Value Type='Text'>
SP2007 </Value></Eq></Where>";

// Set the Row Limit
curQry.RowLimit = 100;

//Get the List
SPList myList = myWeb.Lists["ListName"];

//Get the Items using Query
SPListItemCollection curItems = myList.GetItems(curQry);

// Go through the resulting items
foreach (SPListItem curItem in curItems)
{
string ResultItemTitle = curItem["Title"].ToString();
}

Query on DateTime Field - Query to Find Items in the List with Today's date.

// Create a SPQuery Object
SPQuery DateFieldQuery = new SPQuery();


//Write the query (I suggest using U2U Query Bulider Tool)
DateFieldQuery.Query = “<Where><Eq><FieldRef Name=\ ”TodaysDate\” />
<Value Type=\ ”DateTime\”> + DateTime.Now.ToString("yyyy-MM-ddTHH:\\:mm \\:ssZ") + </Value>
</Eq></Where>”;

//Get the List
SPList myList = myWeb.Lists["ListName"];

//Get the Items using Query
SPListItemCollection ResultItems = myList.GetItems(DateFieldQuery);
Query Using Yes\No Columns - Query to Retrieve all the Items from a list where a Yes\NO type Field, named "AreYouCool?" is "Yes".


// Create a SPQuery Object
SPQuery CheckBoxQuery = new SPQuery();


//Write the query (I suggest using U2U Query Bulider Tool)
CheckBoxQuery .Query = “<Where><Eq><FieldRef Name=\ ”AreYouCool?\” />
<Value Type=\ ”bit\”>1</Value>
</Eq></Where>”;

//Get the List
SPList myList = myWeb.Lists["ListName"];

//Get the Items using Query
SPListItemCollection ResultItems = myList.GetItems(CheckBoxQuery);