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>
<FieldRef Name=\'FileLeafRef\'/>
<Value Type=\"Text\"></Value>
</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))
{
{
using (SPWeb web = site.OpenWeb(WebName))
{
SPQuery query = new SPQuery();
query.ViewAttributes = "Scope=\"Recursive\"";
query.Folder = list.RootFolder;
query.ViewAttributes = "Scope=\"Recursive\"";
query.Folder = list.RootFolder;
do
{
{
SPListItemCollection listItems = list.GetItems(query);
//Perform the operations you want
//Perform the operations you want
query.ListItemCollectionPosition = listItems.ListItemCollectionPosition;
//If you are showing in a grid view then stor the listItems.ListItemCollectionPosition in
//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;
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:
1.) Title
2.) Test
I need to use CAML query and get only the items which has null values in "Test" column.
Code snippet:
|
