Export (0) Print
Expand All
Expand Minimize
4 out of 7 rated this helpful - Rate this topic

Using the Server Capabilities in Microsoft Visual Studio 2005 Tools for Office to Build a Benefits Registration System

Office 2003

J. Jason De Lorme, Simplesheet, Inc.

Lori Turner, Microsoft Corporation

January 2006

Applies to: Microsoft Visual Studio 2005, Microsoft Visual Studio 2005 Tools for the Microsoft Office System , Microsoft Office Word 2003, Microsoft ASP.NET 2.0, Microsoft SQL Server 2005 Express Edition

Summary: Learn how to use the server capabilities in Microsoft Visual Studio 2005 Tools for the Microsoft Office System and the Microsoft ASP.NET 2.0 page framework to build a benefits registration system. Discover how to build a Web application that dynamically attaches a Visual Studio 2005 Tools for Office customization to a document, caches data in a document, and delivers a document to a user from an HTTP Web request. The accompanying download contains projects written in both Microsoft Visual Basic and Microsoft Visual C#. (29 printed pages)

NoteNote:

This article replaces the previously published article, "Overview of Server Capabilities in Visual Studio 2005 Tools for Office."

Download OfficeVSTOBenefitsRegistration.msi.

Contents

  • Overview

  • Benefits Registration Application Overview

  • Setup and Installation

  • Using the Benefits Registration Application

  • Developing the Benefits Registration Application

  • Deployment and Security Considerations

  • Conclusion

  • Additional Resources

  • About the Authors

Overview

Microsoft Visual Studio 2005 Tools for the Microsoft Office System enables developers to build enterprise solutions using Microsoft Office Word 2003 and Microsoft Office Excel 2003 as smart clients. Typically, solutions are single-document or template-based solutions, for example, status reports, expense reports or invoices. Microsoft Visual Studio 2005 attaches the code to the document or template, and when the user opens the document or creates a new document from the template, Word or Excel executes the code.

Although that solution works great for a single document or single template, what happens when you have a heterogeneous group of documents that all must share the same customization functionality, and you cannot control the template or who authors the document? Demonstrating the server capabilities of Visual Studio 2005 Tools for Office, ASP.NET 2.0, and Word 2003, this article offers a solution to that problem. The application alleviates the process of filling out dissimilar benefits registration forms and allows human resource administrators to keep track of benefits registrations.

Scenario

Registering for benefits can be a tedious, repetitive process. You can probably recall the first week of a new job, getting married, having your first child, or any other qualifying event, where you were required to fill out your name and address a dozen times on a dozen different paper forms. Although most organizations now publish their benefits registration forms on a corporate intranet, this does little more than save you the time required to walk to the human resources department and get a copy of the form. What you want instead is for the portal to figure out who you are, read the human resources database, and fill in as much of the form as it can before sending it to you.

Many of the registration forms we see are created with Word. Word offers great flexibility and ease of layout and design. It also has significantly better printing support than Web-based forms or other applications designed strictly for forms. Most importantly, with Visual Studio 2005 Tools for Office, Word 2003 is a great platform on which to build enterprise software solutions.

Figure 1. Benefits registration form


Benefits registration form

Benefits Registration Application Overview

The following figure shows the three parts of the Benefits Registration application. There are two users: employees and administrators. Employees view available benefits on the portal and complete benefits registration forms in Word. Administrators upload new forms and keep track of benefits registrations.

Figure 2. Architecture diagram of the Benefits Registration application


Architecture diagram of the application

Benefits Portal Application

The Benefits Portal application is an ASP.NET 2.0 Web application with five pages, as shown in the site map below. The Default.aspx page displays a login button and a list of available links for authenticated users. The login button performs authentication and authorization, which restricts access to the administrative pages and allows only authenticated users into the remainder of the site.

Figure 3. Web site map


Web site map

The ListBenefits.aspx page displays a list of the available benefits. It displays a completed date if the employee has registered for the benefit, a short description, and the date the benefits registration form was uploaded to the site. An administrator can delete benefits registration forms from this page by clicking Delete. The benefit name is a hyperlink to download the associated form into Word.

When an employee registers for a benefit, the Benefits Portal application stores the information in the database. Administrators can view a list of benefits registrations from the ListRegistrations.aspx page.

Administrators upload new benefits registration forms from the UploadForm.aspx page.

HTTP Handler

The Benefits Portal application registers the BenefitFormHandler HTTP handler to service all requests for *.doc Word documents in the Forms directory. To request a Word document from the Benefits Portal, an employee clicks a hyperlink on the ListBenefits.aspx page. The BenefitFormHandler opens a connection to the database, retrieves information about the benefit and the employee, caches the information in the document, and then sends the document to the employee's browser.

Web Service

The Benefits Portal application calls the Register Web service from the Word Document application in Visual Studio 2005 Tools for Office to register an employee for a benefit. The Web service records the registration in the database.

Benefits Registration Form

The Benefits Registration application lessens the need for users to type redundant data multiple times and guides them through the traditional process of saving and printing documents for consumption by legacy processes.

In Microsoft Office Professional Edition 2003, or Word 2003 as a stand-alone product, the XML features enable you to attach an XML schema representing employee details to a benefits registration form. You manually apply XML tags from the Schema, such as FirstName, LastName, or DateOfBirth, to the document, as shown in Figure 4.

Figure 4. Applied XML tags


Applied XML tags

As a Word Document application in Visual Studio 2005 Tools for Office, the document references a managed assembly, described in this article as a customization assembly. When the document loads, Visual Studio 2005 Tools for Office invokes a startup method in this assembly. The startup method binds employee data cached in the document to XML host controls that represent the applied XML tags. This process fills in the common employee information.

The Benefits Registration application creates a user control in the actions pane, shown in Figure 5, that uses cached benefit data to display information about the benefits registration form and instructions for completing it.

The Save Registration button in the actions pane, shown in Figure 1, invokes a three-page wizard, modeling the prescribed registration process. The first step asks if the user would like to save a local copy of the benefits registration form, opening the Save As dialog box, if appropriate. The second step submits XML data to the Benefits Portal application's Register Web service to record the registration. The final step facilitates printing the form by opening the Print dialog box in Word.

Data Layer

The Benefits Portal application uses the data layer to access the underlying Microsoft SQL Server database and retrieve business objects. The Benefits Registration application does not access a database, but uses the data layer objects to represent the Employee and Benefit business objects that are cached in the document. The data layer uses typed datasets to represent the business objects shown in Table 1.

Table 1. Datasets
Name Description

Benefit

A benefit that is available for employee registration. Benefits have a name, description, associated form, and creation date.

Employee

Common information about an employee that can be shared between forms.

EmployeeBenefit

An employee registration for a benefit. When an employee registers, the relationship and date of the activity is stored in this object.

Setup and Installation

To get the most out of this article, download and install the associated sample code and follow along.

Required Software

  • Microsoft Visual Studio 2005 Tools for the Microsoft Office System

  • Microsoft SQL Server 2005 Express Edition (included with Visual Studio 2005 Tools for Office)

  • Microsoft Office Word 2003 Service Pack 1 (SP1) or later

To install the software

  1. Install Visual Studio 2005 Tools for Office with Visual Web Developer and SQL Server 2005 Express Edition.

  2. Perform a complete installation of Microsoft Office Professional Edition 2003 or stand-alone Word 2003.

  3. Download and run the installation package associated with this article.

    A link to download the package follows the Summary at the top of this article. The package contains source code, sample Word documents, and a SQL Server 2005 Express Edition database file.

    NoteImportant:

    The Microsoft Windows Installer installation package copies the necessary files to your computer. The Setup.bat batch file installs the required XML Schema in Word and grant permissions in your security policy that allows the Benefits Registration application to run. If you change any of the project output settings, you may have to modify your security policy in order for the application to run. For more information, see Deployment and Security Considerations.

Using the Benefits Registration Application

To become familiar with the application, walk through the application as employee and again as administrator.

To walk through the Benefits Portal application

  1. Double-click Setup.bat, which is located in the folder where you installed the Benefits Registration application.

    Setup.bat installs a schema into the Word Schema library and sets up the required security policies for the application.

  2. Open BenefitsRegistration.sln from either the CS or VB folder in Visual Studio.

  3. If you want to use a named instance other than the application's default name of SQLExpress, make the following modifications:

In the Web.config file of the BenefitsPortal project, change the data source for the connection string from .\SQLExpress to your named instance.

  • Right-click the BenefitData project in Solution Explorer, and then click Properties.

  • On the Settings tab, change the data source for the BenefitsConnectionString setting from .\SQLExpress to your named instance.

  1. Right-click the BenefitsPortal project in Solution Explorer, and then click Set as Startup Project to ensure that the Web site is the startup project.

  2. Press F5 to build and run the application.

    NoteNote:

    This sample uses the ASP.NET Web server, included with Visual Studio 2005, and does not require Microsoft Internet Information Services (IIS). The Benefits Registration application written in C# sets the ASP.NET Web server to use port 2420 by default. The application written in Visual Basic uses port 2288 by default. If you have other software running on port 2420 or port 2288, Visual Studio may prompt you for a Web site URL when the application starts. Click Cancel, and then change the development Web server port by selecting the BenefitsPortal project in Solution Explorer, and then in the Properties window, changing the port number to an available port. For more information, see Changing the Development Web Server Port later in this article.

Employee Scenarios

As an employee, you can use the Benefits Registration application to register for different benefits using benefits registration forms.

To list available benefits

Sign in to the Benefits Portal with username janetl and password p@ssword.

  1. Click Click here to register for employee benefits.

    Notice the list of benefits available. Currently, the available benefits include Fitness Incentive and Tuition Assistance.

    Figure 5. Available Benefits Web page


    Available Benefits Web page

To register for a benefit

  1. (Optional) Select any one of the benefits to download the benefits registration form.

    The Benefit Name column is a hyperlink to open the benefits registration form in Word. Word opens inside Microsoft Internet Explorer with a Document Actions task pane and with the employee details filled in throughout the form.

  2. Complete a few of the editable sections of the document, and then click Submit.

    Document protection guides you to only the locations you must complete. The editable sections are enclosed with brackets and highlighted in yellow.

  3. (Optional) Click Save Registration in the actions pane to start the Benefits Registration Wizard.

  4. Follow the steps of the wizard.

    You have successfully registered for the benefit and your registration is recorded in the database.

    NoteNote:

    The Benefits Registration application saves only the data that you have registered for a benefit in the database; the contents you enter into the form are saved only in your local copy of the document and not on the server.

  5. Click Back to Benefits Registration on the actions pane to return to the Benefits Portal.

    If prompted to save your changes to the document, click No.

To view completed registrations

  • In the Benefits Portal application, click Click here to register for employee benefits.

    You see a date and time in the Completed column next to the benefit for which you registered.

    Figure 6. Completed benefits registration


    Completed benefits registration

Administrator Scenarios

As an administrator, you can use the Benefits Registration application to view completed registrations and upload new benefits registration forms.

To list completed registrations

If you are currently signed in, click LOGOUT to sign out as an employee.

  1. Log on to the Benefits Portal with the username admin and a password of p@ssword.

  2. Click Click here to view employee registrations.

  3. View the employee, benefit, date, and time of registration.

    Figure 7. Employee registrations


    Employee registrations

In the upper-right corner of the page, click HOME to return to the home page.

To upload a new benefits registration form

  1. On the home page, click Click here to upload a new benefits registration form.

  2. Click Browse to select a form to upload.

    Benefits registration forms are Word documents that are marked up with the Employee.xsd Schema. You can create your own document or use the document SampleBenefitForm.doc that is in the Benefits Registration Sample root folder.

  3. Enter a benefit name and description, and then click Add.

    The Benefits Portal application redirects you to the ListBenefits.aspx page, which displays the list of forms that now includes the benefits registration form you just uploaded. Any employee or administrator who logs onto the site sees this new benefits registration form listed and can complete it using the employee scenario you walked through earlier.

To delete a benefits registration form

As an administrator, you can also delete benefits registration forms.

  • On the ListBenefits.aspx page, click Delete next to one of the benefits registration forms.

    The DeleteForm.aspx page appears with a status message indicating that you have successfully deleted the form.

Developing the Benefits Registration Application

Table 2. BenefitsRegistration.sln projects
Project Type Description

BenefitForm

Office Word document

A Visual Studio 2005 Tools for Office Word Document project. This project contains the code to populate the XML nodes of the Word document, the user controls hosted in the actions pane, and the wizard for submitting the registration.

BenefitData

Class library

A class library that defines strongly typed datasets representing the business objects, and implements the data access layer, serving as a bridge between the strongly typed datasets and a database connection for reading and writing data.

BenefitsPortal

Web site

An ASP.NET 2.0 Web application composed of ASPX pages, a Web service for recording registrations, and an HTTP handler that processes Word document requests.

Building the Benefits Registration Form

Hopefully, you are following along with the associated solution code because this article does not show how to create each of the projects from scratch. There are many good articles describing how to build a Word Document application from scratch. For more information, see Building a Status Report Application with Visual Studio 2005 Tools for Office.

First, here is a little document anatomy. A Visual Studio 2005 Tools for Office application consists of two discrete parts: the Microsoft Office document and the customization assembly. Visual Studio 2005 Tools for Office compiles solution code into an assembly and distributes it separately from the document. Visual Studio 2005 Tools for Office documents have an embedded, structured storage stream and some custom attributes that reference the assembly location. When a Visual Studio 2005 Tools for Office document opens, Microsoft Office checks to see whether this reference exists, and then starts the Visual Studio 2005 Tools for Office runtime. The runtime attempts to locate the assembly, and then execute the startup routine, ThisDocument_Startup, as shown in the following code:

Private Sub ThisDocument_Startup(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Startup

private void ThisDocument_Startup(object sender, System.EventArgs e)

XMLNode Host Controls

When you add XML tags to the document in the Word designer in Visual Studio, Visual Studio 2005 Tools for Office creates an XMLNode host control for each XML tag. If you use an existing document when you create the project, Visual Studio 2005 Tools for Office discovers all of the XML elements in the document, and then creates the necessary host controls automatically.

In Solution Explorer, you see the ThisDocument.cs file or the ThisDocument.vb file, which Visual Studio 2005 Tools for Office created. You can right-click the ThisDocument.cs or ThisDocument.Designer.vb file name, and then click View Code to see that the ThisDocument class is defined as public partial. Partial classes are one of the new features of C# and Visual Basic. Partial classes make it possible for a single class to be defined in multiple files. This is especially helpful for classes that are partially autogenerated by Visual Studio. Visual Studio can generate code in one file because users cannot directly modify the code.

If you click Show All Files in Solution Explorer, you see two additional files nested below ThisDocument.cs or ThisDocument.vb. Table 3 describes these autogenerated files.

Table 3. Autogenerated code files
File Description

ThisDocument.Designer.xml

This XML file enumerates all the managed host controls that the document contains. Visual Studio uses this file as a blueprint to generate the code-behind ThisDocument partial class.

ThisDocument.Designer.cs

ThisDocument.Designer.vb

The code-behind ThisDocument partial class, which contains declarations for all the host controls, and much of the internal plumbing enabling the application to run.

The BenefitForm.doc in the BenefitForm project contains all of the available XML nodes in the Employee.xsd schema. Thus, due to the host control autogeneration model, the BenefitForm customization assembly contains an XMLNode host control for each XML node. This is an important point to understand due to the implications when you must extend your Schema or handle missing nodes in your documents.

  • Extending the Schema. If you ever extend the Employee Schema to include another field, for instance a national identification number, you have to modify the template document in Visual Studio 2005 Tools for Office to include a new XMLNode host control, recompile, and republish the BenefitForm customization assembly.

  • Handling missing nodes. The BenefitForm customization assembly contains a reference for all XMLNode host controls; however, documents that use the customization might use only a subset of those XMLNode host controls. For example, some benefits registration forms might require a social security number, but others might require only a name or address. When you iterate through the controls collection to test for XMLNode host controls, you can ignore exceptions of type ControlNotFoundException to handle this situation. The following code shows how to handle missing nodes for the optional XMLNode host controls:

    try
    {
        // Attempt to assign a value to the visible Text property.
        myWordXMLNode.Text = "Some sample text.";
    }
    catch (ControlNotFoundException)
    {
        // Ignore, some documents may not have all the fields.
    }
    

    Because the code-behind ThisDocument partial class for each document creates an instance of XMLNode regardless of whether the node exists, myWordXMLNode is never null. You cannot determine if the node exists unless you try to access it.

Managed XMLNode Host Controls

The Visual Studio 2005 Tools for Office managed XMLNode host control offers two features not included in the COM object: it exposes events and can be bound to data.

If you are browsing through the MSDN library you may be surprised to discover two XMLNode references in the Word object model. To be clear, the XMLNode host control described here is the Visual Studio 2005 Tools for Office managed Microsoft.Office.Tools.Word.XMLNode host control. Do not be confused by the Microsoft.Office.Interop.Word.XMLNode interface, which is an interface used by the primary interop assemblies to call the COM-based Word XML object.

Cached Data

In Visual Studio 2005 Tools for Office you can cache data within structured storage in the Word document. Technically, it is possible for a Visual Studio 2005 Tools for Office application to cache any data type that can be stored as XML, but typically, you cache datasets or typed datasets. The benefits registration form defines two typed datasets as cached members in the ThisDocument class, as shown in the following code:

<Cached()> _
Public benefitDataSet As Benefit
<Cached()> _
Public employeeDataSet As Employee

[Cached]
public Benefit BenefitDataSet;
[Cached]
public Employee EmployeeDataSet;

You can use the declarative attribute Microsoft.VisualStudio.Tools.Applications.Runtime.CachedAttribute to enable caching on a member. You must declare the member variable as public to get cached.

Caching the data in the document is a design choice and an architectural decision that each organization makes. Although the Benefits Registration application could have connected to a Web service to retrieve the data when the document loaded, caching employee and benefit data inside the document gives clients everything they require inside the document without opening another connection to the database to retrieve external data.

Another advantage to data caching is that it enables users to work offline. Because the employee data and benefit data are stored in the local copy of the document, the application can run offline because a database connection is not required to populate the document with data.

Data Binding

Although the BenefitRegistrationPane control uses the BenefitDataSet to populate label controls, fundamentally, the key to this solution is data binding XMLNode host controls to the EmployeeDataSet.

The BindEmployeeData method, called from the ThisDocument_Startup method, adds data bindings to each managed XMLNode host control:

For i As Integer = 0 To Me.Controls.Count - 1
    Dim node As XMLNode = Nothing
    If TypeOf (Me.Controls(i)) Is XMLNode Then
        node = DirectCast(Me.Controls(i), XMLNode)
        Try
            If Not node.HasChildNodes Then
            If node.BaseName = "DateOfHire" Or _
                    node.BaseName = "DateOfBirth" Then
                node.DataBindings.Add("Text", employeeBindingSource, _
                    node.BaseName, True, DataSourceUpdateMode.Never, "d")
            Else
                node.DataBindings.Add("Text", employeeBindingSource, _
                    node.BaseName, False, DataSourceUpdateMode.Never)
            End If
        End If
        Catch ex As ControlNotFoundException
            'Ignore and continue if the XMLNode does not exist in the 
            'document.
        Catch ex As Exception
            Throw ex
        End Try
    End If
Next

for (int i = 0; i < this.Controls.Count; i++)
{
    object ctrl = this.Controls[i];
    if (ctrl.GetType() == typeof(XMLNode))
    {
        XMLNode node = null;
        node = (XMLNode)(this.Controls[i]);
        try
        {
            if (!node.HasChildNodes)
            {
                if ((node.BaseName == "DateOfHire") || (
                    node.BaseName == "DateOfBirth"))
                {
                  node.DataBindings.Add("Text", employeeBindingSource,
                    node.BaseName, true, DataSourceUpdateMode.Never, "d");
                }
                else
                {
                   node.DataBindings.Add("Text", employeeBindingSource, 
                     node.BaseName, false, DataSourceUpdateMode.Never);
                }
            }
        }
        catch (ControlNotFoundException ex) { }
        catch (Exception ex) { throw ex; }
    }
}

Document Protection

Form protection is not a new feature in Word, but Word 2003 introduces document protection. Form protection allows users to input data inside of Word fields without changing the rest of the document. Document protection offers many other advantages. For the purposes of this application, document protection offers greater granularity. You can arbitrarily select regions of the document that a user can edit, much like forms protection, but without fields. Notice the yellow sections of the document in Figure 8 which identify regions that the user can edit.

Figure 8. Document protection


Document protection

This application helps users complete the document without accidentally deleting an XML tag, than with preventing them from changing the document itself. At the end of the ThisDocument_Startup routine, the code calls the EnableProtection method, as shown in the following code:

Private Sub EnableProtection()
    If (Me.ProtectionType = Word.WdProtectionType.wdNoProtection) Then
        Me.Protect(Word.WdProtectionType.wdAllowOnlyReading, , "")
    End If
End Sub

private void EnableProtection()
{
    if (this.ProtectionType == Word.WdProtectionType.wdNoProtection)
    {
        // This is not used for security purposes, leave blank.
        object password = "";
        this.Protect(Word.WdProtectionType.wdAllowOnlyReading,
            ref missing, ref password, ref missing, ref missing);
    }
}

The application presents the document to the user in protected mode. However, to programmatically modify any of the document's XMLNode host controls during the startup routine, you must ensure that the document is not protected. If the document is protected when you attempt to modify a host control, you get the following exception:

System.Runtime.InteropServices.COMException (0x800A17EC): You are not 
    allowed to edit this region because document protection is in effect.

To prevent this error and ensure document protection is on when the user sees the document, the startup routine includes the following code:

Private Sub ThisDocument_Startup(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles Me.Startup
    DisableProtection()

    ' Do data binding work . . . 

    EnableProtection()
End Sub

private void ThisDocument_Startup(object sender, System.EventArgs e)
{
    DisableProtection();

    // Do data binding work . . . 

    EnableProtection();
}

Actions Pane

The actions pane in Word hosts a custom user control. This user control includes label controls that are dynamically populated with data about the benefit and a button that invokes the Submission Wizard. The following code creates the control and adds it to the actions pane:

' Create actions pane control.
benefitRegistrationPane = New BenefitRegistrationControl(benefitDataSet)

' Assign the EmployeeId from the data set.
benefitRegistrationPane.EmployeeId = _
    employeeDataSet._Employee(0).EmployeeId

' Assign the BenefitId from the data set.
benefitRegistrationPane.BenefitId = benefitDataSet._Benefit(0).BenefitId

' Add the control that displays the actions pane.
Me.ActionsPane.Controls.Add(benefitRegistrationPane)

// Create actions pane control.
benefitRegistrationPane = new BenefitRegistrationControl(BenefitDataSet);

// Assign the EmployeeId from the data set.
benefitRegistrationPane.EmployeeId =
    EmployeeDataSet._Employee[0].EmployeeId;

// Assign the BenefitId from the data set.
benefitRegistrationPane.BenefitId = BenefitDataSet._Benefit[0].BenefitId;

// Add the control that displays the actions pane.
this.ActionsPane.Controls.Add(benefitRegistrationPane);

Registration Wizard

Users click the Save Registration button to invoke the Registration Wizard. The Wizard is a simple example of how you can leverage Windows Forms in Visual Studio 2005 Tools for Office. The Wizard folder contains three standard Windows Forms. The BenefitRegistrationPane user control orchestrates opening the three forms with their ShowDialog method, as shown in the following code:

' Create the first step of the wizard.
Dim step1 As New Step1Form
If (step1.ShowDialog() = DialogResult.Cancel) Then
    ' Return back to the document if the user cancelled.
    Return
End If
' And on through step 3.

// Create the first step of the wizard.
Wizard.Step1Form step1 = new BenefitForm.Wizard.Step1Form();
if (step1.ShowDialog() == DialogResult.Cancel)
{
    // Return back to the document if the user cancelled.
    return;
}
// And on through step 3.

Using the DataSet Designer

The BenefitData project supports both the BenefitForm and the BenefitsPortal projects, defining the business objects and acting as a data access layer.

Using the dataset designer in Visual Studio 2005 Tools for Office, you can drag and drop tables from the database onto the design surface to quickly create typed datasets representing the tables in the database. Typed datasets provide strongly typed methods, events, and properties that enable you to access tables and columns by name, instead of iterating through collections. In addition to creating a typed dataset, the designer also creates a strongly typed table adapter class. The table adapter serves as the data access layer, bridging the business object and the database.

Connections

The Benefits Portal application depends on data in tables for users, authentication, and more. Therefore, you should store the connection string in only one place in the Benefits Portal application. Because the BenefitForm project does not connect to the data source, you do not have to store a connection string for that project, but the BenefitData project does connect to the data source.

Although the wizards make it easy to create typed datasets, they make it a bit difficult to support complex scenarios like this. Specifically, the dataset designer automatically creates a separate configuration file in the BenefitData project and stores the connection string, which can be problematic.

To use a custom Connection object

  1. Double-click the EmployeeBenefit.xsd file in the BenefitData project to open the dataset designer.

    The dataset defines all the fields of the EmployeeBenefit table, and it has an EmployeeBenefitTableAdapter that defines data access methods, such as Fill and Exists.

  2. Select EmployeeBenefitTableAdapter in the designer, and then view the Properties window, as shown in Figure 9.

Figure 9. EmployeeBenefit.xsd in the dataset designer


EmployeeBenefit.xsd in the dataset designer

With the ConnectionModifier property set to Public, the calling code can specify its own Connection object, as shown in the following code:

Dim tableAdapter As New _
    EmployeeBenefitTableAdapters.EmployeeBenefitTableAdapter()
tableAdapter.Connection = New SqlConnection(connectionString)

EmployeeBenefitTableAdapter tableAdapter = new 
    EmployeeBenefitTableAdapter();
tableAdapter.Connection = new SqlConnection(connectionString);

Building the Benefits Portal Application

The Benefits Portal Web site is a gateway into the Benefits Registration application. Although it demonstrates some ASP.NET 2.0 features, the focus of this article is how it takes advantage of the server capabilities of Visual Studio 2005 Tools for Office. This section describes how to attach a customization assembly, cache data in a document, and deliver a document to a user's browser without having to invoke Word or even having Word installed on the server.

UploadForm.aspx

The Benefits Portal application stores a list of available benefits in the database and the associated form in a Web accessible directory, named Forms. The Upload page ensures that the benefits registration form does not already exist on disk and then saves the form to disk. It attaches the BenefitForm customization assembly to the form programmatically using the ServerDocument class, as shown in the following code:

ServerDocument.AddCustomization(documentPath,
    assemblyFileName, manifestPath, benefitFormVersion, false);

AddCustomization is a static method of the Microsoft.VisualStudio.Tools.Applications.Runtime.ServerDocument class. It takes a Word document or Excel workbook and attaches a customization without requiring Word or Excel. Table 4 describes the parameters from the previous example.

Table 4. AddCustomization parameters
Parameter Description Example value

documentPath

The location of the Word document after you uploaded it

..\Visual Studio 2005\Projects\Benefits Registration Sample \ BenefitsPortal\Forms\Fitness Incentive Form.doc

assemblyFileName

The location of the customization assembly relative to the manifestPath

BenefitForm_1.0.0.0\BenfitForm.dll

manifestPath

The location of the application manifest that is produced by publishing the BenefitForm project

http://localhost:2420/BenefitsPortal/Forms/BenefitForm.application

benefitFormVersion

A string representation of the application deployment manifest version

1.0.0.0

After attaching the customization, the UploadForm.aspx page records the benefit in the database so that it is visible from the ListBenefits.aspx page.

HTTP Handler

When the employee navigates to the ListBenefits.aspx page, the benefit name is a hyperlink to download the Word document from the Forms directory. The path to the document might look like this, for example: http://localhost:2420/BenefitsPortal/Forms/Fitness Incentive Form.doc. An HTTP handler is registered in the Web.config file to handle all GET requests for .doc files in the Forms directory, as shown in the following code:

<httpHandlers>
    <_- Handle all document requests in the Forms directory. -->
    <add path="Forms/*.doc" verb="GET" 
        type="BenefitsRegistration.BenefitsPortal.BenefitFormHandler"
        validate="True" />
</httpHandlers>

The file sitting on the disk is the benefits registration form that you uploaded from the UploadForm page. The file references the customization assembly by using an application manifest. The role of the HTTP handler is to open the document, cache data about the employee and the benefit instructions from the database in the document, and then send it to the client browser.

The BenefitFormHandler class implements the IHttpHandler interface and overrides the ProcessRequest method, which the Web server calls when a document is requested. The following code shows this:

Public Sub ProcessRequest(ByVal context As System.Web.HttpContext) _
Implements System.Web.IHttpHandler.ProcessRequest
    Dim sd As ServerDocument = Nothing

    ' Open the file as a stream.
    Dim fs As New FileStream(formPath, FileMode.Open, _
        FileAccess.Read, FileShare.ReadWrite)
    ' Pass the file name and extension indicating the type of document.
    sd = New ServerDocument(fs, Path.GetFileName(formPath))

    ' Get the object that hosts the cached data items.
    Dim hostItem As CachedDataHostItem = Nothing

    If (sd.CachedData.HostItems("BenefitForm.ThisDocument") Is Nothing) _
            Then
        hostItem = sd.CachedData.HostItems.Add("BenefitForm.ThisDocument")
    Else
        hostItem = sd.CachedData.HostItems("BenefitForm.ThisDocument")
    End If

    ' Populate the data sets.
    Dim benefitDataSet As Benefit = _
        GetBenefits(Path.GetFileName(formPath))

    ' Cache benefit data set in the document.
    If (benefitDataSet IsNot Nothing And _
             benefitDataSet._Benefit.Rows.Count > 0) Then
        CacheDataSet(hostItem, "benefitDataSet", benefitDataSet)
    Else
        Throw New HttpException(500, "Could not load benefit data" & _
            " for the form requested.")
    End If

    Dim employeeDataSet As Employee = GetEmployees(userName)

    ' Cache employee data set in the document.
    If (employeeDataSet IsNot Nothing And _
             employeeDataSet._Employee.Rows.Count > 0) Then
        CacheDataSet(hostItem, "employeeDataSet", employeeDataSet)
    Else
        Throw New HttpException(500, "Could not load employee" & _
            " data for the form requested.")
    End If

    ' Save cached data to the stream.
    sd.Save()

    ' Write the document to the client HTTP stream.        
    WriteHttpResponse(context.Response, sd.Document)

End Sub

public void ProcessRequest(HttpContext context)
{
    ServerDocument sd = null;

    // Open the file as a stream.
    using (FileStream fs = new FileStream(formPath, FileMode.Open,
        FileAccess.Read, FileShare.ReadWrite))
    {
        // Pass the file name and extension indicating the type of document.
        sd = new ServerDocument(fs, Path.GetFileName(formPath));
    }
    
    // Get the object that hosts the cached data items.
    CachedDataHostItem hostItem = null;

    if (sd.CachedData.HostItems["BenefitForm.ThisDocument"] == null)
    {
        hostItem =
            sd.CachedData.HostItems.Add("BenefitForm.ThisDocument");
    }
    else
    {
        hostItem =
            sd.CachedData.HostItems["BenefitForm.ThisDocument"];
    }

    // Populate the benefit data set.
    Benefit benefitDataSet = 
        GetBenefitDataSet(Path.GetFileName(formPath));

    // Cache benefit data set in the document.
    if (benefitDataSet != null &&
        benefitDataSet._Benefit.Rows.Count > 0)
    {
        CacheDataSet(hostItem, "BenefitDataSet", benefitDataSet);
    }
    else
    {
        throw new HttpException(500, "Could not load benefit data" +
            " for the form requested.");
    }

    // Populate the employee data set.
    Employee employeeDataSet = GetEmployeeDataSet(userName);

    // Cache employee data set in the document.
    if (employeeDataSet != null &&
        employeeDataSet._Employee.Rows.Count > 0)
    {
        CacheDataSet(hostItem, "EmployeeDataSet", employeeDataSet);
    }
    else
    {
        throw new HttpException(500, "Could not load employee" +
            " data for the form requested.");
    }

    // Save cached data to the stream.
    sd.Save();

    // Write the document to the client HTTP stream.        
    WriteHttpResponse(context.Response, sd.Document);
}

To cache data in the document, you get a reference to a CachedDataItem from the document. You then call its SerializeDataInstance method, passing the XML object you want to cache. These steps are in the CacheDataSet method shown in the following code:

Private Sub CacheDataSet(ByVal hostItem As CachedDataHostItem, _
ByVal datasetName As String, ByVal ds As Data.DataSet)

    ' Create the item if it's not already available.
    Dim item As CachedDataItem = Nothing

    If (hostItem.CachedData(datasetName) Is Nothing) Then
        item = hostItem.CachedData.Add(datasetName, "")
    Else
        item = hostItem.CachedData(datasetName)
    End If

    item.SerializeDataInstance(ds)
End Sub

private static void CacheDataSet(CachedDataHostItem hostItem,
    string datasetName, DataSet dataset)
{
    // Create the item if it's not already available.
    CachedDataItem item = null;

    if (hostItem.CachedData[datasetName] == null)
    {
        item = hostItem.CachedData.Add(datasetName, "");
    }
    else
    {
        item = hostItem.CachedData[datasetName];
    }

    item.SerializeDataInstance(dataset);
}

Because the ServerDocument class enables opening the document as a byte stream, you can modify the document in memory, and then write it directly to the HTTP response stream, as shown in the following code:

[Visual Basic]
WriteHttpResponse(context.Response, sd.Document)

[C#]
WriteHttpResponse(context.Response, sd.Document);

Consider all the input and output saved by being able to do this. In contrast, creating a copy of the document on the local disk, caching the data in it, saving it, and then opening it to send to the client is a terrible waste of time.

Opening Word in a New Window

Typically, when Internet Explorer downloads a Word document, it attempts to host Word inside of the Internet Explorer window and merge the menu and tool bars. Microsoft Windows allows you to change this behavior so that the document opens in a new Word window. However, if you have changed the default setting of the Browse in same window command for Word documents, a known issue with Word 2003 prevents the customization assembly from loading. You do not receive any errors, but when the benefits registration form downloads, no code executes.

To open Word in a new window

  1. Open any folder using Windows Explorer.

  2. On the Tools menu, click Folder Options.

  3. On the File Types tab, select DOC Microsoft Word Document.

  4. Click Advanced.

  5. In the Edit File Type dialog box, be sure that Browse in same window is selected.

  6. Click OK to close the dialog box.

  7. Click Close to close the Folder Options dialog box.

Register Web Service

The BenefitForm project calls the Register Web service to register an employee for a benefit. The Register Web service simply uses EmployeeBenefitTableAdapter to ensure that only one registration for an employee and benefit exists, and to record the registration in the database. The following code shows this:

tableAdapter.InsertEmployeeBenefit(employeeId, benefitId, DateTime.Now)

tableAdapter.InsertEmployeeBenefit(employeeId, benefitId, DateTime.Now);

If a registration already exists, the code replaces it with the latest registration.

ASP.NET Users

Although the main focus of the Benefits Portal application is to demonstrate what you can do with the new Visual Studio 2005 Tools for Office server capabilities, consider how easy it is to use some of the built-in authentication and authorization features in ASP.NET 2.0. Because the Benefits Portal application uses ASP.NET 2.0, you can set up users, roles, and access permissions from the built-in Web Site Administration tool. To access the tool, you select the BenefitsPortal project in Solution Explorer, and on the Website menu, click ASP.NET Configuration.

In ASP.NET, authentication and authorization use the same database used for employee benefits. This is done by overriding the default LocalSqlServer in the Web.config file, as shown in the following code:

<connectionStrings>
    <_- Override the default machine.config connection string.  -->
    <remove name="LocalSqlServer"/>
    <add name="LocalSqlServer" 
        connectionString="Data Source=.\SQLExpress;Integrated 
        Security=True;User Instance=True;
        AttachDBFilename=|DataDirectory|Benefits.mdf" />
</connectionStrings> 

Using Server Explorer in Visual Studio 2005 to examine the Benefits.mdf database, you see that ASP.NET automatically created several tables for authorization and personalization.

Figure 10. Benefits database


Benefits database

Creating New Employees

The Benefits Registration application does not provide an interface to create or edit employee records. You can create new users of the system in the ASP.NET configuration tool, but before you log on as that user, you must create a corresponding Employee record in the database because user logons are matched against existing employees. For referential integrity, the Employee.UserName must match the aspnet_Users.UserId, although matching is not enforced.

To create a record in the database, you can use Server Explorer. To do so, you right-click on the Employee table, and then select Show Table Data. Visual Studio starts a data editor table that you can use to add, edit, or remove employee information.

Deployment and Security Considerations

The Benefits Portal application is inherently a deployment vehicle for the BenefitForm customization assembly. For a user to browse to the application and download the latest assembly, you must be sure to publish the BenefitForm customization assembly to the Web folder. By default, Visual Studio builds the BenefitForm project to its local Debug or Release folder, located here: Bin\Debug or Bin\Release. Which folder it uses depends on the release type you choose.

The Web server makes files in the BenefitsPortal folder accessible. As you saw in the site map in Figure 3, the Web server deploys all forms to the BenefitsPortal\Forms directory. When a user requests a document from the Web server, Word loads the document and recognizes that a customization assembly is associated with it. Word reads the manifest that was created with ServerDocument.AddCustomization, which tells Word to attempt to download the application manifest from a published location. The application manifest file then directs Word to the files it must download to run the Benefits Registration application.

NoteNote:

Visual Studio 2005 Tools for Office sometimes displays this ambiguous error: "The customization assembly could not be found or could not be loaded." This error might indicate that your assembly is not where you said it was or it might indicate a security exception.

Publish

In Visual Studio, the publish features handle the creation of the manifest files and deployment of the BenefitForm project. When you installed the Benefits Registration application, you received a set of published BenefitForm files in the Forms folder of this directory: Benefits Registration Sample\[CS or VB]\BenefitsPortal\Forms. Figure 11 shows this.

Figure 11. Published BenefitForm files


Published BenefitForm files

If you modify the BenefitForm project, then you also must publish a new version so that your modifications are recognized. When you publish a new version of the BenefitForm customization, Visual Studio updates the application manifest to the new location and creates a deployment folder for the new version, as shown in Figure 12. Visual Studio then copies updated versions of the application files to the deployment folder.

Figure 12. Published BenefitForm files for a new version


Published BenefitForm files for a new version

You can only publish to a Web location if that server has Microsoft Office FrontPage Server Extensions installed. Thus, it is not possible to publish the Benefits Registration application to the ASP.NET development Web server using an HTTP path on the Publish page in Visual Studio. To publish to a file path, you must modify the application manifest to use the HTTP path for downloading updated application files.

To modify and build the BenefitForm project

  1. In the BenefitForm project, add a MessageBox.Show with a message of your choice to the startup event of the ThisDocument class.

  2. On the Build menu, click Rebuild Solution.

To publish the BenefitForm project

  1. Right-click the BenefitForm project in Solution Explorer and click Publish.

  2. In the Publish Wizard, click Finish.

  3. Open BenefitsPortal\Forms\BenefitForm_1.0.0.1\BenefitForm.dll.manifest in any text editor.

  4. Change the codebase attribute of the installFrom element to reference BenefitForm.application using the HTTP path, as shown in the following code:

    For the Visual Basic application, use port 2288.

    <asmv2:installFrom codebase="http://localhost:2288/BenefitsPortal/Forms/BenefitForm.application" />
    
    

    For the C# application, use port 2420.

    <asmv2:installFrom codebase="http://localhost:2420/BenefitsPortal/Forms/BenefitForm.application" />
    
    
  5. Save your changes to BenefitForm.dll.manifest.

To test your changes

  1. Press F5 to run the application.

  2. Log on as an employee, and then click Click here to register for employee benefits.

  3. Select any one of the benefits registration forms.

  4. When the form opens in Word, your new message box appears.

    This confirms that the updated BenefitForm customization is loaded.

Code Access Security

The Benefits Registration application is a sample application that demonstrates the power of using the server capabilities of Visual Studio 2005 Tools for Office. Although the concepts and patterns described in this article are viable for real-world solutions, the code associated with this article is sample code and is not intended to be deployed to a production environment.

A discussion of code access security is outside the scope of this article. To learn more about security with Visual Studio 2005 Tools for Office, see Additional Resources. Assuming you have some of these fundamentals down, or are about to read up on them, Visual Studio 2005 Tools for Office customization assemblies require full trust to run. The Microsoft .NET Framework provides the Code Access Security Policy tool (Caspol.exe) for modifying code access security.

For the benefits registration form to execute on your development computer or a user's computer, the security policy must grant full trust to the assembly location. When you ran Setup.bat, the security policies required to run the application were created for you. Setup.bat simply wraps a call to Caspol.exe, as shown in the following code:

caspol -q -m -ag LocalIntranet_Zone –url
    http://localhost:2420/BenefitsPortal/* FullTrust -n 
    BenefitsPortal_VSTO_Sample_CS -d "Grants trust to the 
    MSDN Sample Benefits Portal (CS) Web site."

If you decide to access this sample from a computer other than your development computer, you must modify that command to reflect your computer name (instead of localhost), and then execute it on the computer onto which you intend to download a benefits registration form. The computer must be in the local intranet zone.

Likewise, if you change the port that Visual Studio uses for the development Web server, or switch to IIS, you must change the security policy that was created when you installed the application. You must modify the following command with your new port nnnn, and then execute it from a Visual Studio command prompt to change the existing security policy:

caspol -q -cg BenefitsPortal_VSTO_Sample_CS -url http://localhost:nnnn/BenefitsPortal/*

For more information about code security, see the Microsoft Security Developer Center.

For more information about deploying a Visual Studio 2005 Tools for Office application, see Deploying Visual Studio 2005 Tools for Office Solutions Using Windows Installer (Part 1 of 2) and Deploying Visual Studio 2005 Tools for Office Solutions Using Windows Installer: Walkthroughs (Part 2 of 2).

Changing the Development Web Server Port

The sample you have seen uses the Visual Studio 2005 ASP.NET Development Web Server. By default, the port is fixed to 2420 for the C# application and 2288 for the Visual Basic application. In your environment, you may have to change the port Visual Studio uses for the Development Web Server, if you have something else on that port.

To change the port for the Benefits Portal application

  1. In Visual Studio, select the BenefitsPortal project in Solution Explorer.

    The Properties window displays project settings like those shown in Figure 13.

  2. If you do not see the Properties window, click Properties Window on the View menu.

    Figure 13. The Properties window in the Benefits Portal application


    The Properties window in the application
  3. Change the port number to an available port.

    To determine which ports are currently used, execute the following code in a command window:

    netstat –p TCP –a
    
  4. Ensure that Use dynamic ports is set to False.

    Dynamic ports tell Visual Studio to find and use an available port. The disadvantage with using a dynamic port is that each time the port changes, you must change the security policy and configuration files.

To modify configuration files

  1. In the Web.config file in the BenefitsPortal project, update the value of the ManifestPath application setting.

  2. Right-click BenefitForm in Solution Explorer, and then click Properties.

  3. On the Settings tab, update the URLs for the BenefitForm_RegisterWebService_Register and BenefitsPortalUrl settings.

To publish BenefitForm and update security settings

  1. Publish BenefitForm as previously described. Remember to update the application manifest with the correct URL.

  2. Add security settings using your URL as previously described.

To upload updated benefits registration forms

The existing benefits registration forms that you uploaded to the Benefits Portal rely on localhost:2420 or localhost:2288. As noted earlier, when you execute the ServerDocument.AddCustomization method, you pass the application manifest URL that you modified in the Web.config file.

  1. Make a copy of the forms in the BenefitsPortal\Forms directory.

  2. Run the Benefits Portal application, and log on as admin.

  3. On the List Benefits page, click Delete on each form.

  4. To store the updated manifest location in the documents, use the Upload page to upload each form.

Conclusion

The server capabilities in Visual Studio 2005 Tools for Office make it possible to change the document customization paradigm. They enable you to build a single solution that you can attach to any document as needed, without having Microsoft Office installed on the server.

Although the Benefits Registration application described in this article applies specifically to a human resource department's benefits registration system, the general scenario has broad reach. For example, consider an insurance company that must fill thousands of forms with policy numbers, names, addresses, and so on. Plenty of applications use other methodologies to populate forms on the server. Some modify documents as raw XML, while others install Word on the server, and then iterate through fields collections. Visual Studio 2005 Tools for Office provides an elegant, efficient way to accomplish these tasks on the server.

Additional Resources

About the Authors

J. Jason De Lorme is a principal consultant with Simplesheet, Inc., a software architecture and development firm specializing in solutions built with the Microsoft .NET Framework and Microsoft Office 2003 Editions. As an early adopter, he enjoys working with new products, especially when it involves .NET Framework.

Lori Turner is a developer consultant for Microsoft Services with many years of expertise in Office development. Lori has been with Microsoft for more than 12 years. She enjoys assisting Microsoft customers and writing Microsoft Office solutions with .NET technologies.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.