Querying SharePoint

Monday, March 30, 2015

Introduction

SharePoint, being a content management system, of course, offers a couple of ways to query its contents programmatically. Here we will explore some of these options.

Web Parts

First, of course, there are the web parts. These allow us to configure queries visually on a page. The most important web parts are:

ContentByQueryWebPart: use this for simple queries that do not return much contents, on a single site collection. Can be used in SharePoint Online. Can only be customized through XSLT.

ContentBySearchWebPart (introduced in SharePoint 2013): more powerful, but does not exist in SharePoint Online. Can handle more complex queries that can span multiple site collections and multiple levels of sorting. Can only be customized through HTML and JavaScript templates. Results can be refined.

XsltListViewWebPart/DataFormWebPart: can be used to view of a specific list. The display can be totally configured. XsltListViewWebPart is a bit more powerful.

ListViewByQuery: requires that you pass an SPList and a SPQuery instance. Can only display pre-defined views.

APIs

There are several APIs for querying, either directly or using the search index.

SPQuery: can only be applied to a single list:

var query = new SPQuery();

query.Query = "<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>Bla Bla</Value></Eq></Where>";

var list = site.RootWeb.Lists["Some List"];

var table = list.GetItems(query).GetDataTable();

SPSiteDataQuery: can be applied to several lists across a site collection. Has some issues, for example, won't return values for multi-value fields:

var query = new SPSiteDataQuery();

query.Lists = "<List Name='Tasks'/>";

query.Query = "<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>Bla Bla</Value></Eq></Where>";

query.Webs = "<Web Scope='SiteCollection'/>";

var table = site.RootWeb.GetSiteData(query);

KeywordQuery: uses the search index to search for keywords, so it requires that contents are indexed beforehand, and the search service is functioning:

using (var query = new KeywordQuery(site))

{

    query.QueryText = "Bla Bla";

    query.ResultsProvider = SearchProvider.Default;

    var searchExecutor = new SearchExecutor();

    var resultTableCollection = searchExecutor.ExecuteQuery(query);

    var searchResult = resultTableCollection.Filter("TableType", KnownTableTypes.RelevantResults).Single();

    var table = new DataTable();

    table.TableName = "Result";

    table.Load(searchResult, LoadOption.OverwriteChanges);

}

FullTextSqlQuery: uses SharePoint Search SQL to execute queries, which makes it generally more powerful than KeywordQuery:

using (var query = new FullTextSqlQuery(site))

{

    query.QueryText = "SELECT * FROM scope() WHERE Title = 'Teste'";

    query.ResultsProvider = SearchProvider.Default;

    var searchExecutor = new SearchExecutor();

    var resultTableCollection = searchExecutor.ExecuteQuery(query);

    var searchResult = resultTableCollection.Filter("TableType", KnownTableTypes.RelevantResults).Single();

    var table = new DataTable();

    table.TableName = "Result";

    table.Load(searchResult, LoadOption.OverwriteChanges);

}

CrossListQueryInfo and CrossListQueryCache: performs queries in a single site collection but multiple sites, with optional audience targeting. CrossListQueryCache caches the results for a period of time:

var crossListQueryInfo = new CrossListQueryInfo();

crossListQueryInfo.Query = "<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>Bla Bla</Value></Eq></Where>";

crossListQueryInfo.Lists = "<List Name='Some List'/>";

crossListQueryInfo.Webs = "<Webs Scope='SiteCollection' />";

crossListQueryInfo.UseCache = true;

var crossListQueryCache = new CrossListQueryCache(crossListQueryInfo);

var table = crossListQueryCache.GetSiteData(site.RootWeb);

Client KeywordQuery: this is the client counterpart to KeywordQuery. It uses the SharePoint Client Components, or Client Side Object Model (CSOM), which means, it accesses SharePoint through its web services. It is basically similar to the server KeywordQuery, but can be used from a remote machine:

var keywordQuery = new Microsoft.SharePoint.Client.Search.Query.KeywordQuery(ctx);

keywordQuery.QueryText = "SharePoint";

var searchExecutor = new Microsoft.SharePoint.Client.Search.Query.SearchExecutor(ctx);

var results = searchExecutor.ExecuteQuery(keywordQuery);

ctx.ExecuteQuery();

var searchResult = results.Value.Where(x => x.TableType == KnownTableTypes.RelevantResults.ToString()).Single();

Web Services

Search.asmx: SOAP web service that takes a SharePoint SQL query. Part of the SharePoint Foundation 2010 Web Services, now somewhat obsolete.

Lists.asmx: Another SOAP web service that can return and update items in a list.

ListData.svc: WCF Data Service REST/OData that can be used for querying (including OData queries) or modifying contents of lists.

SharePoint 2013 REST Services: new REST/OData web services introduced in SharePoint 2013. Includes _api/web, _api/search, _api/web/lists, for searches, web or list operations.

SharePoint Foundation RPC Protocol

The SharePoint Foundation RPC Protocol, now obsolete, this allowed querying, exporting contents and performing a number of other operations through the OWSSVR.DLL handler. Although almost unused nowadays, still offers the only out of the box way to, for example, export a list in XML format.

JavaScript

In SharePoint 2010 the JavaScript Side Object Model (JSOM) was introduced and in version 2013 it was enhanced. It is now possible to do anything that the SharePoint Client API allows.