Walkthrough: Building a Word Document Using SQL Server Data

Article
12/18/2006

Mary Chipman
MCW Technologies, LLC

September 2003

Applies to:
    Microsoft® Visual Studio® Tools for the Microsoft Office System
    Microsoft Office Word 2003
    Microsoft Visual Studio .NET 2003

Summary: Shows how you can create customized documents based on Microsoft SQL Server data in code by taking advantage of bookmarks in a Microsoft Office Word 2003 document or template. (14 printed pages)

Introduction
Prerequisites
Getting Started
Creating the Document Header
Connecting to SQL Server and Inserting the Data
Conclusion

Introduction

In this walkthrough, you will first create a Microsoft® Office Word 2003 document that contains bookmarks as the location for inserting text retrieved from the Microsoft SQL Server Northwind sample database. You will then use ADO.NET to connect to and retrieve the data. You'll insert the data in the Word document at the specified bookmark.

Prerequisites

To complete this walkthrough, the following software and components must be installed on the development computer:

Microsoft Visual Studio® .NET 2003 or Microsoft Visual Basic® .NET Standard 2003
Microsoft Visual Studio Tools for the Microsoft Office System
Microsoft Office Professional Edition 2003
Microsoft SQL Server or Microsoft SQL Server Desktop Engine (MSDE) 7.0 or 2000, with the Northwind sample database installed. This demonstration assumes that you have set up SQL Server/MSDE allowing access using integrated security.

**Tip **This demonstration assumes that if you're a Visual Basic .NET programmer, you've set the Option Strict setting in your project to On (or have added the Option Strict statement to each module in your project), although it is not required. Setting the Option Strict setting to On requires a bit more code, as you see, but it also ensures that you do not perform any unsafe type conversions. You can get by without it, but in the long run, the discipline required by taking advantage of this option far outweighs the difficulties it adds as you write code.

Getting Started

First you need to create a Word Document project using Microsoft Visual Studio Tools for the Microsoft Office System.

To create a Word Document project

From the File menu, point to New, and then click Project to display the New Project dialog box.
In the Project Types pane, expand Microsoft Office System Projects, and then select Visual Basic Projects or Visual C# Projects.
In the Templates pane, select Word Document.
Name the project BuildWordDocSQL, and store it in a convenient local path.
Accept the defaults in the MicrosoftOffice Project Wizard, and click Finish to create the project.

Visual Studio .NET opens the ThisDocument.vb or ThisDocument.cs file in the Code Editor for you.

Creating the Document Header

In this example, you will create a procedure that inserts and formats the document header, and then creates a Bookmark as a marker for inserting text from the Northwind database to create a phone list of suppliers. You will then call the procedure from the Open event handler for the ThisDocument object, although you could also invoke the procedure from a button, menu, or form. All the steps in this section work with a procedure named CreateHeader, which you will create in the first step.

To create a document header

In the OfficeCodeBehind class, create a procedure called CreateHeader and create variables as needed:

' Visual Basic
Private Sub CreateHeader()
    Dim rng As Word.Range

End Sub

// C#
private void CreateHeader() 
{
    Word.Range rng;
    Object start = Type.Missing;
    Object end = Type.Missing;
    Object unit = Type.Missing;
    Object count = Type.Missing;
}

Add code to the CreateHeader procedure, clearing any existing contents of the document and setting the section to landscape orientation:

' Visual Basic
' Clear the contents of the document.
ThisDocument.Range.Delete()
ThisDocument.Sections(1).PageSetup. _
    Orientation = Word.WdOrientation.wdOrientLandscape

// C#
// Clear the contents of the document.
ThisDocument.Range(ref start, ref end).Delete(ref unit, ref count);
ThisDocument.Sections[1].PageSetup.
    Orientation = Word.WdOrientation.wdOrientLandscape;

Add code that sets up an array of locations for tab settings within the document:

' Visual Basic 
' Set up tab locations.
Dim tabStops() As Single = {4, 6}

// C#
// Set up tab locations.
Single[] tabStops = new Single[] {4, 6};

Add code to the CreateHeader procedure that creates a Range object consisting of the empty paragraph mark that is the only character in the document:
```
' Visual Basic 
rng = ThisDocument.Range(0, 0)

// C#
start = 0;
end = 0;
rng = ThisDocument.Range(ref start, ref end);
```

Add code to insert and format the title text in the document and format it:

' Visual Basic 
' Insert the header.
rng.InsertBefore("Supplier Phone List")
rng.Font.Name = "Verdana"
rng.Font.Size = 16
rng.InsertParagraphAfter()
rng.InsertParagraphAfter()

// C#
// Insert the header.
rng.InsertBefore("Supplier Phone List");
rng.Font.Name = "Verdana";
rng.Font.Size = 16;
rng.InsertParagraphAfter();
rng.InsertParagraphAfter();

Add code to reset the active range, and retrieve the paragraph format for the range:

' Visual Basic 
' Create a new range at the insertion point.
rng.SetRange(Start:=rng.End, End:=rng.End)
Dim fmt As Word.ParagraphFormat = rng.ParagraphFormat

// C#
// Create a new range at the insertion point.
rng.SetRange(rng.End, rng.End);
Word.ParagraphFormat fmt = rng.ParagraphFormat;

Add code to set up the tabs for the column headers:

' Visual Basic 
' Set up the tabs for the column headers.
fmt.TabStops.ClearAll()
fmt.TabStops.Add( _
    ThisApplication.InchesToPoints(tabStops(0)), _
    Word.WdTabAlignment.wdAlignTabLeft, _
    Word.WdTabLeader.wdTabLeaderSpaces)
fmt.TabStops.Add( _
    ThisApplication.InchesToPoints(tabStops(1)), _
    Word.WdTabAlignment.wdAlignTabLeft, _
    Word.WdTabLeader.wdTabLeaderSpaces)

// C#
// Set up the tabs for the column headers.
Object alignment = Word.WdTabAlignment.wdAlignTabLeft;
Object leader = Word.WdTabLeader.wdTabLeaderSpaces;
fmt.TabStops.ClearAll();
fmt.TabStops.Add(ThisApplication.InchesToPoints(tabStops[0]),
    ref alignment, ref leader);

alignment = Word.WdTabAlignment.wdAlignTabLeft;
leader = Word.WdTabLeader.wdTabLeaderSpaces;
fmt.TabStops.Add(ThisApplication.InchesToPoints(tabStops[1]),
    ref alignment, ref leader);

Add code to create the Company Name, Contact, and Phone headings, separated by tabs:

' Visual Basic 
' Insert the column header text and formatting.
rng.Text = _
    "Company Name" & ControlChars.Tab & _
    "Contact" & ControlChars.Tab & _
    "Phone Number"
rng.Font.Name = "Verdana"
rng.Font.Size = 10
rng.Font.Bold = CLng(True)
rng.Font.Underline = Word.WdUnderline.wdUnderlineSingle

// C#
// Insert the column header text and formatting.
rng.Text = "Company Name\tContact\tPhone Number";
rng.Font.Name = "Verdana";
rng.Font.Size = 10;
rng.Font.Bold = Convert.ToInt32(true);
rng.Font.Underline = Word.WdUnderline.wdUnderlineSingle;

Add code to create a range at the current insertion point, and retrieve the paragraph format associated with this range:

' Visual Basic 
' Create a new range at the insertion point.
rng.InsertParagraphAfter()
rng.SetRange(Start:=rng.End, End:=rng.End)
fmt = rng.ParagraphFormat

// C#
// Create a new range at the insertion point.
rng.InsertParagraphAfter();
rng.SetRange(rng.End, rng.End);
fmt = rng.ParagraphFormat;

Add code to recreate the same tab stops at the new insertion point, but with dot leaders instead of spaces:

' Visual Basic 
' Set up the tabs for the columns.
fmt.TabStops.ClearAll()
fmt.TabStops.Add( _
    ThisApplication.InchesToPoints(tabStops(0)), _
    Word.WdTabAlignment.wdAlignTabLeft, _
    Word.WdTabLeader.wdTabLeaderDots)
fmt.TabStops.Add( _
    ThisApplication.InchesToPoints(tabStops(1)), _
    Word.WdTabAlignment.wdAlignTabLeft, _
    Word.WdTabLeader.wdTabLeaderDots)

// C#
// Set up the tabs for the columns.
fmt.TabStops.ClearAll();
alignment = Word.WdTabAlignment.wdAlignTabLeft;
leader = Word.WdTabLeader.wdTabLeaderDots;
fmt.TabStops.Add(ThisApplication.InchesToPoints(tabStops[0]), 
    ref alignment, ref leader);
fmt.TabStops.Add(ThisApplication.InchesToPoints(tabStops[1]), 
    ref alignment, ref leader);

Add code to create a Bookmark named Data at the insertion point, and insert a paragraph after the Bookmark.

' Visual Basic 
' Insert a bookmark to use for the inserted data.
ThisDocument.Bookmarks.Add( _
    Name:="Data", Range:=DirectCast(rng, System.Object))
rng.InsertParagraphAfter()

// C#
// Insert a bookmark to use for the inserted data.
Object range = rng;
ThisDocument.Bookmarks.Add("Data", ref range);
rng.InsertParagraphAfter();

Add code in the ThisDocument_Open() event handler to call the CreateHeader procedure, turning screen updating off and back on again:

' Visual Basic 
Private Sub ThisDocument_Open() Handles ThisDocument.Open
    Try
        ThisApplication.ScreenUpdating = False
        CreateHeader()

    Finally
        ThisApplication.ScreenUpdating = True
    End Try
End Sub

// C#
protected void ThisDocument_Open()
{
    try
    {
        ThisApplication.ScreenUpdating = false;
        CreateHeader();
    }
    finally
    {
        ThisApplication.ScreenUpdating = true;
    }
}

Save your work and test by running the project. You should see the headings for the phone list, as shown in Figure 1. If you have turned on the display of Bookmarks, you should see the Data Bookmark as an I-beam directly under Company Name. Close the document when you're done (you can save the changes if you like) and return to Visual Studio .NET.

Figure 1. The phone list before the data is added

Connecting to SQL Server and Inserting the Data

Once you have set up the phone list, you will create a new procedure to connect to the Suppliers table in the Northwind SQL Server database. You will then insert the data at the bookmark you defined. You will call the procedure from the Open event handler for the ThisDocument object.

To insert data from the database

Scroll to the top of the open code file and type the following statement:

' Visual Basic
Imports System.Data.SqlClient

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

Scroll to the end of the CreateHeader procedure in the OfficeCodeBehind class, and add a new procedure named RetrieveSuppliers:
```
' Visual Basic
Private Sub RetrieveSuppliers()

End Sub

// C#
private void RetrieveSuppliers() 
{

}
```

Within RetrieveSuppliers, add code to create the variables your procedure will need:

' Visual Basic
Dim cnn As SqlConnection
Dim dr As SqlDataReader
Dim cmd As SqlCommand
Dim rng As Word.Range
Dim sw As New System.IO.StringWriter

// C#
SqlConnection cnn;
SqlCommand cmd;
SqlDataReader dr = null;
Word.Range rng;
System.IO.StringWriter sw = new System.IO.StringWriter();

Add code to create a String variable to create a SELECT statement, selecting the CompanyName, ContactName, and Phone fields from the Suppliers table.

' Visual Basic
' Set up the command text:
Dim strSQL As String = _
    "SELECT CompanyName, ContactName, Phone " & _
    "FROM Suppliers ORDER BY CompanyName"

// C#
// Set up the command text:
string  strSQL =
    "SELECT CompanyName, ContactName, Phone " +
    "FROM Suppliers ORDER BY CompanyName";

Add code to create an exception-handling block that displays the Exception.Message value using the MessageBox.Show method if an exception occurs:

' Visual Basic
Try

Catch ex As Exception
    MessageBox.Show(ex.Message, ThisDocument.Name)

Finally

End Try

// C#
try 
{
} 
catch (Exception ex) 
{
    MessageBox.Show(ex.Message, ThisDocument.Name);
} 
finally 
{
}

Add code in the Try block to open the connection to the local SQL Server database:

' Visual Basic
' Create the connection:
cnn = New SqlConnection( _
    "Data Source=(local);Database=Northwind;Integrated Security=True")
cnn.Open()

// C#
// Create the connection:
cnn = new SqlConnection(       
    "Data Source=(local);Database=Northwind;" + 
    "Integrated Security=true");
cnn.Open();

Add code to create the Command object and retrieve the data reader:

' Visual Basic 
' Create the command and retrieve the data reader:
cmd = New SqlCommand(strSQL, cnn)
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

// C#
// Create the command and retrieve the data reader:
cmd = new SqlCommand(strSQL, cnn);
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

Add code to loop through the data, building up a string containing the data and the tab separators:

' Visual Basic 
' Loop through the data, creating tab-delimited output:
While dr.Read()
    sw.WriteLine("{0}{1}{2}{3}{4}", _
        dr(0), ControlChars.Tab, _
        dr(1), ControlChars.Tab, dr(2))
End While

// C#
// Loop through the data, creating tab-delimited output:
while (dr.Read())
{
    sw.WriteLine("{0}\t{1}\t{2}", 
        dr[0], dr[1], dr[2]);
}

Add code to insert the delimited string into the bookmark you created earlier, and format the text:

' Visual Basic 
' Work with the previously created bookmark:
rng = ThisDocument.Bookmarks("Data").Range
rng.Text = sw.ToString()
rng.Font.Name = "Verdana"
rng.Font.Size = 10

// C#
// Work with the previously created bookmark:
Object item = "Data";
Word.Bookmark bmk = 
    (Word.Bookmark) ThisDocument.Bookmarks.get_Item(ref item);
rng = bmk.Range;
rng.Text = sw.ToString();
rng.Font.Name = "Verdana";
rng.Font.Size = 10;

Add code in the Finally block to clean up any open data objects:

' Visual Basic
If Not dr Is Nothing Then
    dr.Close()
End If

// C#
if (dr != null ) 
{
    dr.Close();
}

Add code in the ThisDocument.Open event handler to call the procedure, after the code that calls the CreateHeader procedure, so that the procedure looks like the following:

' Visual Basic
Private Sub ThisDocument_Open() Handles ThisDocument.Open
    Try
        ThisApplication.ScreenUpdating = False
        CreateHeader()
        RetrieveSuppliers()

    Finally
        ThisApplication.ScreenUpdating = True
    End Try
End Sub

// C#
protected void ThisDocument_Open()
{
    try
    {
        ThisApplication.ScreenUpdating = false;
        CreateHeader();
        RetrieveSuppliers();
    }
    finally
    {
        ThisApplication.ScreenUpdating = true;
    }
}

Save your work and test by running the project. Figure 2 shows a partial view of the completed phone list.

Figure 2. The completed supplier phone list

Conclusion

In this walkthrough, you learned how you can take advantage of bookmarks in a Word document or template to create customized documents based on SQL Server data in code. Using Visual Studio Tools for the Microsoft Office System to create your project and ADO.NET to connect to the data, you can insert data into a Word document at a specified bookmark.