Content Queries Using SharePoint Enterprise Search API

February 15 2010 20 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% [?]

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

  1. [...] Content Queries Using SharePoint Enterprise Search API (SharePoint Blues)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: [...]

  2. Derek says:

    Hi.

    I’m using a fulltextsqlquery to successfully return results, but one thing I require is similar functionality to the normal keyword search. I’m indexing the HTML of my sharepoint pages so that the user can search for text on any rendered page.

    I’d like to return results where you’d find values in the HitHighlightedSummary, but using the wildcard functionality of the fulltextsqlquery syntax.

    Which column in the results can I use in my “like” statement to get this to work? And is this even possible?

    …AND ???? LIKE ‘%ice%’

  3. Aapo Laakkonen says:

    Derek,

    You can use CONTAINS(‘*ice*’) or CONTAINS(‘*’, ‘*ice*’) or CONTAINS(‘ALL’, ‘*ice*’), I think, but I have not tried this. Please let us know if it works.

  4. Aapo Laakkonen says:

    Derek,

    Oh, I didn’t answer directly to your question, have you checked these:
    http://www.windows-tech.info/6/538411d9517c21e2.php

  5. Faith says:

    I would like to search content on my sharepoint sites from another site (non-sharepoint) and process the result to have control on how it gets rendered on my site. Does sharepint provide API to do that? If so, would anybody give me any clues? Thanks!!!

  6. [...] scope of custom queries. We’ve had our CrossListQueryCache’s, SPSiteDataQueries, the search API along for some time now and with these libraries and techniques it hasn’t been a hard task to [...]

  7. Lou says:

    queryText.Append(“SELECT AccountName “);
    queryText.Append(“FROM Scope() “);
    queryText.Append(“WHERE \”scope\” = ‘People’ “);
    queryText.Append(“AND \”Roles\” = ‘” + role + “‘ “);

    returns the account name for me but the following does not return the roles for the given account name!

    queryText.Append(“SELECT AccountName, Roles “);
    queryText.Append(“FROM Scope() “);
    queryText.Append(“WHERE \”scope\” = ‘People’ “);
    queryText.Append(“AND \”AccountName\” = ‘” + user + “‘ “);

    Seems the Roles is empty! Any idea? The Roles is a custom property that gets its value from a Managed Metadata.

  8. Janaiah says:

    Hi, This is very good information. I have tried your code and its working perfect for Farm Administrator but when try with normal users (Site Owner, Contributor etc) not returning results. Could you help me in this.
    I would like to get site collections for which current user have access.

    Thanks,
    Janaiah

  9. Quality content is the main to attract the users to pay
    a visit the website, that’s what this web site is providing.

  10. Samuels (Eminem) “Nothin’ On You” — Philip Lawrence,
    Ari Levine, Bruno Mars & Bobby Simmons Jr. –LINK REMOVED
    –>Grammy Nominations 2011<. Juni, als Bombenanschläge in Kathmandu und anderen Städten Nepals acht Tote und 22 Verletzte forderten.

  11. I comment each time I like a post oon a website or if I have something
    to valuable to contribute to the conversation. It is a esult of the fire displayed iin the article I looked at.

    And on this post Content Querie Using SharePoint Enterprise Search API
    | SharePoint Blues. I was actually moved enough to drop a leave a responsea
    response ;-) I actually ddo have a few quhestions for you if you
    tend not too mind. Is it just mee or does it appear like a
    ffew of the responses come across like coming from brain dead visitors?

    :-P And, if you are writing on othher sites, I’d like to keep up with antthing new you have to post.
    Could you list all of all your shared pages likke your linkedin profile,
    Facebook page or twitter feed?

  12. Rose essence is not irritating and is suitable for my skin.
    Similarly, the skin of patients with severe acne. Some women with mild acne,
    but it may take 2-3weeks for your skin, so ease into a regimen that
    includes the two to avoid redness and peeling. What makes
    human it even more parched. If you run out of nail polish remover but I don’t like scented aftershave balms as I do in terms of diet.

  13. Ollie says:

    Please let me know if you’re looking for a article writer for your blog.
    You have some really great articles and I believe I would be a good asset.
    If you ever want to take some of the load off, I’d love to write some material for your blog in exchange
    for a link back to mine. Please send me an e-mail if interested.

    Many thanks!

  14. Hey There. I found your blog using msn. This is a really well written article.
    I’ll make sure to bookmark it and come back to read
    more of your useful info. Thanks for the post. I will certainly comeback.

  15. Heya! I realize this is somewhat off-topic however I needed to ask.

    Does running a well-established website like yours require a lot of work?
    I’m completely new to writing a blog however I do write in my diary every day.
    I’d like to start a blog so I will be able to share my personal experience and thoughts online.
    Please let me know if you have any recommendations or tips for brand
    new aspiring blog owners. Thankyou!

  16. Aundrea says:

    I read a lot of interesting articles here. Probably you spend a lot of time
    writing, i know how to save you a lot of work, there is an online tool that creates readable,
    SEO friendly posts in minutes, just search in google – laranitas free content source

  17. I’m gone to tell my little brother, that he should also visit this webpage on regular basis to get updated from newest news update.

  18. WOW just what I was looking for. Came here by searching for advice

  19. Undeniably believe that which you stated. Your favorite
    reason seemed to be on the net the easiest thing to be
    aware of. I say to you, I definitely get irked while people think about worries that they just don’t know about.
    You managed to hit the nail upon the top as well as defined out the whole thing without having side-effects ,
    people can take a signal. Will probably be back to get more.
    Thanks

  20. Greetings other online sites performs good in my situation yet
    your site is loading slowly which had taken approximately a few minutes to be able to load,
    I am not sure if it’s my problem perhaps website problems.
    Nevertheless, I have to appreciate you for adding excellent article.
    Perhaps it has become helpful to many individuals
    . This is definitely incredibly good what you actually have
    concluded and wish to discover even more interesting posts from you.
    I already have your site saved to my bookmarks to see blog you
    publish.

Leave a Reply