WSS Custom Search

Windows SharePoint Server (WSS) lacks some search functionalities which are available in MOSS but these differences are among the reasons why WSS is free and MOSS is a more pricey product. There are, however, some ways that WSS search can be improved upon to provide some of the same functionalities as MOSS. The FullTextSqlQuery represents the search query and can be used to provide a custom search feature. This class uses the search SQL syntax and is defined in the Microsoft.SharePoint.Search.Query namespace.

In WSS, searches are only possible in the ‘All site’ scope using scope() in the from clause, but not on the document library scope (which is a main feature of MOSS). In MOSS, even new scopes can be defined to give users expanded search features. These searches can be possible in WSS by defining the FullTextSqlQuery properties in the proper way. The FulltextSqlQuery can be defined in this manner:

using (SPSite site = new SPSite(url))

  {

   using (FullTextSqlQuery query = new FullTextSqlQuery(site))

      {

  ResultTableCollection results = null;

  results = query.Execute();

  ResultTable relevantResults=results[ResultType.RelevantResults]

      }         

   }

The KeywordQuery is another class which can be used to perform custom search queries. It is better to use this class when you only need to use keyword syntax because keyword syntax can pass the search terms directly to the search component, and do not need to parse through the search terms to build the query. The KeywordSyntax query is simple when compared to FullTextSqlQuery which can construct complex groupings of included and excluded terms.  

The Microsoft.SharePoint.Search.Query only supports the built-in metadata columns for search by design. The column names used in the FullTextSqlQuery should be valid managed proprieties. Some of the important managed properties are mentioned in the document attached with this blog named properties.txt. The query elements which are supported only with SQL search syntax using the FullTextSqlQuery class are:

•  FREETEXT() – Searches for the search term everywhere, even in the content of the documents if specified like this FREETEXT(*, '" + searchString + "')) where * denotes search in all managed proprieties. Instead of “*” you can mention the managed property name in which you want to search. The FREETEXT() is better suited to finding documents containing combinations of the search words spread throughout the column.
• CONTAINS() – Is better suited to search for the exact search term mentioned. You can also use  the wildcard character at the end of the word or phrase, e.g., CONTAINS(Title, ‘comp*) where “Title” is the column you need to search. If the column name is not specified, the Contents Column, which is the body of the document is specified.
• LIKE – Like can be used to search if the term exits at the beginning, end or even in between, e.g., Filename like '%" + searchString + "%'
• ORDER BY – Is used to place the search results in some particular order, e.g., ORDER By RANK DESC will order the search results based on RANK.

The FullTextSqlQuery has the property called “QueryText” which accepts SQL Syntax query as shown below:

string queryText = "select Title, Path, Filename, SiteName, ContentType";

queryText += "from scope() where ContentType != 'Folder'";

queryText += " AND (Filename like '%" + searchString + "%'";

queryText += " OR (Title like '%" + searchString + "%'";

queryText += " OR FREETEXT(*, '" + searchString + "'))";

queryText += " AND SITE='" + webUrl + "'";

queryText += " AND SiteTitle='" + webTitle + "'";

queryText += " AND Path like '%" + rootUrl + "%'";

queryText += " ORDER By RANK DESC";

query.QueryText = queryText;

The query above searches for documents matching the search term mentioned in the “searchstring” for the document library in a site where the rootUrl would be the URL of the document library you are searching, and webUrl would be the URL of the site.

There are some other properties of the FullTextSqlQuery which can be useful to enhance the search results:

• query.TrimDuplicates = true; - Avoid duplicate results
• query.EnableStemming = true; - Enable stemming
• query.IgnoreAllNoiseQuery = true; - Ignore all noisy search results
• query.KeywordInclusion = KeywordInclusion.AnyKeyword; - You can specify “AllKeyword” or “AnyKeyword” depending on the search.
• query.ResultTypes = ResultType.RelevantResults;
• query.AuthenticationType=QueryAuthenticationType.PluggableAuthenticatedQuery; - Here you can specify the authentication level.

A better way to define the authentication type is to check the SPSecuirty authentication mode and then assign the proper authentication to run the query. The code to check the SPSecurity authentication is:

if (SPSecurity.AuthenticationMode !=System.Web.Configuration. AuthenticationMode.Windows)

query.AuthenticationType=QueryAuthenticationType.PluggableAuthenticatedQuery;

else

query.AuthenticationType = QueryAuthenticationType.NtAuthenticatedQuery;

In the above code, the relevantResults contain the final result of the FullTextSqlQuery search, which can be filtered further to get the desired results. There could be an issue with the search if the search term contains some words which exist in the SharePoint internal files. In such cases, the search will try to access the files the user doesn’t have access to read, and will then throw an exception. Even if we try to catch that exception, the search results will only contain the results up to the point where the exception occurred. This happens when we use FREETEXT(*, '" + searchString + "')) to search on a word and the search tries to read the SharePoint internal files as well; that’s the point where it throws the exception. To avoid this issue, the above code can be replaced with:

using (ResultTable relevantResults = results[ResultType.RelevantResults])

  {

IDataReader reader = (IDataReader)relevantResults;

searchDataTable= ConvertDataReaderToDataTable(reader);

  }

This code will read the relevant result to the reader and then convert it in to the DataTable for further use. The ConvertDataReaderToDataTable is the function which converts the DataReader back to the datatable. By doing it this way, the exception can be avoided.