Querying the CSharpQuery Index

Now for the easy part – Using the index. Beleve it or not, the hard part was creating your index. Once it's created we just need to know the name of the index to query, the path to that index, the culture information to use, and the search criteria.

A typical full text query for searching for track information could look something like this:
public static List<Track> QuickSearch(...) {
    
    FreeTextQuery.DatabasePath = csharpQueryIndexLocation; // passed in as param or set by class

    // We need a valid CultureInfo class so CSharpQuery knows what Index to use
    CultureInfo ci = CultureInfo.InvariantCulture : 

   // Perform the CSharpQuery!
    List<QueryResult> trackResults = // Returns a List of QueryResult
        FreeTextQuery.SearchFreeTextQuery(
             "QuickSearch",    // The Index Name 
             ci,               // The culture info
             searchCriteria);  // The search term passed into the function

    // We want to pass these results to SQL Server so we're going to format the results as XML
    string trackIDxml = FormatResultsAsXml(trackResults);

    // Get the data from SQL Server (using LINQ to SQL)
    int? nRowCount = 0;
    var searchResults = Con(con).QuickSearch2(
        trackIDxml,                      // The XML I created from my CSharpQuery
        pageSize,                        // Support for custom paging
        pageNumber, 
        (int)sort,                          // How would you like the results formatted?  Implemented in T-SQL
        ref nRowCount);               // Returns the number of rows found
    
    return searchResults;
}

The code above simply calls FreeTextQuery.DatabasePath to set the location of the text index. You only need to set this path once since it is static but I set it for every call, just in case. Next I call FreeTextQuery.SearchFreeTextQuery to perform the search. This returns a list of QueryResult which gives you back the Key that you specified when creating the index, the rank (presorted), and the locations of the words in the original Value that it matched to. This is very handy if you wanted to do something like highlight search terms especially if you want to also highlight words that matched via the thesaurus.

After I get my results I call a function called FormatResutlsAsXml so I can get an XML string to send to SQL server and join these keys to the actual track information. My implementation of FormatResultsAsXml actually uses a StringBuilder because I found this to be a little quicker at creating an XML string.
private static string FormatResultsAsXml(List<QueryResult> phraseResults) {
    if (phraseResults == null || phraseResults.Count == 0)
        return null;
    StringBuilder sb = new StringBuilder();
    sb.AppendLine("<root>");
    foreach (QueryResult r in phraseResults) {
        sb.AppendLine("<result>");
        sb.AppendLine("<key>" + r.Key + "</key>");
        sb.AppendLine("<rank>" + r.Rank + "</rank>");
        sb.AppendLine("</result>");
    }
    sb.AppendLine("</root>");
    return sb.ToString();
}


Of course that part is optional but leads well into the how to integrate CSharpQuery with SQL Server.

Last edited Oct 16, 2009 at 6:15 PM by NathanZaugg, version 1

Comments

No comments yet.