Walkthrough: Connect an Item in the Data Store to a SQL Server Database

Article
06/14/2014

Word Developer Reference

Microsoft Office Word 2007 enables you to build data-driven, document-generation solutions. You can create a document template that includes a custom XML part and use content controls to bind to custom XML data using XML mapping. Then you can create a managed application to build a new document based on the template. The managed application opens the document template, retrieves data from a Microsoft SQL Server database to build a new custom XML part, replaces the template's custom XML part , and saves as a new document.

This walkthrough explains how to build a new template and how to create a server-side application that generates documents that display data stored in a Microsoft SQL Server database. To build this application, you will do the following:

Create a Word 2007 document template.
Create a server-side application that pulls data from a Microsoft SQL Server database and generates new documents based on the Word 2007 document template.

The objects used in this sample are:

ContentControl
ContentControls
CustomXMLPart (Microsoft Office system core object model)
CustomXMLParts (Microsoft Office system core object model)
XMLMapping

For more information about content controls, see Working with Content Controls.

Business Scenario: Custom Customer Letter

To demonstrate how to build a Word 2007 template and connect an item in the data store to a Microsoft SQL Server database, you first build a customer letter document template with content controls that map to an XML file. Next, you create a document-generation application that enables you to select a company name to generate a custom letter. The application retrieves customer data from a Microsoft SQL Server database and uses the customer letter document template to build a new document that displays customer data based on a user selection. The document displays the following information:

Company Name
Contact Name
Contact Title
Phone Number

Create a Word Template and Define the XML Mappings for Each Content Control

Use the following steps to create a Word 2007 template.

Open Word 2007.
Create a document.
Create content controls to bind to a node in the data store.

Content controls are predefined pieces of content. There are several types of content controls, including text blocks, drop-down menus, combo boxes, calendar controls, and pictures. You can map these content controls to an element in an XML file. Using XPath expressions, you can programmatically map content in an XML file to a content control. This enables you to write a simple and short application to manipulate and modify data in a document.
Set the XML mapping on the content control.

XML mapping is a feature of Word 2007 that enables you to create a link between a document and an XML file. This creates true data/view separation between the document formatting and the custom XML data.

To load a custom XML part, you must first add a new data store to a Document object by using the Add method of the CustomXMLParts collection. This appends a new, empty data store to the document. Because it is empty, you cannot use it yet. Next, you must load a custom XML part from an XML file into the data store, by calling the Load method of the CustomXMLPart object, using a valid path to an XML file as the parameter.
Add four plain-text content controls in the following order:
1. Content control for Company Name
2. Content control for Contact Name
3. Content control for Contact Title
4. Content control for Phone Number
Save the template document as C:\CustomerLetterTemplate.docx.

To Set an XML Mapping on a Content Control

Note
In this procedure, you map the content control to a sample custom XML file. You create a valid custom XML file, save it to your hard disk drive, and add a data store to the document that contains the information to which you want to map.

Create a text file and save it as C:\CustomerData.xml.

Copy the following into the text file and save it:


<?xml version="1.0"?>
<Customer>
<CompanyName>Alfreds Futterkiste</CompanyName>
<ContactName>Maria Anders</ContactName>
<ContactTitle>Sales Representative</ContactTitle>
<Phone>030-0074321</Phone>
</Customer>

Open the Microsoft Visual Basic editor and run the following Visual Basic for Applications (VBA) code to add a data store to your template document. This sample code demonstrates how to attach an XML file to a document, so that it becomes an available data store item.


' Load CustomerData.xml file
ActiveDocument.CustomXMLParts.Add
ActiveDocument.CustomXMLParts(4).Load ("c:\CustomerData.xml")

Set an XML mapping on a content control that refers to a node in the added data store. To create an XML mapping, you use an XPath expression to the node in the custom XML data part to which you want to map a content control. After you add a data store to your document (and the data store points to a valid XML file), you are ready to map one of its nodes to a content control. To do this, pass a String containing a valid XPath to a ContentControl object by using the SetMapping method of the XMLMapping object (via the XMLMapping property of the ContentControl object). Open the Visual Basic editor and run the following VBA code to bind content controls to items in the data store.

Dim strXPath1 As String strXPath1 = "/Customer/CompanyName" ActiveDocument.ContentControls(1).XMLMapping.SetMapping strXPath1

Dim strXPath2 As String strXPath2 = "/Customer/ContactName" ActiveDocument.ContentControls(2).XMLMapping.SetMapping strXPath2 Dim strXPath3 As String strXPath3 = "/Customer/ContactTitle" ActiveDocument.ContentControls(3).XMLMapping.SetMapping strXPath3

Dim strXPath4 As String strXPath4 = "/Customer/Phone" ActiveDocument.ContentControls(4).XMLMapping.SetMapping strXPath4

Create a Server-Side Application That Pulls Data from a SQL Server Database and Generates a New Document

You can create a Web-based application that enables users to select a company name and generate a custom letter. The Web-based application retrieves customer data from a Microsoft SQL Server database, opens the customer letter document template, and creates a new document that displays customer data based on a user selection. This application does not require the use of Word 2007 or VBA. You can use your favorite managed code (Microsoft Visual Basic .NET or C#) language to build this application. To build this application, do the following:

Open Microsoft Visual Studio 2005 or Microsoft Visual Web Developer 2005.
Create an ASP.NET Web site and name it SqlServerSample.
Connect the ASP.NET Web site to a Microsoft SQL Server database.

Add a connection string to the Web.config file as follows:


<connectionStrings>
<add name="NorthwindConnectionString"
connectionString="data source=(local);database=Northwind; integrated security=SSPI;persist security info=false;"
providerName="System.Data.SqlClient" />
</connectionStrings>

Add the CustomerLetterTemplate.docx to the App_Data folder.
Download and install the Microsoft .NET Framework 3.0 (formerly Microsoft WinFX).

Configure the assembly in the Web.config file as follows:


<compilation debug="false">
<assemblies>
<add assembly="WindowsBase, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
</assemblies>
</compilation>

Create a Web Form and replace the default.aspx code with the following sample code.

Sample Code

Microsoft Visual Basic .NET

The following sample shows how to bind to a Microsoft SQL Server database to retrieve data based on a customer selection and create a new document based on the CustomerLetterTemplate.docx.

<%@ Page Language="VB" AutoEventWireup="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.IO.Packaging" %>
<%@ Import Namespace="System.Xml" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Data-Driven Document Generation - SQL Server Sample</title>
</head>
<script runat='server'>
Private Const strRelRoot As String = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"

Private Sub CreateDocument()
    ' Get the template file and create a stream from it
    Const TemplateFile As String = "~/App_Data/CustomerTemplate.docx"
    
    ' Read the file into memory
    Dim buffer() As Byte = File.ReadAllBytes(Server.MapPath(TemplateFile))
    Dim memoryStream As MemoryStream = New MemoryStream(buffer, True)
    buffer = Nothing
    
    ' Open the document in the stream and replace the custom XML part
    Dim pkgFile As Package = Package.Open(memoryStream, FileMode.Open, FileAccess.ReadWrite)
    Dim pkgrcOfficeDocument As PackageRelationshipCollection = pkgFile.GetRelationshipsByType(strRelRoot)
    For Each pkgr As PackageRelationship In pkgrcOfficeDocument
        If (pkgr.SourceUri.OriginalString = "/") Then
            
            ' Get the root part
            Dim pkgpRoot As PackagePart = pkgFile.GetPart(New Uri(("/" + pkgr.TargetUri.ToString), UriKind.Relative))
            
            ' Add a custom XML part to the package
            Dim uriData As Uri = New Uri("/customXML/item1.xml", UriKind.Relative)
            If pkgFile.PartExists(uriData) Then
            
                ' Delete template "/customXML/item1.xml" part
                pkgFile.DeletePart(uriData)
            End If
            
            ' Load the custom XML data
            Dim pkgprtData As PackagePart = pkgFile.CreatePart(uriData, "application/xml")
            GetDataFromSQLServer(pkgprtData.GetStream, ddlCustomer.SelectedValue)
        End If
    Next
    
    ' Close the file
    pkgFile.Close()
    
    ' Return the result
    Response.ClearContent()
    Response.ClearHeaders()
    Response.AddHeader("content-disposition", "attachment; filename=document.docx")
    Response.ContentEncoding = System.Text.Encoding.UTF8
    memoryStream.WriteTo(Response.OutputStream)
    memoryStream.Close()
    Response.End()
End Sub

Private Sub GetDataFromSQLServer(ByVal stream As Stream, ByVal customerID As String)
    
    'Connect to a Microsoft SQL Server database and get data
    Dim source As String = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
    Const SqlStatement As String = "SELECT CompanyName, ContactName, ContactTitle, Phone FROM Customers WHERE CustomerID=@customerID"
    Dim conn As SqlConnection = New SqlConnection(source)
    conn.Open()
    Dim cmd As SqlCommand = New SqlCommand(SqlStatement, conn)
    cmd.Parameters.AddWithValue("@customerID", customerID)
    Dim dr As SqlDataReader = cmd.ExecuteReader
    If dr.Read Then
        Dim writer As XmlWriter = XmlWriter.Create(stream)
        writer.WriteStartElement("Customer")
        writer.WriteElementString("CompanyName", CType(dr("CompanyName"), String))
        writer.WriteElementString("ContactName", CType(dr("ContactName"), String))
        writer.WriteElementString("ContactTitle", CType(dr("ContactTitle"), String))
        writer.WriteElementString("Phone", CType(dr("Phone"), String))
        writer.WriteEndElement()
        writer.Close()
    End If
    dr.Close()
    conn.Close()
End Sub

Protected Sub SubmitBtn_Click(ByVal sender As Object, ByVal e As EventArgs)
    CreateDocument()
End Sub

</script>
<body>
<form id="form1" runat="server">
<div>
<h1>
Customer Letter Generator</h1>
<table border="0" cellpadding="0" cellspacing="0" style="width: 100%; height: 12%">
<tr>
<td>
Choose a customer:</td>
<td>
<asp:DropDownList ID="ddlCustomer" runat="server" AutoPostBack="True" DataSourceID="CustomerData"
DataTextField="CompanyName" DataValueField="CustomerID" Width="301px">
</asp:DropDownList>
<asp:SqlDataSource ID="CustomerData" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [CustomerID], [CompanyName] FROM [Customers]" ProviderName="<%$ ConnectionStrings:NorthwindConnectionString.ProviderName %>">
</asp:SqlDataSource>
</td>
</tr>
</table>
</div>
<br />
<asp:Button ID="Button1" runat="server" OnClick="SubmitBtn_Click" Text="Create Letter"
Width="123px" />
</form>
</body>
</html>

C#