There are many ways we can query sharepoint data. Below are the objects used for querying sharepoint data.
1) SPQuery - This is used to retrieve data from a specific list.
2) SPSiteDataQuery - This is used to query data from multiple lists across different sites in a site collection.
3) CrossListQueryCache - The SharePoint CrossListQueryCache class provides a highly scalable way to run site wide queries. It provides all the power of SPSiteDataQuery without the performance implications. However it has some major gotchas that you should be aware of before using.
4) PortalSiteMapProvider - The PortalSiteMapProvider class allows us to access cached objects for sites, lists, and list items.
Details and Examples:
SPQuery:
Scenario:
To get the list of items from SharePoint list whose age is greater than 10.
Assumption: Student List is created with FirstName, LastName, Age and the name of the list is Student.
Code:
SPWeb spWeb = SPContext.Current.Site.RootWeb;
SPList spList = spWeb.Lists["Students"];
SPQuery spQuery = new SPQuery();
spQuery.Query = "Write Caml query here" //like select * from table where Age > 10
//Execution of below line of code will return SPListItemCollection
SPListItemCollection collListItems = spList.GetItems(spQuery);
1) SPQuery - This is used to retrieve data from a specific list.
2) SPSiteDataQuery - This is used to query data from multiple lists across different sites in a site collection.
3) CrossListQueryCache - The SharePoint CrossListQueryCache class provides a highly scalable way to run site wide queries. It provides all the power of SPSiteDataQuery without the performance implications. However it has some major gotchas that you should be aware of before using.
4) PortalSiteMapProvider - The PortalSiteMapProvider class allows us to access cached objects for sites, lists, and list items.
Details and Examples:
SPQuery:
Scenario:
To get the list of items from SharePoint list whose age is greater than 10.
Assumption: Student List is created with FirstName, LastName, Age and the name of the list is Student.
Code:
SPWeb spWeb = SPContext.Current.Site.RootWeb;
SPList spList = spWeb.Lists["Students"];
SPQuery spQuery = new SPQuery();
spQuery.Query = "Write Caml query here" //like select * from table where Age > 10
//Execution of below line of code will return SPListItemCollection
SPListItemCollection collListItems = spList.GetItems(spQuery);
//Loop through each item and perform the operation
foreach (SPListItem spListItem in collListItems) {
//Process each item
}
There will be an question arise why can't we use Linq instead of SPQuery.
If we have Linq then it needs to run under full trust proxy which is not possible in sand box solution at that time we need to use SPQuery.
SPSiteDataQuery:
Scenario:
To get list of items from different list under multiple sites(webs) under same site collection.
Follow these steps for using the SPSiteDataQuery class
- Create a SPSiteDataQuery instance.
- Set properties on the SPSiteDataQuery instance to specify the lists or list types to include in the query, the individual sites to include in the query, and the CAML query itself.
- Call the GetSiteData method on a SPWeb instance, passing in the SPSiteDataQuery instance as a parameter. The GetSiteData method returns a DataTable.
Code:
SPSiteDataQuery dataQuery = new SPSiteDataQuery();
//Here if we set Scope to Recursive then the query considers the current Web site and all subsites of the current Web site.
//If we set Scope to SiteCollection, the query considers all Web sites that are in the same site collection as the current Web site.
//Code snippet will be like dataQuery.Webs = "
dataQuery.Lists = "
dataQuery.ViewFields = "
DataTable dtable = web.GetSiteData(dataQuery);
//Do whatever the required operations here
When to use SPSiteDataQuery:
SPSiteDataQuery is commonly used in list aggregation scenarios, where list data from team sites or other subsites is collated and presented in a single interface. The SPSiteDataQuery will only aggregate data from SharePoint lists and will ignore data from external lists.
In terms of efficiency, the SPSiteDataQuery class provides an optimal approach to data access in the following scenarios:
- When we need to query multiple lists within the same site collection for the same content
- When we need to query across two or more lists that are not related by lookup columns
The SPSiteDataQuery class is optimized for querying list data across multiple sites in a site collection and across multiple lists within a single site.
CrossListQueryCache:
The SharePoint CrossListQueryCache class provides a highly scalable way to run site wide queries.
The CrossListQueryInfo object uses the CrossListQueryInfo object to get the cached results or, if there are no cached results available, it performs a cross-list query to the database and then caches the results for future use.
How to use CrossListQueryCache:
CrossListQueryInfo query = new CrossListQueryInfo();
query.Lists = "
" +
"Completed ";
query.UseCache = true;
CrossListQueryCache cache = new CrossListQueryCache(query);
DataTable results = cache.GetSiteData(spSite);
Where to use CrossListQueryCache:
It should be used when we need to query one or more lists and store the results in cache so subsequent calls are at near lightning speed.
There are 4 overloads for CrossListQueryCache.GetSiteData. Two of the overloads cache the results and two do not.
| CrossListQueryCache Routine | Uses Cache |
| GetSiteData (SPSite) | Yes |
| GetSiteData (SPWeb) | No |
| GetSiteData (SPSite, String) | Yes |
| GetSiteData (SPWeb, SPSiteDataQuery) | No |
So unless we are using one of the overloads that support caching and have the CrossListQueryInfo.UseCache property set to true we might as well use SPSiteDataQuery.
PortalSiteMapProvider:
The PortalSiteMapProvider class allows us to access cached objects for sites, lists, and list items. It also provides methods for executing SPQuery and SPSiteDataQuery queries against cached SharePoint lists. We can use the GetCachedListItemsByQuery method to cache the query results. Executing a SPSiteDataQuery query with a PortalSiteMapProvider object is similar to using the CrossListQueryCache class.
How to use PortalSiteMapProvider:
To retrieve items from a list PortalSiteMapProvider class includes a method called GetCachedListItemsByQuery that retrieves data from a list based on a SPQuery object that is provided as a parameter to the method call.
The method then looks in its cache to see if the items already exist. If they do, the method returns the cached results, and if not, it queries the list, stores the results in cache and returns them from the method call.
Code:
SPQuery spQuery = new SPQuery();
spQuery.Query = ”
“
”;PortalSiteMapProvider ps = PortalSiteMapProvider.WebSiteMapProvider;
var pNode = ps.FindSiteMapNode(web.ServerRelativeUrl) as PortalWebSiteMapNode;
var Items = ps.GetCachedListItemsByQuery(pNode, ”Tasks”, spQuery, web);
foreach (var item in Items )
{
//Do what ever want here
}
Where to use PortalSiteMapProvider:
A primary advantage of the PortalMapSiteProvider class is its use and management of the SharePoint object cache. The object cache provides highly efficient access to objects that are not part of a list. By default, the PortalSiteMapProvider class queries and manages the object cache.
