Researching in Microsoft Office

Research?? In Microsoft Office?? What are we talking about?? Well, what it means is searching for some information using a set of pluggable services. These services are called research services and the name research gives the functionality a kind of cool feeling!! Bascially, what this functionality allows the users to do is to query a set of services for some peice of information and return data about that piece, which can then be used in the appropriate Office application. For example, consider that you are writing a letter to one of your customers using Microsoft Word. As you keep typing your letter, you realize that you need some information about your customer (say his consumption volume in the last year and his payment patterns) using which you wanted to write some specific text in your document. What would you do today?? You have two options:
  • Your organization might be running some ERP scale application that holds information about all your customers. Thus, you will have to switch out of your document and then log onto the ERP application and then provide some search criteria that will bring up the necessary customer details. Note that the application may not provide all the information that you need in one functionality. Thus, you might have to traverse multiple screens to get the information and then note them down or paste them onto the Word document as you keep reading it.
  • Your document might have a custom VBA script developed by your IT department that provides some custom screen for you to enter query information about the customer using which it queries the database and then shows you some information back in the screen or directly pastes it onto the Word document using the Word object model. Although this model is much user friendly than the earlier model, it has one drawback. Since the custom script has to connect to a database of some kind, your computer has to have the necessary drivers installed so that the connection can be made. Another major drawback is that what if you are working from your home and you are using a dial-up connection?? The database connection may not work!! Thus, you may have a solution that is partially useful, but not satisfying all its needs.

Having seen the two possible solutions that you have, imagine for a moment if you are able to access some screen in Word in which you can provide information about the customer (say his customer ID) and the screen then contacts a web service (which can be anywhere) which then connects to some database, fetches the information that you want and then shows in a nice format in the Word screen!! Would'nt that be great!! Also, imaging if from that screen you can select some pieces of information which you can directly paste onto your document without resourting to ^C and ^V!! This would be a great time saver since for one, you are not leaving the Word application and second, the information that is fetched back can be used in your application (Word in this case) with ease (there is no macro that has to do some fancy Word object model programming)!! If you think this feature is great and can provide great value added services for you, then, welcome to the Research Pane in Microsoft Office and the Research Services that enable this feature!

In this article, we will see how to use the Research Pane to fetch information based on some input criteria. We will develop a sample using the pubs database that will allow a user to query for authors and their publications by providing the author id as the query criteria. We will use Microsoft Word to demonstrate this feature, but the concept is the same for other Office applications too.

Where is the Research Pane??

To access the Research Pane in Microsoft Word, use Tools > Research as shown in the following figure:

Note that you can also access this functionality by using ALT + Click. This will open a task pane (called the Research Pane) in the Word document title Research. Earlier you could search for a word or phrase using the thesaurus or the dictionary. Microsoft Word itself now uses the Research services to provide these functionalities. When you now search for a word, you will see the result for the same displayed in the Research Pane along with options to search other sites and the internet. This is just a sampling of the features that you can have with the Research Pane.

How to program the Research Pane??

If you want to enable your own services through the Research Pane (for example, the customer service that we talked about earlier), there are many ways to go about doing it, but the simplest and the most easiest is to use a web service. You can create a web service that provides a set of functionality and then register the same with the research services. Once registered, you would be able to search against that service from any Office application.

So how does the Office application know which method to call when you want to search against it?? Well, the answer lies in the fact that, if you want to use the research services, your web service needs to adhere to a pre-defined Office schema. This means that all inputs to your web service and all outputs from your web service have to abide by that schema. In addition, the Office application will always call standard method names that you will have to implement in the web service. For the research services, these methods are called Registration and Query. The following figure shows how the process works:

 
Here are the various steps and operations that take place when you use the Research Pane:
  • When you first register a service with the Office application, say Microsoft Word, Word automatically calls the Registration method in the web service with a standard XML parameter.
  • The method is expected to response in an XML schema that contains information about the various services that are exposed by the web service and what the URL is for those services.
  • Once this information is received, Word registers the service in its Research list.
  • Now, the user can type in a query that the service can understand (for example, a customer id) and then instruct Word is search against the registered service.
  • At this point, Word calls the Query method passing in the users selection and the method can then parse the input and fetch the appropriate information (either from the database or any other source).
  • Once the information to be sent has been received, the Query method will package it into a schema (which closely resembles HTML) and then sends it back to Word.
  • Word will now display this information in the Research Pane.
That's all there is to it!! Its quite easy to program for and is interesting too. The schema that you have to adhere to, is published along with the Research Services SDK, which you can download here. In the next section, we will actually see how all this works.
 
Creating the Authors Research Service
 
Using the concepts above, let us now see how to develop a research service that will allow users to search for author details from the pubs database. The research service that we will develop will accept the author id as a parameter and then return the author information along with all the publications that have been written by the author. We will develop the research service in steps so that you can understand the flow of the application.

As the first step, let us create a web service that will host the research service. Fire up Visual Studio .NET 2003 and then choose File > New > Project > Visual Basic Projects > ASP.NET Web Service. We will be using Visual Basic .NET to develop the sample code. If you are comfortable in another language (like C#) feel free to use the appropriate project option. You can easily conver this code into the language of your choice. Name the project as AuthorResearchServices and the default web service file as Research.asmx.

Now, import the following namespaces:


Imports System.Web.Services
Imports System.Xml
Imports System.IO
Imports System.Text
Imports System.Web.Services.Protocols
Imports System.Configuration
 
Some of the namespaces are part of the web service creation, but others like System.Xml are things that we will need for some work that we are going to do. Having imported the namespaces, let us now write the Registration method. Here is the source:

' ******************************************************************************************
' This method will be called by Office 2003 when the registration of the research service
' happens. This implementation just loads a standard response file from the local
' application directory and formulates the response.
' ******************************************************************************************
<WebMethod()> Public Function Registration(ByVal registrationxml As String) As String
    Dim xdResponse As New XmlDocument

    Try
        ' Open the standard XML file and send the response
        xdResponse.Load(Server.MapPath(".") & "\RegistrationResponse.xml")
        Return xdResponse.InnerXml.ToString
    Catch e As Exception
        'Something failed, so we don't want to register the service with Office
        Return ""
    End Try
End Function
 
I've highlighted portions of the code that we need to note. The first is the name of the method itself. It should be called Registration. This method takes as parameter a string and returns a string. Note that although Word uses XML schemas to send and receive information, they are passed as strings. Within the method, we load information from a static file called RegistrationResponse.xml. This XML file contains the schema in which the response has to be sent back. It is a standard practice to use a file that contains the necessary information. Let us now see the contents of this file:

<?xml version="1.0" encoding="utf-8"?>
<ProviderUpdate xmlns="urn:Microsoft.Search.Registration.Response">
	<Status>SUCCESS</Status>
	<Providers> 
		<Provider> 
			<Message>Congratulations. You have successfully registered into the Authors Research Service.</Message>
			<Id>{81EDD9B6-7AB9-43a2-B10B-ED78FC7784A2}</Id>
			<Name>SCT Software Solutions</Name>
			<QueryPath>http://localhost/authorsresearchservices/research.asmx</QueryPath>
			<RegistrationPath>http://localhost/authorsresearchservices/research.asmx</RegistrationPath>
			<Type>SOAP</Type>
			<Services> 
				<Service> 
					<Id>{77A67CD4-250A-4f6b-8F31-9BB6C0FDD4F9}</Id>
					<Name>Search Authors (By Author ID)</Name>
					<Description>This service is used to search for authors and their titles using the author ID.</Description>
					<Copyright>Srinivas Sampath, 2004</Copyright>
					<Display>On</Display>
					<Category>BUSINESS_GENERAL</Category>
				</Service>
			</Services>
		</Provider>
	</Providers>
</ProviderUpdate>
 
Let us now see some important elements of this XML to understand their significance. The following table provides information about the important elements. Also, note the namespace that is used for the XML. This is a standard Microsoft namespace for the research service.
 
Element Name Description
Message This element provides the informative message that the user sees when the service is registered. You can typically put copyright information here and other helpful text for the user.
Id This is a unique identifier for the provider and is a GUID. You can generate this by using the guidgen tool of .NET.
QueryPath This is the URL to which queries will be sent when the user types in a text and searches against the service. Note that you will need to change the URL to suite your setup.
RegistrationPath This is the URL that is to be contacted for registration or updates to the service. Note that you will need to change the URL to suite your setup.
Service This element indicates the service that is provided. There can be multiple elements here to indicate various types of services.
Service/Id The identification of the service. Again, this is a GUID and you can generate this by using guidgen.
Service/Name The name for the service which will be displayed when the service has been successfully registered.
 
Ok, having created the basic method, Word will now be able to register our service. Once it has registered the service, whenever the user types in a query and selects our service, the Query method is called. Let us now see how this method looks like:

' ******************************************************************************************
' This method will be called by Office 2003 when the query for the research service
' happens. We then query the authors tables for information related to the author and
' then format it. This formatted XML is then sent back to Office
' ******************************************************************************************
<WebMethod()> Public Function Query(ByVal queryXml As String) As String
    Dim requestXml As XmlDocument
    Dim nsmRequest As XmlNamespaceManager
    Dim queryTerm As String
    Dim xdResponse As New XmlDocument

    ' Load the incoming query request into an XML document
    requestXml = New XmlDocument
    requestXml.LoadXml(queryXml)

    ' ********************************************************************************
    ' UnComment this line if you want to see what Office is sending as a query
    ' requestXml.Save(Server.MapPath(".") & "\QueryOutput.xml")
    ' ********************************************************************************

    ' Add the required namespace references for querying the XML
    nsmRequest = New XmlNamespaceManager(requestXml.NameTable)
    With nsmRequest
        .AddNamespace("ns", "urn:Microsoft.Search.Query")
        .AddNamespace("sp", "urn:Microsoft.Search.Office.ServiceParameters")
        .AddNamespace("oc", "urn:Microsoft.Search.Query.Office.Context")
    End With

    ' Extract the information that the user entered in the Research box
    queryTerm = requestXml.SelectSingleNode("//ns:QueryText", nsmRequest).InnerText
    Try
        ' Get the response to be sent back to Office
        xdResponse = GetOutputString(queryTerm)
        Return xdResponse.InnerXml.ToString

    Catch e As Exception
        'Something failed, so we don't want to register the service with Office
        Return ""
    End Try
End Function
 
Again, I've highlighted code that needs an understanding. Like the registration method, the Query method also takes as input a string and outputs a string. The input and output string conforms to a predefined schema. In the method, we load the incoming string into an XML document and then search through it for a node called QueryText. To search for this node, we need to register a bunch of namespaces so that our query is correctly qualified. The XmlNamespaceManager is used for this. Once we have selected the QueryText node, we extract its contents using the SelectSingleNode method and then use it further. Finally, we send back the response XML in a string format from the method. The bulk of the formatting and fetching from the database is done by the GetOutputString method, which we will see next. In the code above, note that we have a line that is commented that saves the incoming XML string into a file called QueryOutput.xml. You can uncomment this line if you are interested in seeing what information Word is seding to this method.

' ******************************************************************************************
' This function is used to query the pubs database for author information based on a
' query term (which is the author id). The information retrieved is packaged into the
' Research Pane XML schema and attached into the Response Schema and sent back to Office
' ******************************************************************************************
Private Function GetOutputString(ByVal queryText As String) As XmlDocument
    Dim oDocument As XmlDocument
    Dim oAuthorData As DataSet
    Dim oTitle As DataRow
    Dim nsmRequest As XmlNamespaceManager
    Dim queryResponse As StringWriter
    Dim xmlWriter As XmlTextWriter
    Dim strAuthorName As String

    ' Query the data from the pubs database
    Try
        oAuthorData = Helper.GetAuthorsAndTitles(queryText)
    Catch ex As Exception
        Throw New SoapException(ex.Message, SoapException.ClientFaultCode)
    End Try

    ' Prepare the data that is to be written
    queryResponse = New StringWriter
    xmlWriter = New XmlTextWriter(queryResponse)

    With xmlWriter
        ' Write out the authors name
        .WriteStartElement("P")
            .WriteStartElement("Char")
                .WriteAttributeString("bold", "true")

            ' Check if we have data in the person table
            If (oAuthorData.Tables("Authors").Rows.Count > 0) Then
                strAuthorName = oAuthorData.Tables("Authors").Rows(0).Item("au_lname") & ", " & oAuthorData.Tables("Authors").Rows(0).Item("au_fname")
            End If
                .WriteString(strAuthorName)
            .WriteEndElement()
        .WriteEndElement()

            ' Write out the other details for the author
        .WriteStartElement("Heading")
            .WriteAttributeString("collapsed", "true")
            .WriteStartElement("Text")
                .WriteString("Author Details")
            .WriteEndElement()

            .WriteStartElement("Tabular")
                .WriteAttributeString("title", "")

                .WriteStartElement("Record")
                    .WriteStartElement("Name")
                        .WriteString("Author ID")
                    .WriteEndElement()

                    .WriteStartElement("Value")
                        .WriteString(oAuthorData.Tables("Authors").Rows(0).Item("au_id"))
                    .WriteEndElement()
                .WriteEndElement()
                .WriteStartElement("Record")
                    .WriteStartElement("Name")
                        .WriteString("Address")
                    .WriteEndElement()

                    .WriteStartElement("Value")
                        .WriteString(oAuthorData.Tables("Authors").Rows(0).Item("address"))
                    .WriteEndElement()
                .WriteEndElement()
                .WriteStartElement("Record")
                    .WriteStartElement("Name")
                        .WriteString("City")
                    .WriteEndElement()

                    .WriteStartElement("Value")
                        .WriteString(oAuthorData.Tables("Authors").Rows(0).Item("city") + ", " + oAuthorData.Tables("Authors").Rows(0).Item("state") + " - " + _
							oAuthorData.Tables("Authors").Rows(0).Item("zip"))
                    .WriteEndElement()
                .WriteEndElement()
            .WriteEndElement()
        .WriteEndElement()

        .WriteStartElement("Heading")
            .WriteAttributeString("collapsed", "true")
            .WriteStartElement("Text")
                .WriteString("Publications")
            .WriteEndElement()

            ' Get the list of titles mapped to the author
            For Each oTitle In oAuthorData.Tables("Titles").Rows
                .WriteStartElement("Tabular")
                    .WriteAttributeString("title", "")

                    .WriteStartElement("Record")
                        .WriteStartElement("Name")
                            .WriteString("Title ID")
                        .WriteEndElement()

                        .WriteStartElement("Value")
                            .WriteString(oTitle.Item("title_id"))
                        .WriteEndElement()
                    .WriteEndElement()
                    .WriteStartElement("Record")
                        .WriteStartElement("Name")
                            .WriteString("Title")
                        .WriteEndElement()

                        .WriteStartElement("Value")
                            .WriteString(oTitle.Item("title"))
                        .WriteEndElement()
                    .WriteEndElement()
                    .WriteStartElement("Record")
                        .WriteStartElement("Name")
                            .WriteString("Type")
                        .WriteEndElement()

                        .WriteStartElement("Value")
                            .WriteString(oTitle.Item("type"))
                        .WriteEndElement()
                    .WriteEndElement()
                    .WriteStartElement("Record")
                        .WriteStartElement("Name")
                            .WriteString("Publication Date")
                        .WriteEndElement()

                        .WriteStartElement("Value")
                            .WriteString(oTitle.Item("pubdate"))
                        .WriteEndElement()
                    .WriteEndElement()
                .WriteEndElement()

                .WriteStartElement("P")
                .WriteEndElement()
            Next
        .WriteEndElement()

        .Close()
    End With

    ' Open the response template document
    oDocument = New XmlDocument
    oDocument.Load(Server.MapPath(".") & "\Response Template.xml")

    ' Add the required namespace references for querying the test XML
    nsmRequest = New XmlNamespaceManager(oDocument.NameTable)
    nsmRequest.AddNamespace("ns1", "urn:Microsoft.Search.Response")
    nsmRequest.AddNamespace("ns2", "urn:Microsoft.Search.Response.Content")

    ' Append the above written text to the response XML and return the same
    oDocument.DocumentElement.SelectSingleNode("//ns1:Response/ns1:Range/ns1:Results/ns2:Content", nsmRequest).InnerXml = queryResponse.ToString()


    ' ********************************************************************************
    ' UnComment this line if you want to see what we are sending back to Office
    ' oDocument.Save(Server.MapPath(".") & "\ResponseOutput.xml")
    ' ********************************************************************************

    Return (oDocument)
End Function
 
The method is quite long, but is actually very simple. We first call the GetAuthorsAndTitles method from a helper class (which we will see next). This method queries the database and then returns a dataset with the information about the authors and their titles. Once we have that information with us, we now start preparing the output XML format. To do this, we use the XmlTextWriter that allows us to create an XML stream which can then be aggregated. Note the various tags that we are using to decorate the data and you might think that it is very similar to HTML. In fact it is!! There are some variations though, but the output of this XML is very similar to a HTML document. You can see how this file looks by uncommenting the line that saves the XML to a file called ResponseOutput.xml.
 
The XML that we are generating has to packaged within a standard XML schema and this is done by loading a standard template file called Response Template.xml and then inserting the generated XML into the Content element. Finally, we return back the document to the Query method from which it is returned back to Word. Let us now see the contents of this template file:

<ResponsePacket revision="1" xmlns="urn:Microsoft.Search.Response">
	<Response domain="{77A67CD4-250A-4f6b-8F31-9BB6C0FDD4F9}">
		<QueryId>{6BD2330C-7D64-4495-BBE5-435AC43CED3A}</QueryId>
		<Range>
			<Results>
				<Content xmlns="urn:Microsoft.Search.Response.Content">
				</Content>
			</Results>
		</Range>
		<Status>SUCCESS</Status>
	</Response>
</ResponsePacket>
 
There is only one thing that you have to note here. The domain attribute value is the same GUID that we specified in the Service/Id element in the registration file. This helps to identify the appropriate service response. The query by itself has an Id which you can generate and store here. Note that the Content element is empty and this is where we inserted the XML that was generated above.
 
Ok, there is one final element that we need to take care of and that is the implementation of the GetAuthorsAndTitles method. For this, create a module called Helper.vb and paste the following code:

Imports System.Xml
Imports System.Data.SqlClient
Imports System.Configuration

Module Helper
    Public _mConnectionString As String

    ' This function is used to get information about an author and all the titles that have been authored by the
    ' author
    Public Function GetAuthorsAndTitles(ByVal authorID As String) As DataSet
        Dim sqlStatement As String
        Dim oDs As DataSet

        ' List of relation objects that are used to set the parent-child
        ' relationships between the various tables
        Dim authorTitles As DataRelation

        sqlStatement = "EXEC dbo.GetAuthorsAndTitles " & "'" & authorID & "'"
        oDs = ExecuteDataSet(sqlStatement)

        oDs.Tables(0).TableName = "Authors"
        oDs.Tables(1).TableName = "Titles"

        ' Create the relation between person and student instances
        authorTitles = oDs.Relations.Add("authorTitles", oDs.Tables("Authors").Columns("au_id"), oDs.Tables("Titles").Columns("au_id"))
        authorTitles.Nested = True

        ' ********************************************************************************
        ' UnComment this line if you want to see how the data is being returned as
        ' oDs.WriteXml("C:\Temp\Output.xml")
        ' ********************************************************************************

        Return (oDs)
    End Function

    ' This helper function executes a given SQL statement and returns a data set. This
    ' data set can then be manipulated by the caller
    Private Function ExecuteDataSet(ByVal sqlStatement As String) As DataSet
        Dim oConnection As SqlConnection
        Dim oCommand As SqlCommand
        Dim oAdapter As SqlDataAdapter
        Dim oDs As DataSet

        oConnection = New SqlConnection(_mConnectionString)
        oAdapter = New SqlDataAdapter(sqlStatement, oConnection)
        oDs = New DataSet

        oAdapter.SelectCommand.CommandType = CommandType.Text
        oAdapter.Fill(oDs)
        oConnection.Close()

        Return (oDs)
    End Function

End Module
 
The code is pretty straight forward. We execute a procedure called GetAuthorsAndTitles passing it the author id. The procedure returns two tables which we call as Authors and Titles. We then create a relation between these two tables in the dataset by using the DataRelation. To see another example of how to create hierarchical datasets refer to this article. You can uncomment the line that saves the dataset to a file to see what content is being returned. The method makes use of another helper method called ExecuteDataSet that takes in a SQL string and returns back a dataset. Note that the code makes use a public variable called _mConnectionString. Where is this being initialized?? We initialize this in the web service constructor method (the New method of Research.asmx) as follows:

Helper._mConnectionString = ConfigurationSettings.AppSettings("connectionString")
 
What this line of code does is, read a key called connectionString from the web.config file. This key is defined as follows:

<appSettings>
	<add key="connectionString" value="Server=.;Database=pubs;Uid=sa;Pwd=password"/>
</appSettings>
 
Is that all?? Are we done?? Just not yet!! We still have to write the stored procedure that gets the author and publications information. Here is the code for the procedure. You will need to create this in the pubs database.

IF (OBJECT_ID ('dbo.GetAuthorsAndTitles') IS NOT NULL)
	DROP PROCEDURE dbo.GetAuthorsAndTitles
GO
CREATE PROCEDURE dbo.GetAuthorsAndTitles
(
	@au_id	VARCHAR(20)
) AS
BEGIN
	-- Select all the details for the author
	SELECT
		au_id, au_lname, au_fname, phone, address, city, state, zip
	FROM
		authors
	WHERE
		au_id = @au_id
	IF (@@ROWCOUNT = 0)
	BEGIN
		RAISERROR ('Invalid author ID. Author does not exist.', 16, 1)
		RETURN
	END

	-- Select all the titles for the author
	SELECT
		titleauthor.au_id,
		titles.title_id, titles.title, titles.type, titles.pubdate
	FROM
		titleauthor
			INNER JOIN titles ON titles.title_id = titleauthor.title_id
	WHERE
		titleauthor.au_id = @au_id
END
GO
 
Ok, now we are all set. Just before you register this service this Word, it might be helpful if you can test this code from Visual Studio itself. If you execute the project, you will see the browser displaying the services available and you can test these services to see if they work (if they give any errors do not worry, the XML may not be well formed). Now for the registration!!
 
Registering the Authors Research Service
 
Registration is multi-step process. Open the research pane by using Tools > Research. Once you have the research pane displayed, click on the Research Options link present at the bottom of the pane. This opens the following dialog box:
 
 
This dialog lists all the existing services. Click on the Add Services button to create a new service. This opens the new service dialog box in which we will provide the URL to our service as follows:
 
 
This is the time of deliverance!! Click on the Add button and if everything goes well, you should see the following dialog box.
 
 
Note that the message that gets displayed here is the same that we registered in the Message element of the registration template file. Click on the Continue button. You will now see the list of services exposed by this web service as shown in the following figure. Again, this information comes from the Service element that we registered earlier.
 
 
Note that pieces of the XML that we registered earlier are shown here. Click on the Install button and a confirmation message is displayed. Close the dialog box and you will now see the service (Search Authors (By Author ID)) being displayed in the dialog box that was originally invoked by using the research options link.
 
Invoking the Research Service
 
To invoke our service, just enter an author id in the Search for text box and choose our service from the dropdown box below the field. The service will now be automatically invoked (you can also invoke it by clicking on the green button). Again, if everything goes well, you will see the output from the service formatted the way we want. This is shown in the following figure.
 
 
Well, isn'nt that interesting!! We got excellent capabilities here with familiar .NET code and all we had to do was abide by some standard schemas to generate the XML and Office takes care of the rest. You can access the same research pane in Excel and also Powerpoint. How's that for reusability!!
 
There are many other interesting things that you can do with the generated schema. For example, you can provide interactivity in the output generated by annotating the generated XML with some more tags which will allow the user to select information from the display and paste it onto the document. I will leave that as an exercise to the reader!! Have fun experimenting!!

Home