SQL Query in SharePoint

The "FullTextSqlQuery" object's constructor requires an object that has context but don't be fooled. This context will not be the context in which your query will search on. Scopes are used for that are I will be explaining scopes a little later on.

Query Text

The "FullTextSqlQuery" object has a "QueryText" property which takes a query very similar to a generic SQL query. Here are a list of minor differences for those who are interested:

• There is only one table to search on - "Scope" and this table is suffixed by parenthesis.
• You cannot search on all columns using *. You have to specify the column names you require.
• There are no aggregate functions eg. 'Count'
• I found that "LIKE" doesn't work. I have found examples that use "LIKE" and state that it works. I myself never got the results I was expecting so I use "CONTAINS(Title, 'SharePoint').
• The comparable commands are Equals "=", Not Equals "!=", Contains "CONTAINS(ColumnName, 'Value')" and FREETEXT(*, 'value') which allows you to search on all columns. NOTE: You cannot put spaces in any of these value objects. Replace all spaces with the '+' character to search multiple words. And use the '*' character in the values as a wildcard - WILDCARDS ONLY WORK IN SP 2010!.

So here is an example of what your QueryText could look like:

SELECT Title, Description, Path FROM SCOPE() WHERE CONTAINS(Title, 'SharePoint') AND IsDocument = 1 ORDER BY Title

Query Properties

There are some properties on the "FullTextSqlQuery" object that you will need to set.

• ResultTypes. This is an enum of type Microsoft.Office.Server.Search.Query.ResultType. I've only ever used the "RelevantResults" value. I've tested the rest but I don't get any results using them so not sure what they are for.
• TrimDuplicates. As the name states.
• StartRow. Used for paging. The index of the first item to return. NOTE: If you're using the API this index starts at 0 but if you are using the webservice to call a Query the index starts at 1.
• RowLimit. Used for paging. The amount of results the query must return.

There are some more properties but I'm not sure exactly what they are used for yet.

Columns

If you want to know what columns you can select on you can see this in your Shared Services Provider. On the home page click on the "Search settings" option under "Search". The navigation menu should have changed now. Click on the "Metadata properties" item in the quick launch menu under "Queries and Results". On this screen you can see all the managed properties and their crawled property mappings. Check about my other post "Metadata Property Mapping" for more information.

Scopes

Scopes allow us to search in specific areas of sharepoint. I haven't worked extensively with this yet but from what I understand at the moment you can only create a scope inside a web application and a site collection. This means that if you have multiple web applications and multiple site collections you can make your search specific to one of multiple of these. If you don't specify a scope the search will return results from all web application and all site collections. This optomises your search greatly as the scopes index all your results. NOTE: In your for your scope to be visible from your "FullTextSqlQuery" it needs to be Shared.

Indexing

All "Managed properties" and "Scopes" are indexed by SharePoint. When a new object is added to SharePoint is will not be indexed yet and will not be returned by your search query until this has happened. A crawl needs to be started in order for any new objects or object changes to appear in your results. If you are busy testing your search you can manually start a full crawl inside your Shared Services Site. On the home page click on the "Search settings" option under "Search". The navigation menu should have changed now. Click on the "Content sources" item in the quick launch menu under "Crawling". You can start a full crawl on your content source on this page. Once the crawl is complete run your search to see if your results are being returned. If you want to deploy a custom search feature you will need to setup an incremental crawl for this content source so that it executes every so often.

Results

The results of a "FullTextSqlQuery" are returned in the form of a "ResultTableCollection" from the "Execute" method. In order to get your results from this collection you need to the index the collection with your ResultType like so "fullTextSqlQuery.Execute()[ResultType.RelevantResults];".
The ResultTable is not very straight forward and I struggled to get my results out. First you need to call the Read() function inside a while loop and then use a for loop to get the index of each result item. You then use two methods on the table: "GetName()" and "GetValue()". Each of these methods take an index parameter. I then need to compare the name to the name im looking for and set the value for the object. Here's a code sample:

while (resultTable.Read())
{
  for (int i = 0; i < resultTable.FieldCount; i++)
  {
      string name = resultTable.GetName(i);
      object objValue = resultTable.GetValue(i);
      string value = string.Empty;
      if (objValue != null)
      {
        value = objValue.ToString();
      }

      if (name == SearchProperties.Title.ToString().ToUpper(CultureInfo.CurrentCulture))
      {
        sr.Title = value;
      }                       
   }
}

Bugs
Yup, like most complex software out there, even Microsoft software, there are bugs. So far I have picked up two.

1. Sort by Author does not return desired results. The Author managed property is linked to a Mail and Office category field. For some reason if you sort by this property it adds a null row after every result so your count will double. As far as I'm aware this is a bug in 2007 and 2010.

2. ModifedBy and CreatedBy are empty. Although these fields exist inside the SPItem, you will not be able to retrieve them using the FullTextSqlQuery object. It seems that these fields are not indexable and they will always return null.

Resources
1. http://community.bamboosolutions.com/blogs/bambooteamblog/archive/2009/04/24/wss-custom-search.aspx
2. http://blogs.msdn.com/b/varun_malhotra/archive/2008/08/16/moss-search-with-order-by-clause-doesn-t-return-all-results.aspx

Office.Interop Tip of the week

I needed to get all content controls inside a word document and add an OnEnter and OnExit eventhandler however when you loop through the Controls in a document you are only allowed to access them as Host Controls and not Host Items so the event handlers are not accessible. Then I found out that a document contains a "ContentControlOnEnter" and "ContentControlOnExit" event handler!

Update 17 May 2010

Have just found out that you should never assign this event to a Microsoft.Office.Interop.Word.Document but rather a Microsoft.Office.Tools.Word.Document. I was using the first document and my events sometimes get removed. To get the latter document, you can use this method Microsoft.Office.Tools.Word.Document.GetVstoObject();

Using the Office.Interop to return a list of Macros in an Word 2007 template

I struggled for hours to find a code example for this but eventually stumbled across this page: http://support.microsoft.com/kb/262961 which helped me a lot!!! I converted it to C# and modified it slightly. I have not yet fully completed my task but here is a code snippet for now - I will explain the theory behind macros next. Hope this helps someone so long:

You will need to reference the following to get this code working:

using System.Collections.Generic;
using Microsoft.Office.Interop.Word;
using Microsoft.Vbe.Interop;

public static List<string> GetMacroList(Document document)
{
 List<string> macroList = new List<string>();
 vbext_ProcKind prockind = vbext_ProcKind.vbext_pk_Proc;                                   
 string curMacro = string.Empty;
 string newMacro = string.Empty;
 foreach (VBProject pj in document.Application.VBE.VBProjects)
 {
  try
  {
   if (pj.Protection == vbext_ProjectProtection.vbext_pp_none)
   {
    foreach (VBComponent vbcomp in pj.VBComponents)
    {
     if (vbcomp.CodeModule.CountOfLines > 0)
     {
      for (int i = 1; i < vbcomp.CodeModule.CountOfLines - 1; i++)
      {
       newMacro = vbcomp.CodeModule.get_ProcOfLine(i, out prockind);
       if (curMacro != newMacro)
       {
        curMacro = newMacro;
        macroList.Add(curMacro);
       }
      }
     }
    }
   }
   else
   {
    //do not have permission - tell the user
   }
  }
  catch
  {
   //catch something...
  }
 }
 return macroList;
}

Issue with Word Add-Ins in VS 2010

If you create a Word Add-In in Framework 3.5 using VS 2010 it will not compile from the start if you do not have Visual Studio 2008 SP1 installed. Hope this helps someone - it bugged me for a while

Word Research Pane Customisation Part 1

So you want to customise your research pane in word 2007? Here's how you do it:

1. Create a webservice
   
   Your webservice should contain two web methods: "Registration" and "Query". They each accept and return a string. Please note that you will need to register the "Microsoft.Search" namespace in your webservice in order for this to work with word. This took a few hours of my time trying to figure it out :/
   
   Here is an example of the xml that your "Registration" method should return:
   
   <?xml version="1.0" encoding="utf-8"?>
   <ProviderUpdate xmlns="urn:Microsoft.Search.Registration.Response">
     <Status>SUCCESS</Status>
     <Providers>
       <Provider>
         <Message>Welcome to customising your research service</Message>
         <Id>{C37EE888-D74E-47e5-B113-BA613D87F0B2}</Id>
         <Name>My First Research Service</Name>
         <QueryPath>http://localhost:49375/Service1.asmx/Registration</QueryPath>
         <RegistrationPath>http://localhost:49375/Service1.asmx/Registration</RegistrationPath>
         <Type>SOAP</Type>
         <Services>
           <Service>
             <Id>{8DD063CA-94FC-4514-8D83-3B36B12432BE}</Id>
             <Name>My First Research Service</Name>
             <Description>My First Research Service</Description>
             <Copyright>(C) 2008</Copyright>
             <Display>On</Display>
             <Category>RESEARCH_GENERAL</Category>
           </Service>
         </Services>
       </Provider>
     </Providers>
   </ProviderUpdate>
   
   And here is an example of the xml that your "Query" method should return:
   
   <?xml version="1.0"?>
   <ResponsePacket providerRevision="1" xmlns="urn:Microsoft.Search.Response">
     <Response domain="{8DD063CA-94FC-4514-8D83-3B36B12432BE}">
       <QueryID>{AAF43E6E-FF3D-4F20-BE8C-7ED18546E7DB}</QueryID>
       <Range>
         <Results>
           <Document xmlns="urn:Microsoft.Search.Response.Document">
             <Title>Amy Kins Gardiner</Title>
             <DisplayUrl>http://amykinsgardiner.blogspot.com/</DisplayUrl>
             <Description>a blogspot from a kinners</Description>
           </Document>
           <Content xmlns="urn:Microsoft.Search.Response.Content">
             <Heading>
               <Text>Some Test Actions</Text>
               <Actions>
                 <Text>Do Action</Text>
                 <Insert />
                 <Copy />
                 <Data>Some Data</Data>
               </Actions>
             </Heading>
           </Content>
         </Results>
       </Range>
       <Status>SUCCESS</Status>
     </Response>
   </ResponsePacket>
2.  Register your new webservice with word
   
   So once you have this webservice up and running and returning the correct xml data you can register your service with word. You can do this by going to the "Review" tab and clicking on the "Research" button. At the bottom of the Research Pane which has now just popped up there is a link called "Research Options". Click on this link and it should popup a dialog which has another button to "Add Services". Click on this button and you will be able to specify the url for your new webservice. It should then register your service and you will now be able to choose "My First Research Service" in the drop down list under the search box. You can now search from your service. Enjoy :)
    

So I was asked to check out some extra things around this research service and this is what I have discovered:

1. Q: Can I use the Actions to insert word documents into the current document that is open?
   A: I think you can :) I saw two ways that could work. Firstly you could create VB smart tag COM object and reference these objects in your XML. But I hear the words COM and VB and my hair stands up sooo I didn't look any further there. Second option is to create Smart Tags inside a word add-in. Now this looks a lot cleaner and could be quite fun but we are trying to get around not installing add ons so until we decide to go this route I don't have anymore information on how to do this as of yet.
2. Q: Could I send the contents of a word document through the XML to allow it to be inserted?
   A: Yes you could but there are some limitations that will stop you from wanting to do this. The limitation is that only plain text can be inserted. So no formatting, not even carriage returns can be send through :/ And from what I can tell there is a 65 000 character limitation - but this might not be accurate.

Happy research servicing :)

Hello World

if (canRead)
{
    Console.WriteLine("Yellow is safe");
}