Tuesday, July 28, 2009

SPQuery Few important things

1. Always specify the RowLimit to get limited results.

oQuery.RowLimit = 100;
2. To Get the Search Items from the sub folders of the list using SPQuery you can do following. Check here
SPQuery.ViewAttributes = "Scope='Recursive'";
or
SPQuery.ViewAttributes = "Scope='RecursiveAll'";
3. Be sure to create a new SPQuery instance each time you use it. You cannot create one SPQuery instance and then reuse it multiple times in a loop where you alter the SPQuery's Query value.
 for (int i = 0; i < 10 ; i++)
{
SPQuery oQuery = new SPQuery();
oQuery.Query = "<Where><Eq><FieldRef Name='AssignedTo' /><Value Type='UserMulti'>"+i +"</Value></Eq></Where>";
SPListItemCollection collListItems = list.GetItems(oQuery);
}
4. SPQuery query object doesn't need to include tags
oQuery.Query = "Completed";
5. If you want to do CAML query which also consider the time in datetime fields then try this
<Where><Eq><FieldRef Name='Created'/><Value Type='DateTime' IncludeTimeValue='TRUE'><Today /></Value></Eq></Where>
6. SPQuery can't be used to search across site collection.One can use SPSiteDataQuery class to search across multiple Lists.

To query the lookup based value items
<where><eq><fieldref name="Employee" lookupid="TRUE"><value type="User">1</value></eq></where>  
7. To restrict the items to approved, use moderationtype attribute. more details here
SPQuery.ViewAttributes = "Scope='Recursive' ModerationType='HideUnapproved'";
8. If you want your query to return empty columns, you have to add Nullable='TRUE' to the viewfields.
query.ViewFields="<FieldRef Name='Description' Nullable='TRUE'/>";
To get the item related within past few days , you can use OffsetDays attribute along with Today.
<Where><Eq><FieldRef Name='Created'/><Value Type='DateTime' ><Today OffsetDays='-7' /></Value></Eq></Where>
Article:
SPQuery Best Practice

4 comments:

Ashwin,  August 26, 2010 at 3:11 AM  

I am working with WSS 2.0, so only SPQuery is available for use. With or without using SPQuery, what is the recommended approach for listing out the entire folder hierarchy inside a document library recursively, along with retrieving the items within that hierarchy? Without SPQuery, if I simply use list.Items, that yields all the non-folder items in the document library. I need the folder items too.

Thanks,
Ashwin

Sandeep K Nahta August 26, 2010 at 3:48 AM  

Ashwin

Check point 2 : Recursive, it will give u all the items inside folders also

Sandeep

=8)-DX,  April 10, 2012 at 4:25 AM  

What was this supposed to be?
oQuery.Query = "Completed";

That's invalid C# as well as invalid CAML. Unless there's been a change in SP 2010, CAML is case sensitive isn't it? Please fix this otherwise great post, wish this would have been around back in the day.

Sandeep K Nahta April 10, 2012 at 5:09 AM  

=8)-DX

i didn't get ur comment.. I dont see

oQuery.Query = "Completed"; anywhere