LOB Data in Office

In 2004 Microsoft introduced a brand new technology called Information Bridge Framework or IBF for short. IBF is targeted to information workers, computer users who spend a majority of their time analyzing and reporting on information about their business and making decisions based on these analyses. The success of a modern organization depends largely on the ability of its information workers to discover, analyze, and act on line-of-business data (LOB) and operational information. They depend on e-mail, forms, and electronic documents to communicate and collaborate about the business. In many cases the information workers are using Microsoft Office System applications like Word, Excel and Outlook to do their daily job, these applications are the so called comfort zone of the users.

IBF brings LOB data to that information worker’s comfort zone, it exposes for example data coming from a back-end system in a front-office application like Word in a task pane. On top of that IBF has a catalog of metadata describing relations and actions between several LOB data sources in a very abstract way. The drawback of IBF development is that it’s quite complicated to get started. You have to get familiar with the metadata catalog, write IBF compliant web services and tie everything together.

IBF uses technologies like smart tags and task panes to integrate with Office applications. Since Visual Studio Tools for Office 2005 it’s very easy to built smart tags and task panes, without having to deal with the nasty COM interfaces (remember IsmartDocument?). This article is going to describe how you can mimic IBF functionality by using VSTO 2005, without having to use the metadata catalog.

The Proposal Builder Application

In this article we’re going to build an application that will allow information workers to build a proposal by using Word. The result will be a Word template document which allows the information user to fetch data from the back end system. We’ll be using the AdventureWorks database as a back end, so the proposal will be about bikes. When an information worker opens the Word template document, he’ll see an empty proposal. The document contains a section where a customer data can be filled out, and a section where information about a product can be filled out. When the user enters one of those sections, the task pane in Word is going to display a task pane that will browse through data coming from the AdventureWorks database. When the user has selected the desired customer record or product record, the information can be sent from the task pane to the Word document.

Building the Word Template

The first step in building the solution is to create a Word template that is going to be opened by the information workes. To start we are going to create a new VSTO solution in Visual Studio 2005. If you have VSTO 2005 installed on your machine you will have an Office category in the Project Types list, we need to select the “Word Template” template. In the next screen you can choose to create a new Word document or start from an existing Word document. Since we don’t have a document to start from we’re going to create a new one.

When the project has been created, you’ll see a blank Word template embedded in the Visual Studio 2005 development environment. All the functionality of Word as an application is still accessible from the Visual Studio menu’s. For the ProposalBuilder solution we need to create a document that looks like the one in the next screenshot. For now the name, address, email, number, ... fields for the Customer and Product sections are plain text.

Building the Task Pane Control

The next step is building the custom task pane that we want to show to the information worker, that will allow them to easily look up LOB data and insert that data in the Word document. In VSTO 2005 you can use any Windows Forms user control in a Task Pane. So we need to add a new user control in Visual Studio. By using the Data Sources window in Visual Studio, we can quickly build UI controls that get customer data from the AdventureWorks database. The source of the data used in this example is the vIndividualCustomer view. When this view is added to the AdventureWorks dataset, you can easily drag and drop individual fiels from the Data Sources window to the newly created user control. Visual Studio will automatically generate UI elements in a ToolStrip that allow navigation to the first, last, next and previous customer record. Since we only want to allow that the information workers can retrieve data, the ReadOnly property of all the TextBoxes can be set to true.

Since the AdventureWorks database contains a large number of customers, a search functionality is indispensable. We are going to implement the search functionality by adding a query to the BindingSource compoment which you can find a the bottom of the screen. When you open the BindingSource tasks, you can click the “Add Query…” link,

Now the Search Criteria Builder window will open. In this window you can configure the SQL select statement that will be used to do the query. The original select statement is already filled out, so the only thing that needs to be added is the WHERE clause. Don’t forget to name the new query, for example FillByName. As an alternative you can also use the Query Builder if you prefer to build the select statement by using a nice GUI.

When the OK button is clicked, a new ToolStrip is generated, containing a Label, TextBox and Button. These controls will allow your users to easily search for customers based on the last name. You can easily change text of each control, so the end result could look like the following screenshot. If you want to create a user control that resize properly, make sure the Anchor TextBox controls is set to Left, Top and Right.

Testing the User Control

The user control that we’ve just created can already be tested. We only need to write code that will load the user control and displays it in the task pane. We need to do that in de code behind of the Word template that is a part of our solution. In the solution explorer window, right click on the ThisDocument.cs file which is located under the template itself, and choose “View code”.

In the ThisDocument_Startup function, we need to write some code that will first instantiate the user control, and than add it to the controls of the ActionsPane.

private void ThisDocument_Startup(object sender, System.EventArgs e)
{
    CustomerSelection cs = new CustomerSelection();
    this.ActionsPane.Controls.Add(cs);
}

When we now start the solution, Word will open and our user control will be loaded in the task pane. When a part of a name is entered in the TextBox on the ToolStrip control, and the Search Button is pressed, the customer records are displayed.

Showing the Task Pane when Requested

So far the task pane containing the Proposal Builder control is displayed each time the Word template document is opened. This is not very useful, the goal is to only display the task pane when the user is entering customer date. So we need to figure out a way to get notified when the cursor enters the customer name section in the template, that’s when we want to display the user control. VSTO 2005 has introduced just the control that we need: the Bookmark control. This Bookmark control has very little UI in the Word template, compare it to an editable Label in your Word document. But this Bookmark control has events that will notify us when the user enters or leaves a section marked by the Bookmark control. To add the Bookmark control, drag and drop it from the toolbox to the Word document. You’ll see a message box popping up asking you to make a selection that will be encapsulated by the Bookmark. In this example we can do add a Bookmark control for the name, address and email address of the user.

Notice that each Bookmark is a real user control as well, so it has properties and events that we can use from the code behind the Word template.

To show the user control in the task pane when the cursor enters the name Bookmark, we need to handle the Selected event. When the user moves the cursor out of the Bookmark, the user control should be removed from the task pane. We can accomplish this by handling the Selected on Deselected events of the Bookmark control. Instead of creating each time a new user control, it’s a better idea to keep a reference of the user control alive, so the cs variable is declared on class level now. A new instance is created when the document template is opened, and it’s added and removed to the task pane when the Selected and Deselected Bookmark events are triggered.

CustomerSelection cs = null;

private void ThisDocument_Startup(object sender, System.EventArgs e)
{
    cs = new CustomerSelection();
}

private void nameBookmark_Selected(object sender, 
    Microsoft.Office.Tools.Word.SelectionEventArgs e)
{
    this.ActionsPane.Controls.Add(cs);
}

private void nameBookmark_Deselected(object sender, 
    Microsoft.Office.Tools.Word.SelectionEventArgs e)
{
    this.ActionsPane.Controls.Remove(cs);
}

Communicating with the Word Document

There’s one issue left to solve: we need to be able to send data from the user control in the task pane back to the word document. One way to accomplish this is to pass a reference to the Bookmark controls to the CustomerSelection user control. When the user clicks a button on the user control, data from the selected customer will be sent to the Text properties of the Bookmark controls. We can pass the reference to the bookmark controls to the task pane control, in the constructor of that control.

public CustomerSelection(Bookmark name, Bookmark address, Bookmark email)
{
    InitializeComponent();
    nameBookmark = name;
    addressBookmark = address;
    emailBookmark = email;
}

When the control is instantiated in the Starup function of the Word document, references to the corresponding bookmarks are passed:

private void ThisDocument_Startup(object sender, System.EventArgs e)
{
    cs = new CustomerSelection(
           nameBookmark, addressBookmark,emailBookmark);
}

Once we have access to these references in the control, we can add a button to the control that executes following code:

private void insertButton_Click(object sender, EventArgs e)
{
    DataRowView currentDrv = (DataRowView)
        vIndividualCustomerBindingSource.Current;

    AdventureWorksDataSet.vIndividualCustomerRow selectedRow =
        (AdventureWorksDataSet.vIndividualCustomerRow)
                currentDrv.Row;

    if(selectedRow.IsTitleNull())
        nameBookmark.Text = selectedRow.FirstName +
        selectedRow.LastName;
    else
        nameBookmark.Text = selectedRow.Title + " " + 
            selectedRow.FirstName + selectedRow.LastName;

    if(selectedRow.IsAddressLine2Null())
        addressBookmark.Text = selectedRow.AddressLine1;
    else
        addressBookmark.Text = selectedRow.AddressLine1 + 
                selectedRow.AddressLine2;
    emailBookmark.Text = selectedRow.EmailAddress;
}

Now we can execute the solution, when a customer is selected, you can click the Insert button so the corresponding data is sent to the Word document. In the same way you could also build a control to fetch product data.