MS SQL and Searching MCMS with SharePoint - Building the Microsoft SQL Full-Text Query

(Page 2 of 4 )

SPS's search process uses full-text indexes and queries for fast keyword lookups in order to provide timely responses to the end user. Full-text queries are very similar to regular T-SQL queries in Microsoft SQL Server, but you have additional functions, or predicates, that you can use to give your query more power. One of these predicates that is useful when searching SharePoint indexes is FREETEXT. FREETEXT takes a list of words separated by spaces, determines which words and phrases are significant, and uses that information to build an internal query to search the targeted data in an efficient manner.

First, you need to be aware of the various fields, or properties, available to you in your query. SharePoint provides a list with this information in the Site Settings administration page of your portal.

1. Open a new instance of Internet Explorer and navigate to our portal: http://portal.tropicalgreen.net.
   
2. Click on the Site Settings link in the upper right. 
   
3.  Under the Search Settings and Indexed Content section, click on the Manage properties from crawled documents link.

For each document crawled, the Manage Properties of Crawled Content page lists all properties that SharePoint could potentially contain indexed data on. For our purposes, we're only going to look at the two fields below, as they contain information for our search results page:

• DAV:href
  
• DAV:getlastmodified

  

Notice that some of the fields listed under the urn:schemas.microsoft.com:htmlinfo:
metainfo group are the same fields as we added to our template META tags using the
SearchMetaTagGenerator user control from the MCMS Connector.

The other things we'll need are the name of the search scope we created, the name of the content index, and our keywords. Once we have all that information, it makes most sense to construct the MSSQLFT query in its own method for readability. We'll call this method BuildMssqlftQuery() and pass it a string containing our search keywords and the search scope to query. Add the following method at the end of the SearchResults.aspx.cs page:

  /// <summary>
  /// Builds the Microsoft SQL FullText query based on the parms.
  /// </summary>
  /// <param name="keywords">Keywords submitted for search.</param>
  /// <param name="searchScope">SPS Search scope to filter.</param>
  /// <returns>String of the MSSQLFT query.</returns>
  private string BuildMSsqlftQuery(string keywords, string searchScope)
  {
    StringBuilder mssqlftQuery = new StringBuilder();
    ArrayList whereClause = new ArrayList();

    #region FILTER: keywords
    // list of keywords to include
    if (keywords != null && keywords.Length >0)
    {
      // add the keyword filter, use a calculated weighted field
      // just as SPS does
      whereClause.Add(string.Format(" {0} {1}",
        "WITH (\"DAV:contentclass\":0,"
      + "\"urn:schemas.microsoft.com: fulltextqueryinfo:description\":0,"
      + "\ urn:schemas.microsoft.com: fulltextqueryinfo:sourcegroup\":0,"
      + "\"urn:schemas.microsoft.com: fulltextqueryinfo:cataloggroup\":0,"
      + "\"urn:schemas-microsoft-com:office:office#Keywords\":1.0,"
      + "\"urn:schemas-microsoft-com:office:office#Title\":0.9,"
      + "\"DAV:displayname\":0.9,"
      + "\"urn:schemas-microsoft-com:publishing:Category\":0.8,"
      + "\"urn:schemas-microsoft-com:office:office#Subject\":0.8,"
      + "\"urn:schemas-microsoft-com:office:office#Author\":0.7,"
      + "\"urn:schemas-microsoft-com:office:office#Description\":0.5,"
      + "\"urn:schemas-microsoft-com:sharepoint:portal:profile:"
      + "PreferredName\ ":0.2,contents:0.1,*:0.05) "
      + "AS #WeightedProps",
        "FREETEXT(#WeightedProps, '" +keywords.ToString().Trim() +"')")
        ); 
    }
    #endregion

    #region FILTER: sps source group
    // filter source group
    whereClause.Add(string.Format(" {0}",
      "(\"urn:schemas.microsoft.com: fulltextqueryinfo:Sourcegroup\" = '"
      + searchScope +"')"));
    #endregion

    //build search query
    mssqlftQuery.Append("SELECT ");
    mssqlftQuery.Append("\"DAV:href\",");
    mssqlftQuery.Append("\"DAV:getlastmodified\"");
    mssqlftQuery.Append(" FROM Non_Portal_Content..SCOPE()");
    mssqlftQuery.Append(" WHERE ");
    int i=0;
    foreach (string s in whereClause)
    {
      if (i > 0)
        mssqlftQuery.Append(" AND ");
      mssqlftQuery.Append(s);
      i++;
    }
    return mssqlftQuery.ToString();
  }

Notice we added a calculated field, which we used to apply certain weight to some fields. This is how SharePoint actually executes its own search. You could configure the property weighting to give more emphasis to specific properties in your query. For example, you may want to give more weight to the title of the page, or to the keywords stored in the HTML META tags, than to the contents of the page.

Now that we have this method, let's move on to creating the MSQuery string. We'll use this method in the construction of our MSQuery string.

Next: Building the MSQuery XML String >>
 

More Windows Scripting Articles
More By PACKT Publishing

Comments On: MS SQL and Searching MCMS with SharePoint

· This article is an excerpt from the book "Advanced Microsoft Content Management...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter five of the book Advanced Microsoft Content Management Server Development, written by Lim Mei Ying et al. (PACKT, 2005; ISBN: 1904811531). Check it out today at your favorite bookstore. Buy this book now.