Lab 6: Using XML and C# in Word Solutions

  • Article

 

Lori Turner
Microsoft Corporation

March 2004

Applies to:
    Microsoft® Visual Studio® Tools for the Microsoft Office System
    Microsoft Office Professional Edition 2003
    Microsoft Visual C#® .NET 2003

Summary: Demonstrates how you can populate and retrieve data from XML nodes in a Word document at run time using Visual C# code and Visual Studio Tools for the Microsoft Office System. (29 printed pages)

Download the VSTOLabs.exe sample file.

Contents

Introduction
Prerequisites
Getting Started
Exercise 1: Using XML in a Word Document
Next Steps

Introduction

The objective of this lab is to illustrate how you can work with XML in Microsoft® Office Word 2003 using Microsoft Visual C#®. In the lab, you create a Word Document project using Microsoft Visual Studio® Tools for the Microsoft Office System. You create an XML schema in Microsoft Visual Studio .NET and attach that XML schema to a document. Additionally, you add XML nodes to the document and populate and retrieve data from the document's XML nodes at run time.

Estimated time to complete:

  • Exercise 1: Using XML with Word in Visual C# - 50 minutes

For a link to all labs in this series, see Visual Studio Tools for the Microsoft Office System Training.

Note   The labs in this series are designed to be completed in order.

Prerequisites

To complete these exercises, you need:

  • Microsoft Visual Studio Tools for the Microsoft Office System.
  • Microsoft Office Word 2003, installed from Microsoft Office Professional Edition 2003.
  • Access to a server with the Northwind SQL Server sample database.
  • Sufficient permissions to read from and write to the SQL Server database.

Getting Started

Start this lab with a new Visual Studio Tools for Office project:

  1. On the File menu in Visual Studio .NET, click New, and then click Project.

    The New Project dialog box appears.

  2. In the Project Types list, expand Microsoft Office System Projects and click Visual C# Projects.

  3. Select the Word Document project type.

  4. Type CustomerForm for the project name, type C:\Labs\Lab6 for the project location, and click OK.

    The Office Project Wizard appears.

  5. Click Finish.

Exercise 1: Using XML in a Word Document

This exercise includes the following tasks:

  • Build a command bar and command bar controls.
  • Create and use Northwind customer data as XML.
  • Create an XML schema for the Customers table.
  • Attach the XML schema to the Word document.
  • Add XML tags to the Word document.
  • Populate XML nodes at run time.
  • Extract XML from the document for updates to the Customers table.

Task 1: Build a Command Bar and Command Bar Controls

To start this lab, you create a command bar with controls as illustrated in Figure 1. The command bar and its controls provide all the functionality needed for the end user to retrieve and save data to the Northwind Customers table.

Figure 1. Customer command bar

In C#, you cannot omit optional parameters to methods like you can with Visual Basic .NET. Instead, with C#, you can use System.Reflection.Missing with optional parameters.

To build the command bar

  1. Add oMissing as a class-level variable in the OfficeCodeBehind class:

    // C#
private object oMissing = System.Reflection.Missing.Value;

  2. Add class-level variables to the OfficeCodeBehind class for the CommandBar and its controls:

    // C#
private Office.CommandBar cbar = null;
private Office.CommandBarButton cbSaveAsNew = null;
private Office.CommandBarButton cbEdit = null;
private Office.CommandBarButton cbSave = null;
private Office.CommandBarButton cbClear = null;
private Office.CommandBarComboBox cbCustomerList = null;

    The SetupCommandBar function creates the CommandBar and its controls.****

  3. Add the SetupCommandBar function to the OfficeCodeBehind class:

    // C#
private bool SetupCommandBar()
{
    try
    {
        // Create a new command bar in the context of this document.
        ThisApplication.CustomizationContext=ThisDocument;
        cbar = ThisApplication.CommandBars.Add("Customer Form", 
            oMissing, oMissing, (object)true);

        // Add a button to the command bar for adding a new customer.
        cbSaveAsNew = (Office.CommandBarButton)(cbar.Controls.Add(
            (object)1, oMissing, oMissing, oMissing, oMissing));
        cbSaveAsNew.Style=Office.MsoButtonStyle.msoButtonCaption;
        cbSaveAsNew.Caption = "Save As New";

        // Add a combo box to the command bar for selecting a customer.
        object objType = Office.MsoControlType.msoControlDropdown;
        cbCustomerList = (Office.CommandBarComboBox)(
            (cbar.Controls.Add(objType, oMissing, oMissing,
            oMissing, (object)true)));
        cbCustomerList.Caption = "Select a Customer:";
        cbCustomerList.Style = Office.MsoComboStyle.msoComboLabel;
        cbCustomerList.ListIndex = 0;
        cbCustomerList.BeginGroup=true;

        // Add a button to the command bar for changing customer
        // data.
        cbEdit = (Office.CommandBarButton)(cbar.Controls.Add(
            (object)1, oMissing, oMissing, oMissing, oMissing));
        cbEdit.Style=Office.MsoButtonStyle.msoButtonCaption;
        cbEdit.Caption = "Edit";

        // Add a button to the command bar for saving changes
        // to customer data; this button is disabled until a
        // customer is selected from the combo box.
        cbSave = (Office.CommandBarButton)(cbar.Controls.Add(
            (object)1, oMissing, oMissing, oMissing, oMissing));
        cbSave.Style=Office.MsoButtonStyle.msoButtonCaption;
        cbSave.Caption = "Save";
        cbSave.Enabled=false;

        // Add a button to the command bar for clearing the customer
        // data on the form.
        cbClear = (Office.CommandBarButton)(cbar.Controls.Add(
            (object)1, oMissing, oMissing, oMissing, oMissing));
        cbClear.Style=Office.MsoButtonStyle.msoButtonCaption;
        cbClear.Caption = "Clear";
        cbClear.BeginGroup = true;

        // Make the new command bar visible.
        cbar.Visible=true;

        return true;
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error creating CommandBar: " + ex.Message, 
            "Customer Form", MessageBoxButtons.OK,
            MessageBoxIcon.Error);
        return false;
    }

}

The next step in building the command bar is to wire the events for the command bar buttons.

To hook up command bar button events

  1. First, declare your event delegate variables at class-level in the OfficeCodeBehind class:

    // C#
private Office._CommandBarButtonEvents_ClickEventHandler
   cbSaveAsNewClickEvent, cbEditClickEvent, cbSaveClickEvent,
   cbClearClickEvent;

  2. Add the following event handlers to the OfficeCodeBehind class.

    **Note   **You add code to each handler as you proceed through this lab.

    // C#
private void cbSaveAsNew_Click(Office.CommandBarButton btn,
    ref bool Cancel)
{

}

private void cbEdit_Click(Office.CommandBarButton btn, ref bool Cancel)
{

}

private void cbSave_Click(Office.CommandBarButton btn, ref bool Cancel)
{

}

private void cbClear_Click(Office.CommandBarButton btn, ref bool Cancel)
{

}

    The WireButtonClickEvents function does the work of wiring up your event delegates to the handlers.

  3. Add the WireButtonClickEvents function to the OfficeCodeBehind class:

    // C#
private void WireButtonClickEvents()
{
    // Set up the Click events for the command bar buttons.
    cbSaveAsNewClickEvent = 
        new Office._CommandBarButtonEvents_ClickEventHandler(
        cbSaveAsNew_Click);
    cbSaveAsNew.Click += cbSaveAsNewClickEvent;
    cbEditClickEvent = 
        new Office._CommandBarButtonEvents_ClickEventHandler(
        cbEdit_Click);
    cbEdit.Click += cbEditClickEvent;
    cbSaveClickEvent = 
        new Office._CommandBarButtonEvents_ClickEventHandler(
        cbSave_Click);
    cbSave.Click += cbSaveClickEvent;
    cbClearClickEvent = 
        new Office._CommandBarButtonEvents_ClickEventHandler(
        cbClear_Click);
    cbClear.Click += cbClearClickEvent;
}

    When the document is opened, the command bar is built and the Click events for all the command bar buttons are wired.

  4. Add the following code to the Open event of the document:

    // C#
protected void ThisDocument_Open()
{
    // Set up command and wire the command bar button event delegates
    // to the Click event handlers.
    if(!SetupCommandBar()) return;
    WireButtonClickEvents();
}

Testing the Application

Try it out:

  1. Press F5 to build and run the project.

    When CustomerForm.doc opens, the new Customer Form command bar appears.

  2. Create a new blank document in this instance of Word.

    Notice that the Customer Form command bar does not appear.

  3. Switch to CustomerForm.doc.

    Notice that the Customer Form command bar reappears. Why? Prior to adding the new Customer Form command bar in the SetupCommandBar function, you set the Word application CustomizationContext to ThisDocument. This indicates that the Customer Form command bar applies only to that document.

  4. Close CustomerForm.doc without saving changes, and quit Word.

Task 2: Create and Use Northwind Customer Data as XML

The assembly for this lab accesses data in the Northwind SQL Server sample database to read and write data from the Customers table. All the data access to SQL Server is placed in a single class module, named clsDataAccess.

The first function you add to clsDataAccess is a function that builds XML from the list of CustomerIDs in the Customers table. Word then traverses the nodes in the XML to populate the command bar combo box list as illustrated in Figure 2.

Figure 2. Combo box populated with data

To populate the combo box with data

  1. On the Project menu, click Add Class.

  2. Name the new class clsDataAccess.cs, and click OK.

  3. Add the following using statements to clsDataAccess.cs:

    // C#
using System.Data;
using System.Data.SqlClient;

  4. Add the following class-level variables to clsDataAccess:

    // C#
string sConn = 
    "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;";

    **Note   **The connection string assumes that the Northwind SQL Server database is on your local computer. If the database is on another computer, change the data source in the connection string appropriately.

    The GetCustomers member of clsDataAccess retrieves a list of all CustomerIDs from the Customers table as a dataset and returns an XML string representing the dataset to the caller.

  5. Add GetCustomers to clsDataAccess:

    // C#
public string GetCustomers()
{
    try
    {
        // Connect to the data source.
        SqlConnection Conn = new SqlConnection(sConn);
        Conn.Open();

        // Build a dataset containing all the Customer IDs.
        SqlDataAdapter da = new SqlDataAdapter(
            "SELECT CustomerID FROM Customers", Conn);
        DataSet ds = new DataSet();
        da.Fill(ds);

        // Write the dataset to XML and return to the caller
        // as a string.
        System.IO.StringWriter sw = new System.IO.StringWriter();
        ds.WriteXml(sw, System.Data.XmlWriteMode.IgnoreSchema);
        return sw.ToString();
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
}

  6. Add the following using statements to ThisDocument.cs**:**

    // C#
using System.Xml;
using System.IO;

    The FillCustomerList function calls the GetCustomers member of clsDataAccess to acquire the CustomerID list as XML and then uses an XmlTextReader to traverse the nodes in the XML. The text in each CustomerID node is added to the list in the command bar combo box.

  7. Add the FillCustomerList function to the OfficeCodeBehind class:

    // C#
private void FillCustomerList()
{
    // Clear the combo box list.
    cbCustomerList.Clear();    

    // Obtain the list of Customer IDs as XML.
    clsDataAccess da = new clsDataAccess();
    StringReader sr = new StringReader(da.GetCustomers());
    XmlTextReader xtr = new XmlTextReader(sr);

    // Read the CustomerID nodes in the XML and then add the
    // values in each node to the combo box list.
    while(xtr.Read())
    {
        if ((xtr.NodeType==XmlNodeType.Element) &
            (xtr.Name=="CustomerID"))
        {
            string sCustomerID = xtr.ReadString();
            cbCustomerList.AddItem(sCustomerID, oMissing);
        }
    }
    xtr.Close();
    sr.Close();
}

  8. Append the following call to ThisDocument_Open:

    // C#
FillCustomerList();

Testing the Application

Try it out:

  1. Press F5 to build and run the project.
  2. Examine the contents of the combo box on the command bar; the combo box list contains CustomerIDs as illustrated in Figure 2.
  3. Close CustomerForm.doc without saving changes and quit Word.

Task 3: Create an XML Schema for the Customers Table

Using the Visual Studio .NET development environment, create an XML schema for the Customers table in the Northwind database:

To create an XML schema

  1. On the Project menu, click Add Component.

  2. In the Add New Item dialog box, select the XML Schema template, name it customer.xsd (all lowercase), and click Open.

  3. On the View menu, click Server Explorer.

  4. In Server Explorer, navigate to the Northwind database for your SQL Server and expand the Tables nodes.

  5. Select Customers under the Tables node, and drag it onto the yellow designer surface for customer.xsd.

    The resulting XML Schema is illustrated in Figure 3.

    Figure 3. XML Schema

Now create a simple type for each element in the Customers node and specify a maxLength for each element so that the element string values conform to the Customers data definition in Northwind.

To create a simple type for each element

  1. In Schema view, change the type for the CustomerID element to Unnamed SimpleType.

    This creates a new SimpleType that is nested in the CustomerID element.

  2. In the new SimpleType for the CustomerID element, change maxLength to 5 as illustrated in Figure 4.

    Figure 4. Specifying maxLength

  3. Repeat the previous two steps for the remaining elements in the Customers node. The maxLength property for each element's simple is shown in Table 1.

    **Note   **The maxLength property matches the length of the field as it is defined in SQL Server. Setting the maxLength field enables you to perform validation on the field length in the XML in Word.

    Table 1. The maxLength property for each element

    Element Name maxLength
    CustomerID 5
    CompanyName 40
    ContactName 30
    ContactTitle 30
    Address 60
    City 15
    Region 15
    PostalCode 10
    Country 15
    Phone 24
    Fax 24

  4. Save customer.xsd.

    **Note   **You can compare your XML schema with the customer.xsd in the folder C:\Labs\Files.

Task 4: Attach the XML Schema to the Word Document

Next you attach the XML schema, customer.xsd, to CustomerForm.doc.

To attach the schema to the document

  1. Open CustomerForm.doc in Word.

  2. On the Tools menu, click Templates and Add-ins.

  3. On the XML Schema tab, click Add Schema.

  4. Browse to C:\Labs\Lab6\CustomerForm\customer.xsd, and click Open.

  5. In the Schema Settings dialog box, enter Customer for the Alias, and click OK.

    The Customer schema now appears in the list of available XML Schemas in the Templates and Add-ins dialog box.

  6. Select Customer, and then click OK to attach the XML schema to your document.

  7. Save CustomerForm.doc and quit Word.

  8. In Windows Explorer, select the Read-Only attribute for CustomerForm.doc.

Task 5: Add XML Tags to the Word Document

You could use the XML Structure task pane to add your XML tags from the customer.xsd schema at design time in Word and save the changes to your document. However, the purpose of this section is to illustrate how you can add and manipulate the XML tags in a document at run time. When the document opens, you programmatically insert the XML tags from the attached Customer schema into cells of a new table as illustrated in Figure 5.

Figure 5. XML tags inserted into a table

In addition to building the document on Open, you also wire the DocumentBeforeClose and DocumentBeforeSave events of the Word Application. The DocumentBeforeClose event of the Application (unlike the Close event of the Document) has a Cancel parameter that you can use to cancel the closing of a document. DocumentBeforeClose is useful when you need to override the document closing with custom save or close functionality as demonstrated in this section.

The BuildDoc function creates a table in the document and adds the XML tags to cells in the table. Form fields are inserted inside each XML node, and the document is protected so that only the form-field results can be modified by the user.

To programmatically add XML tags to the document on Open

  1. Add the BuildDoc function to the OfficeCodeBehind class:

    // C#
private bool BuildDoc()
{
    try
    {
        // Add the Document and Customers nodes.
        Word.XMLNodes nodes = ThisDocument.XMLNodes;

        string ns = "http://tempuri.org/customer.xsd";
        object rng = ThisDocument.Content;
        Word.XMLNode oDSNode = nodes.Add(
            "Document", ns, ref rng);
        rng = oDSNode.Range;
        Word.XMLNode oTblNode = nodes.Add( 
            "Customers", ns, ref rng);

        // Insert an 11x2 table inside the Customers node.
        Word.Table tbl = ThisDocument.Tables.Add(
            oTblNode.Range, 11, 2, ref oMissing, ref oMissing);

        // Format the table.
        Word.Borders brdrs = tbl.Columns[2].Cells.Borders;
        brdrs[Word.WdBorderType.wdBorderHorizontal].LineStyle = 
            Word.WdLineStyle.wdLineStyleSingle;

        // Fill the cells in the table:
        //   Column 1: Label
        //   Column 2: XMLNode and a Form field for fill-in.
        string[] aNames = new string [11]{"CustomerID", "CompanyName",
            "ContactName", "ContactTitle", "Address", "City", "Region",
            "PostalCode", "Country", "Phone", "Fax"};
        for(int i=0;i<=10;i++)
        {
            tbl.Cell(i+1,1).Range.Text = aNames[i];
            rng = tbl.Cell(i+1,2).Range;
            Word.XMLNode node = nodes.Add(aNames[i], ns, ref rng);
            ThisDocument.FormFields.Add(
                node.Range, Word.WdFieldType.wdFieldFormTextInput);
        }                
        ThisDocument.FormFields.Shaded=false;

        // Turn on mixed content.
        ThisDocument.XMLSchemaReferences.IgnoreMixedContent = true;

        // Hide table gridlines and protect the document.
        ThisDocument.ActiveWindow.View.TableGridlines = false;
        ThisDocument.Protect(
            Word.WdProtectionType.wdAllowOnlyFormFields, 
            ref oMissing, ref oMissing, ref oMissing, ref oMissing);

        return true;
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error building document: " + ex.Message, 
            "Customer Form", MessageBoxButtons.OK,   
            MessageBoxIcon.Error);
        return false;
    }
}

  2. Add the event delegates for handling the DocumentBeforeClose and DocumentBeforeSave events of the Application at class-level in the OfficeCodeBehind class:

    // C#
private Word.ApplicationEvents4_DocumentBeforeCloseEventHandler AppDocCloseEvent;
private Word.ApplicationEvents4_DocumentBeforeSaveEventHandler AppDocSaveEvent;

    In your DocumentBeforeClose handler, you prompt the user to confirm the close if the document is "dirty" (has been changed) and handle the close based on their response.

  3. Add the DocumentBeforeClose event handler to the OfficeCodeBehind class:

    // C#
private void App_DocClose(Word.Document Doc, ref bool Cancel)
{
    // Do not proceed if the document closing is not ThisDocument.
    if(!(ThisDocument == Doc)) return;

    // If the document is dirty, determine if the user wants
    // to discard their changes to the Customer data
    // before continuing with the document close.
    if(!ThisDocument.Saved)
    {
        DialogResult ret = MessageBox.Show("You have not " +
            "submitted your changes to the customer data. Close " +
            "this document without submitting your changes?",
            "Customer Form", MessageBoxButtons.YesNo, 
            MessageBoxIcon.Question);
        if(ret==DialogResult.Yes)
        {
            ThisDocument.Saved = true;
        }
        else
        {
            Cancel = true;
        }
    }
    return;
}

    In your DocumentBeforeSave handler, you notify the user to use the Customer Form command bar for saving their changes, and then you cancel the save.

  4. Add the DocumentBeforeSave event handler to the OfficeCodeBehind class:

    // C#
private void App_DocSave(Word.Document Doc, ref bool SaveAsUI, 
    ref bool Cancel)
{
    MessageBox.Show("Please save changes using the Customer Form " +
        "CommandBar.", "Customer Form", MessageBoxButtons.OK, 
        MessageBoxIcon.Information);
    Cancel = true;
}

    You wire the Application events and create the table, XML tags, and form fields in the document when the document opens.

  5. Append the following code to ThisDocument_Open:

    // C#
// Set up event handling for closing and saving the document.
AppDocCloseEvent = 
    new Word.ApplicationEvents4_DocumentBeforeCloseEventHandler(
    App_DocClose);
ThisApplication.DocumentBeforeClose += AppDocCloseEvent;
AppDocSaveEvent = new 
    Word.ApplicationEvents4_DocumentBeforeSaveEventHandler(
    App_DocSave);
ThisApplication.DocumentBeforeSave += AppDocSaveEvent;

// Add the XML tags to the document and apply formatting to 
// the document.
if(!BuildDoc()) return;
ThisDocument.Saved = true;

Testing the Application

Try it out:

  1. Press F5 to build and run the project.

  2. When the document opens, it appears as depicted in Figure 5.

  3. Press CTRL+SHIFT+X to toggle the display of XML tags in your document.

  4. Add data to the Form Fields in the document.

    **Note   **Since the document is protected, you can only add text to the Form Fields within each XML tag. If you wish to unprotect the document, click UnprotectDocument on the Tools menu.

  5. On the File menu, click Save.

    The DocumentBeforeSave event handler is called to display a message and cancel the save.

  6. On the File menu, click Close.

    The DocumentBeforeClose event handler is called to confirm that you want to close the document without submitting your changes.

  7. Click Yes.

  8. Quit Word.

Task 6: Populate XML Nodes at Run Time

In this section, you populate the form fields in the document's XML nodes in one of two ways:

  • By clearing the form field results when the user clicks the Clear command bar button, or
  • By filling the form field results with data for the customer selected in the combo box on the command bar.

The GetCustomerRecord takes a CustomerID as a string parameter and returns an XML string representing the dataset for that customer.

To populate XML nodes at run time

  1. Add the GetCustomerRecord to the clsDataAccess class:

    // C#
public string GetCustomerRecord(string sCustID)
{
    try
    {
        // Connect to the data source.
        SqlConnection Conn = new SqlConnection(sConn);
        Conn.Open();

        // Create a dataset for the customer record with the 
        // given CustomerID.
        SqlDataAdapter da = new SqlDataAdapter( String.Format( 
            "Select * From Customers Where CustomerID='{0}'", sCustID),
            Conn);
        DataSet ds = new DataSet();
        ds.DataSetName = "Document";
        da.Fill(ds, "Customers");

        // Return the dataset to the caller as an XML string.
        System.IO.StringWriter sXML = new System.IO.StringWriter();
        ds.WriteXml(sXML,XmlWriteMode.IgnoreSchema);
        sXML.Close();
        return sXML.ToString();
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
}

    The ClearNodeText function clears the form field result in each XML node in the document.

  2. Add the ClearNodeText function to the OfficeCodeBehind class:

    // C#
private bool ClearNodeText()
{
    try
    {
        // If the document is dirty, verify that the user 
        // wants to clear the form.
        if(!ThisDocument.Saved)
        {
            DialogResult ret = MessageBox.Show(
                "Discard your current edits?", "Customer Form", 
                MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if(ret==DialogResult.No) return false;
        }

        // Iterate the child nodes of the Customers node 
        // in the document and set the Result of each 
        // FormField to a null string.
        Word.XMLNodes nodes = ThisDocument.SelectNodes(
            "/Customer:Document/Customer:Customers/*",
            "xmlns:Customer=\"http://tempuri.org/customer.xsd\"", 
            false);        
        object o = 1;
        for(int i=1;i<=nodes.Count;i++)
        {
            Word.XMLNode node = nodes[i];
            node.Range.FormFields.get_Item(ref o).Result = "";
        }

        return true;
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
        return false;
    }
}

  3. Add code to the cbClear_Click event handler to clear the form fields in the document's XML nodes when the commandbar button is clicked:

    // C#
    ClearNodeText();
    cbSaveAsNew.Enabled=true;
    cbSave.Enabled=false;
    ThisDocument.Saved = true;
    return;

    When the Edit button on the Customer Form command bar is clicked, you determine which CustomerID the end user selected and pass that as a string parameter to the GetCustomerRecord member of clsDataAccess. You load the XML string returned by GetCustomerRecord into an XMLDocument so that you can traverse the nodes in the XMLDocument and add the node text to the Customer Form.

  4. Add the following code to the cbEdit_Click event handler:

    // C#
    try
    {
        if(!ClearNodeText()) return;        

        // Get a string for the selected customer ID.
        string sCustID = 
            cbCustomerList.get_List(cbCustomerList.ListIndex);

        // Load the data for the selected customer into an
        // XML document.
        clsDataAccess da = new clsDataAccess();
        System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
        xmlDoc.LoadXml(da.GetCustomerRecord(sCustID));

        // Iterate the nodes in the data and populate the XMLNodes
        // in the document.
        string sPrefix = 
            "xmlns:Customer=\"http://tempuri.org/customer.xsd\"";
        object o = 1;
        XmlNodeList nodes = 
            xmlDoc.SelectNodes("/Document/Customers/*");
        foreach (XmlNode node in nodes)
        {        
            string sXPath = String.Format(
                "/Customer:Document/Customer:Customers/Customer:{0}",
                node.Name);
            Word.XMLNode wordNode = ThisDocument.SelectSingleNode(
                sXPath, sPrefix, false);
            wordNode.Range.FormFields.get_Item(ref o).Result = 
                node.InnerText;
        }

        ThisDocument.Saved = true;
        cbSave.Enabled=true;
        cbSaveAsNew.Enabled=false;

        return;
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error modifying customer data: " + ex.Message, 
            "Customer Form", MessageBoxButtons.OK, 
            MessageBoxIcon.Error);
        return;
    }

Testing the Application

Try it out:

  1. Press F5 to build and run the project.

  2. Select any CustomerID in the combo box on the Customer Form command bar and click Edit.

    The form in the document is populated with the data for the customer you selected.

  3. Modify the form fields in the document.

  4. Click Clear on the command bar to clear the form on the document.

  5. Quit Word.

Task 7: Extract XML from the Document for Updates to the Customers Table

Last but not least, you add code to handle the Save and Save As New command bar buttons to perform UPDATEs and INSERTs in the Northwind Customers table. For the updates, you validate the XML in the document, extract the XML data only from the document, and call a function, UpdateCustomer, in clsDataAccess. UpdateCustomer builds and executes the command to perform the update.

An enumeration is used to distinguish between SQL INSERTs and UPDATEs.

To extract XML from the document and update the table

  1. Add the EditType enumeration at class-level to clsDataAccess:

    public enum EditType{Insert, Update};

    The UpdateCustomer function performs the actual updates to the Customers table in Northwind. UpdateCustomer takes an EditType and an XML string as parameters and performs either an INSERT or UPDATE with the data represented in the XML string.

  2. Add UpdateCustomer to the clsDataAccess class:

    // C#
public bool UpdateCustomer(EditType edt, string sXML)
{
    try
    {
        // Build a dataset from the XML string passed in by the caller.
        System.IO.StringReader sr = new System.IO.StringReader(sXML);
        DataSet ds = new DataSet();
        ds.ReadXml(sr);                
        sr.Close();

        // Connect to the data source and create a new SqlCommand for
        // that connection.
        SqlConnection Conn = new SqlConnection(sConn);
        Conn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = Conn;

        // A string array for the Customers field names; 
        // this array helps in building the SQL Commands 
        // for INSERTs or UPDATEs.
        string[] aFields = new string [11]{"CustomerID", "CompanyName",
                "ContactName", "ContactTitle", "Address", "City", 
                "Region", "PostalCode", "Country", "Phone", "Fax"};

        switch (edt)
        {
            case EditType.Insert:
                // If the EditType is an Insert, build an INSERT 
                // SQL Command.
                string sInsertCmd = String.Format(
                    "INSERT INTO Customers " +
                    "({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, " + 
                    "{8}, {9}, {10}) VALUES (@{0}, @{1}, @{2}, " + 
                    "@{3}, @{4}, @{5}, @{6}, @{7}, @{8}, @{9}, " +
                    "@{10})", aFields);
                cmd.CommandText = sInsertCmd;
                break;

            case EditType.Update:                    
                // If the EditType is an Update, build an UPDATE 
                // SQL Command.
                string sUpdateCmd = String.Format(
                    "UPDATE Customers SET " +
                    "{1}=@{1}, {2}=@{2}, {3}=@{3}, " +
                    "{4}=@{4}, {5}=@{5}, {6}=@{6}, " +
                    "{7}=@{7}, {8}=@{8}, {9}=@{9} , {10}=@{10} " +
                    "WHERE {0}=@{0}", aFields);
                cmd.CommandText = sUpdateCmd;
                break;
        }

        // Fill the SQL Command parameters using the values in 
        // the dataset.
        for(int f=0;f<=10;f++)
        {
            SqlParameter parm = cmd.Parameters.Add(
                "@" + aFields[f], SqlDbType.Char);
            parm.Value = ds.Tables[0].Rows[0][aFields[f]];
        }

        // Execute the command and close then connection.
        cmd.ExecuteNonQuery();
        Conn.Close();

        return true;
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
}

    On the Word side, prior to submitting the customer data for an update, you call the Validate method for each Word.XMLNode in the document. If a validation error occurs, you display the text of the validation error and return false to the caller; otherwise, if no validation error occurs, you return true to the caller.

  3. Add ValidateCustomer to the OfficeCodeBehind class:

    // C#
private bool ValidateCustomer()
{
    //Validate the data entered and, if not valid, display the
    //name of the invalid node + the validation error text.
    Word.XMLNodes nodes = ThisDocument.XMLNodes;
    Word.XMLNode node;
    for(int i=1;i<=nodes.Count;i++)
    {
        node = nodes[i];
        node.Validate();
        if (node.ValidationStatus !=
            Word.WdXMLValidationStatus.wdXMLValidationStatusOK)
        {
            MessageBox.Show(node.BaseName + ": " + 
                node.get_ValidationErrorText(false), "Customer Form",
                MessageBoxButtons.OK, MessageBoxIcon.Warning);
            return false;
        }
    }
    return true;
}

    When the user clicks the Save button on the command bar, you validate the XML in the document and, if the XML is valid, pass the XML to UpdateCustomer in clsDataAccess with EditType.Update (a SQL UPDATE).

  4. Add to your Click event handler for the cbSave command bar button:

    // C#
private void cbSave_Click(Office.CommandBarButton btn, ref bool Cancel)
{
    try
    {
        if(ValidateCustomer())
        {
            // If no validation errors occurred, try adding the data.
            clsDataAccess da = new clsDataAccess();
            da.UpdateCustomer(clsDataAccess.EditType.Update,
                ThisDocument.XMLNodes[1].get_XML(true));

            // Mark the document as not "dirty."
            cbSave.Enabled = false;
            ThisDocument.Saved = true;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error updating customer data: " + ex.Message, 
            "Customer Form", MessageBoxButtons.OK, MessageBoxIcon.Error);
        return;
    }
}

    When the user clicks the Save As New button, you validate the XML in the document and, if the XML is valid, pass the XML to UpdateCustomer in clsDataAccess with EditType.Insert (a SQL INSERT).

  5. Add to your Click event handler for the cbSaveAsNew command bar button:

    // C#
private void cbSaveAsNew_Click(Office.CommandBarButton btn, ref bool Cancel)
{
    try
    {
        if(ValidateCustomer())
        {
            // If no validation errors occurred, try adding the data.
            clsDataAccess da = new clsDataAccess();
            da.UpdateCustomer(clsDataAccess.EditType.Insert,
                ThisDocument.XMLNodes[1].get_XML(true));

            // Disable the AddNew button once the customer has been
            // added and repopulate the Customer list.
            cbSaveAsNew.Enabled=false;
            FillCustomerList();

            // Mark the document as not "dirty."
            cbSaveAsNew.Enabled=false;
            ThisDocument.Saved = true;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error adding customer data: " + ex.Message, 
            "Customer Form", MessageBoxButtons.OK, MessageBoxIcon.Error);
        return;
    }
}

Testing the Application

Try it out:

  1. Press F5 to build and run the project.

  2. Enter new customer data in the form.

  3. Once complete, click Save As New on the Customer Form command bar.

    The data is validated against customer.xsd before updating the Customers table in Northwind. Try creating an invalid customer record (for example, where the CustomerID is greater than 5 characters) and then try Save As New. You should receive a message box describing the validation error.

  4. Click Clear on the Customer Form command bar to clear the form.

  5. Choose a customer from the combo box on the Customer Form commmand bar and click Edit.

    The form is populated with the data for the customer you selected.

  6. Modify the customer data in the form and click Save to submit your changes.

    **Note   **As with Save As New, the data in the form is validating prior to submitting the update to the Customers table in Northwind.

Next Steps

To learn how to you can take advantage of the calculation and formatting capabilities available in Excel in your Office solutions, see Lab 7: Performing Calculations in Excel Solutions.

For more information, as well as links to other labs in this series, see Visual Studio Tools for the Microsoft Office System Training.