How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

A while back, I posted a how to on using the KeywordQuery class and it seemed to get pretty good response, so I figured I would post a follow up on how to use the FullTextSqlQuery class today.  It's actually pretty similar to using the KeywordQuery class but of course the syntax is different.  I am not going to give you a full run down of all the syntax, but I will provide enough to get you going. 

There are a number of reason to use the FullTextSqlQuery class.  Using a SQL Syntax query unlocks the full power of Enterprise Search that really isn't there out of the box.  With SQL Syntax, you can do wildcard searches, and make use of the powerful CONTAINS and FREETEXT predicates.  With the CONTAINS predicate, you can use the FORMSOF term to do inflectional (i.e.: go matches going, gone, went, etc.) or thesaurus searches.  Again, check the SDK as there is plenty of documentation about how to use all sorts of predicates.  Before, we look at all the code lets look at a couple of things on the query.

SELECT Title, Path, Color, Size, Quantity, Rank, Description, Size FROM SCOPE() WHERE "scope" = 'My Scope' AND CONTAINS(Description, 'ant*')

In a lot of ways this looks like a T-SQL query.  Enterprise Search queries however always query from SCOPE().  To specify the actual scope (or scopes) you want to search on you use the where clause.  I have always found the syntax weird here because the word scope has to be in quotes and what you are comparing it to always has to be in single quotes.  In this case I am using the CONTAINS predicate to do a wildcard search.  It should return anything that has a word starting with ant in the description column (i.e.: ant, ants, anthony, antler, etc.).

Here is the complete code example.

using (SPSite siteCollection = new SPSite(siteCollectionUrl))

{

    // create a new FullTextSqlQuery class - use property intializers to set query

    FullTextSqlQuery myQuery = new FullTextSqlQuery(siteCollection)

    {

        QueryText = "SELECT Title, Path, Color, Size, Quantity, Description, Rank, Size FROM SCOPE() WHERE \"scope\" = 'My Scope' AND CONTAINS(Color, 'ant*')",

        ResultTypes = ResultType.RelevantResults

    };

 

    // execute the query and load the results into a datatable

    ResultTableCollection queryResults = myQuery.Execute();

    ResultTable queryResultsTable = queryResults[ResultType.RelevantResults];

    DataTable queryDataTable = new DataTable();

    queryDataTable.Load(queryResultsTable, LoadOption.OverwriteChanges);

}