Walkthrough: Loading SQL Server Data into a Word Table

  • Article

 

Mary Chipman
MCW Technologies

September 2003

Applies to:

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

Summary: Learn to programmatically load data into a Word table from a SQL Server database by taking advantage of ADO.NET to access data, and using Microsoft Visual Studio Tools for the Microsoft Office System to create your project. (10 printed pages).

Contents

Introduction
Prerequisites
Getting Started
Creating the Word Table
Connecting to SQL Server and Inserting the Data
Conclusion

Introduction

In this walkthrough, you'll first create a Microsoft® Office Word 2003 table to contain data retrieved from the Microsoft SQL Server pubs sample database. You'll use ADO.NET to connect to and retrieve data from pubs, selecting the authors, titles, and royalty percentage. You'll then insert the data into the Word table.

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.

**Note   **The code in this example counts on using integrated security. If you're not able to log onto your SQL Server computer with your own account, you will need to get information from your SQL Server administrator on how to log on, and you'll need to modify the connection information in the code, as well. See the SqlConnection.ConnectionString property in the .NET Framework documentation for more information, if you need to modify the sample code.

**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 Visual Studio Tools for the Microsoft Office System.

To create a Word Document project

  1. Start Visual Studio .NET, and on the File menu, point to New, and click Project.

  2. In the Project Types pane, expand Microsoft Office System Projects, and then select Visual Basic Projects or Visual C# Projects.

  3. In the Templates pane, select Word Document.

  4. Name the project SQLServerWordTable, and store it in a convenient local path.

  5. Accept the defaults in the Microsoft Office 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 Word Table

In this example, you'll use the document already created by the project. You'll add a procedure that creates the Word table, formats it, and inserts the data from the SQL Server database. You'll 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.

To create a table in the document

  1. Within the OfficeCodeBehind class, add the following procedure:

    ' Visual Basic
Private Sub CreateWordTable()
    ' Move to start of document.
    Dim rng As Word.Range = _
        ThisDocument.Range(Start:=0, End:=0)

    ' Insert Title and paragraph marks.
      rng.InsertBefore("Authors and Titles")
      rng.Font.Name = "Verdana"
      rng.Font.Size = 16
      rng.InsertParagraphAfter()
      rng.InsertParagraphAfter()
      rng.SetRange(rng.End, rng.End)

    ' Add the table.
    rng.Tables.Add( _
        Range:=ThisDocument.Paragraphs(2).Range, _
        NumRows:=1, NumColumns:=3)

    ' Set object variable to point to new table
    Dim tbl As Word.Table = ThisDocument.Tables(1)

    ' Format the table
    tbl.Range.Font.Size = 12
    tbl.Range.Font.Name = "Verdana"
    tbl.Borders.InsideLineStyle = _
        Word.WdLineStyle.wdLineStyleSingle
    tbl.Borders.OutsideLineStyle = _
        Word.WdLineStyle.wdLineStyleDouble

    ' Set the column widths.
    tbl.Columns(1).SetWidth( _
        ThisApplication.InchesToPoints(1.5), _
    Word.WdRulerStyle.wdAdjustNone)
        tbl.Columns(2).SetWidth( _
    ThisApplication.InchesToPoints(3.25), _
        Word.WdRulerStyle.wdAdjustNone)
    tbl.Columns(3).SetWidth( _
        ThisApplication.InchesToPoints(1.25), _
        Word.WdRulerStyle.wdAdjustNone)
    tbl.Cell(1, 1).Range.Text = "Author"
    tbl.Cell(1, 2).Range.Text = "Title"

    ' Right-align third column.
    Dim rngCell As Word.Range = tbl.Cell(1, 3).Range
    rngCell.Text = "Royalty Pct"
    rngCell.ParagraphFormat.Alignment = _
        Word.WdParagraphAlignment.wdAlignParagraphRight
End Sub

// C#
private void CreateWordTable() 
{
    // Move to start of document.
    Object start = 0;
    Object end = 0;
    Word.Range rng =
        ThisDocument.Range(ref start, ref end);

    // Insert Title and paragraph marks.
    rng.InsertBefore("Authors and Titles");
    rng.Font.Name = "Verdana";
    rng.Font.Size = 16;
    rng.InsertParagraphAfter();
    rng.InsertParagraphAfter();
    rng.SetRange(rng.End, rng.End);

    // Add the table.
    Object defaultTableBehavior = Type.Missing;
    Object autoFitBehavior = Type.Missing;
    rng.Tables.Add(
        ThisDocument.Paragraphs[2].Range,
        1, 3, ref defaultTableBehavior, ref autoFitBehavior);

    // Set object variable to point to new table.
    Word.Table tbl = ThisDocument.Tables[1];

    // Format the table.
    tbl.Range.Font.Size = 12;
    tbl.Range.Font.Name = "Verdana";
    tbl.Borders.InsideLineStyle = 
        Word.WdLineStyle.wdLineStyleSingle;
    tbl.Borders.OutsideLineStyle =
        Word.WdLineStyle.wdLineStyleDouble;

    // Set the column widths.
    tbl.Columns[1].SetWidth(
        ThisApplication.InchesToPoints((float)1.5), 
        Word.WdRulerStyle.wdAdjustNone);
    tbl.Columns[2].SetWidth(
        ThisApplication.InchesToPoints((float)3.25), 
        Word.WdRulerStyle.wdAdjustNone);
    tbl.Columns[3].SetWidth(
        ThisApplication.InchesToPoints((float)1.25), 
        Word.WdRulerStyle.wdAdjustNone);
    tbl.Cell(1, 1).Range.Text = "Author";
    tbl.Cell(1, 2).Range.Text = "Title";

    // Right-align third column.
    Word.Range rngCell = tbl.Cell(1, 3).Range;
    rngCell.Text = "Royalty Pct";
    rngCell.ParagraphFormat.Alignment =
        Word.WdParagraphAlignment.wdAlignParagraphRight;
}

  2. Modify the ThisDocument_Open procedure, adding the following code:

    ' Visual Basic
CreateWordTable()

// C#
CreateWordTable();

  3. Select Save All on the File menu to save your project.

  4. Press F5 to run the project, verifying that the Word document contains the correct header information, as shown in Figure 1.

    Figure 1. The new table

  5. Close Word to return to Visual Studio .NET.

Connecting to SQL Server and Inserting the Data

You'll use the System.Data.SqlClient classes to connect to and query the SQL Server pubs database.

  1. Scroll to the top of the open code file and enter the following statements:

    ' Visual Basic
Imports System.Data.SqlClient

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

  2. Scroll to the end of the CreateWordTable procedure, and insert some the following new lines of code. Create a String variable to submit a query to return the author, title and royalty percentage.

    ' Visual Basic
Dim strSQL As String = _
    "SELECT au_lname, title, royaltyper " & _
    "FROM authors INNER JOIN titleauthor " & _
    "ON authors.au_id = titleauthor.au_id INNER JOIN titles " & _
    "ON titleauthor.title_id = titles.title_id ORDER BY au_lname"

// C#
String strSQL = 
    "SELECT au_lname, title, royaltyper " + 
    " FROM authors INNER JOIN titleauthor " +
    " ON authors.au_id = titleauthor.au_id INNER JOIN titles " + 
    " titleauthor.title_id = titles.title_id ORDER BY au_lname";

  3. Continuing in the sample procedure, declare SqlConnection, SqlDataReader, and SqlCommand object variables.

    ' Visual Basic
Dim cnn As SqlConnection
Dim sdr As SqlDataReader
Dim cmd As SqlCommand

// C#
SqlConnection cnn = null;
SqlDataReader sdr = null;
SqlCommand cmd = null;

  4. Add the following code to connect to the SQL Server database and execute the DataReader (you can leave out the comments within the code when typing).

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

    ' Open the Command and execute the DataReader.
    cmd = New SqlCommand(strSQL, cnn)
    sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

Catch ex As Exception
    MessageBox.Show(ex.Message)

Finally
    If Not sdr Is Nothing Then
        sdr.Close()
    End If
End Try

// C#
try 
{
    // Open the connection.
    cnn = new SqlConnection(
        "Data Source=(local);Database=pubs;Integrated Security=True");
    cnn.Open();

    // Open the Command and execute the DataReader.
    cmd = new SqlCommand(strSQL, cnn);
    sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex) 
{
    MessageBox.Show(ex.Message);
} 
finally 
{
    if (sdr != null) 
    {
        sdr.Close();
    }
}

  5. Immediately above the Catch statement within the Try block, add the following code, which retrieves the data. This code inserts the data into new rows in the Word table starting on the second row. The code also formats the first row of the table in bold.

    ' Visual Basic
' Start on second row.
Dim intRow As Integer = 2

' Retrieve the data and insert into new rows.
While sdr.Read()
    tbl.Rows.Add()
    tbl.Cell(intRow, 1).Range.Text = sdr(0).ToString
    tbl.Cell(intRow, 2).Range.Text = sdr(1).ToString
    tbl.Cell(intRow, 3).Range.Text = sdr(2).ToString
    intRow += 1
End While

' Bold the column heads. Note the use 
' of the integer value, 1, to turn 
' on bold.
tbl.Rows(1).Range.Bold = 1

// C#
// Start on second row.
int intRow = 2;

// Retrieve the data and insert into new rows.
Object beforeRow = Type.Missing;
while (sdr.Read())
{
    tbl.Rows.Add(ref beforeRow);
    tbl.Cell(intRow, 1).Range.Text = sdr[0].ToString();
    tbl.Cell(intRow, 2).Range.Text = sdr[1].ToString();
    tbl.Cell(intRow, 3).Range.Text = sdr[2].ToString();
    intRow += 1;
}

// Bold the column heads. Note the use 
// of the integer value, 1, to turn 
// on bold.
tbl.Rows[1].Range.Bold = 1;

  6. Select Save All on the File menu to save your project.

  7. Press F5 to run the project, verifying that the Word table contains the correct data. Figure 2 shows the first few rows in the table.

    Figure 2. The completed Word table

Conclusion

Programmatically connecting to a SQL Server database to load data into a Word table is easy once you understand how to take advantage of ADO.NET for data access and how to create a Word table to contain the data.