Using SQL Full-Text Search in SharePoint

4.20 / 5 5
1 / 5
2 / 5
3 / 5
4 / 5
5 / 5

5 votes, 4.20 avg. rating (84% score)

Introduction

Along with keyword queries, you may also create solutions that use Enterprise SQL Search Query syntax. Enterprise SQL Search Query syntax is a full query language that gives you significant control over the executed query.
In this post we will see how to use SQL Full-Text Search. In my earlier post you can also check using Keyword Search in SharePoint
The post includes

Enterprise SQL Search Query
Using FullTextSqlQuery class in getting search results

Skill Level – Medium

Full-Text Search

The following code shows a query that returns the documents that were added to the SharePoint portal within the past week.


SELECT url, title, author FROM Scope
WHERE "scope" = 'All Sites'
AND isDocument=1
AND write >DATEADD(Day,-7,GetGMTDate)

Enterprise SQL Search Query syntax is straightforward. The SELECT part is used to designate the columns to return from the query. The FROM part always contains the Scope statement, refined by the WHERE part, which specifies the exact scope to search. The WHERE part also contains the filters to apply. The WHERE part supports arithmetic operators, Boolean operators, and more specific full-text predicates, such as FREETEXT and CONTAINS. FREETEXT matches the meanings of phrases against fields, while CONTAINS does a straight match against the text in a field.

The FullTextSqlQuery class is used to create and issue queries based on the Enterprise SQL Search Query syntax.
Using the FullTextSqlQuery class

SearchServiceApplicationProxy proxy =
  (SearchServiceApplicationProxy)SearchServiceApplicationProxy.GetProxy(
SPServiceContext.GetContext(SPContext.Current.Site));
FullTextSqlQuery queryObject = new FullTextSqlQuery(proxy);
queryObject.ResultsProvider = SearchProvider.Default;
queryObject.ResultTypes = ResultType.RelevantResults;
queryObject.EnableStemming = true;
queryObject.TrimDuplicates = true;
queryObject.QueryText = queryString;
ResultTableCollection results = queryObject.Execute;

Conclusion

Using the FullTextQuery class is similar to using the KeywordQuery class. The query is set in the QueryText property, and additional properties are available to refine the search. Like the KeywordQuery class, the FullTextQuery class returns results as a ResultTableCollection.

Email this page