Content Queries Using SharePoint Enterprise Search API

February 15 2010 54 comments

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:

SELECT Title FROM SCOPE() WHERE "scope" = 'All Sites'

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’:

var query = new FullTextSqlQuery(SPContext.Current.Site)
{
    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();
results.Load(queryResultsTable, LoadOption.OverwriteChanges);

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:

using Microsoft.Office.Server.Search.Query;

- 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: 100% [?]

54 comments to “Content Queries Using SharePoint Enterprise Search API”

  1. Everything published made a great deal of sense.

    But, think about this, what if you typed a catchier title?
    I ain’t suggesting your information isn’t solid, however suppose you added a title that makes people want more?
    I mean Content Queries Using SharePoint Enterprise Search API | SharePoint Blues is kinda vanilla.
    You might peek at Yahoo’s home page and note how they create
    post headlines to get viewers to open the links. You might add a related video or a related
    picture or two to grab people excited about what you’ve got to say.
    In my opinion, it could bring your website a little livelier.

  2. Hello there I am so glad I found your site,
    I really found you by accident, while I was searching on Bing for something else,
    Regardless I am here now and would just like to say thank
    you for a marvelous post and a all round
    thrilling blog (I also love the theme/design), I don’t have time
    to read it all at the moment but I have bookmarked it and also included your
    RSS feeds, so when I have time I will be back to read a
    lot more, Please do keep up the awesome b.

  3. Very good article. I definitely appreciate this website.
    Continue the good work!

  4. I think this is among the so much important information for
    me. And i’m glad studying your article. But should statement
    on some common issues, The web site style is wonderful, the articles is really great :
    D. Excellent activity, cheers

Leave a Reply