Content Queries Using SharePoint Enterprise Search API
In an earlier post to SharePoint Blues Juho wrote about the challenge of – how to find anything from the sea of content. One way of solving this issue is to use SharePoint Enterprise Search API and the FullTextSqlQuery class. This class allows you to query SharePoint content using SQL syntax. FullTextSqlQuery queries always query from SCOPE(). In the where clause you set the target scope. Here is an example how the query is formed:
As you can see the syntax is SQL and this makes querying content pretty easy. At this point I want to point out that you can only query data that has been indexed by SharePoint so you need to crawl content before you can get any results. This is also a thing that might make using FullTextSqlQuery class impossible – the content isn’t realtime because it needs to be indexed first.
For the following example I created two SharePoint pages. One with a title ‘Example’ and second with a title ‘Example of FullTextSqlQuery’. Below is the example code in which I query content from All Sites scope and where Title field must contain the word ‘Example’:
QueryText = "SELECT Title FROM SCOPE() WHERE \"scope\" = 'All Sites'
AND CONTAINS(Title, 'Example')",
ResultTypes = ResultType.RelevantResults
var queryResults = query.Execute();
var queryResultsTable = queryResults[ResultType.RelevantResults];
var results = new DataTable();
After running the code the results DataTable contains both of the pages I had created earlier. In the query I used predicate CONTAINS and with that I was able to look for word ‘Example’ in the Title field. FullTextSqlQuery uses managed properties in its queries and in the example query Title is an managed property. You can also easily create custom managed properties from your Shared Services Provider under Search Settings.
I think using FullTextSqlQuery is one way to find essential information from the sea of content.
Couple more things to add:
- Remember to use following using statement to make FullTextSqlQuery work:
- I found out that boolean fields in where clause behave bit strangely. I couldn’t get =true working, but I found a workaround for this. You can use !=false instead of =true
Popularity: 95% [?]