Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

The following code example shows how to query the AdventureWorks2000 database and return the data as a DataSet object. The DataSet object is then used as the data source in a WordTemplate document:

Code Block
c#
c#
titleC#c#
void GenerateDocument(int employeeID)
{
     //--- Query the database
     DataTable dt = new DataTable();
     using(SqlConnection conn = new SqlConnection(connString))
     {
          string sql =
               "SELECT FirstName, LastName FROM Employee WHERE EmployeeId = @id";
          SqlCommand cmd =
               new SqlCommand(sql, conn);

          //--- Use the employee ID as a SQL parameter
          cmd.Parameters.Add("@id", employeeID);
          SqlDataAdapter adpt = new SqlDataAdapter(cmd);
          adpt.Fill(dt);
     }

     //--- Use a DataTable as the data source
     WordTemplate wt = new WordTemplate();
     wt.Open(templatePath);
     wt.SetDataSource(dt);
     wt.Process();
     wt.Save(Page.Response, "DatabaseOutput.doc", false);
}
Code Block
vb.net
vb.net
titleVB.NETvb.net
Private Sub GenerateDocument(ByVal employeeID As Integer)
     '--- Query the database
     Dim dt As New DataTable()
     Dim conn As New SqlConnection(connString)
     Dim sql As String = _
          "SELECT FirstName, LastName FROM Employee WHERE EmployeeId = @id"
     Try
          Dim cmd As New SqlCommand(sql, conn)

          '--- Use the employee ID as a SQL parameter
          cmd.Parameters.Add("@id", employeeID)
          Dim adpt As New SqlDataAdaptercmd)
          adpt.Fill(dt)
     Finally
          If Not conn Is Nothing Then
               conn.Dispose()
          End If
     End Try

     '--- Use a DataTable as the data source
     Dim wt As New WordTemplate()
     wt.Open(templatePath)
     wt.SetDataSource(dt)
     wt.Process()
     wt.Save(Page.Response, "DatabaseOutput.doc", False)
End Sub

...

SqlDataReader, OleDbDataReaders and AdomdDataReader are also available for use by WordTemplate. Remember that unlike DataSet and DataTable objects, DataReader objects require an open connection to the database while reading data. Remember to close SqlDataReader, OleDbDataReader or AdomdDataReader objects calling WordTemplate.Process().

Code Block
c#
c#
titleC#c#
SqlCommand cmd;

//--- CloseConnection will close the associated connection
//--- when the reader is closed
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

try
{
     WordTempalte wt = new WordTemplate();
     wt.Open(templatePath);
     wt.SetDataSource(rdr);
     wt.Process();
     wt.Save(Page.Response, "DataReader.doc", false);
}
finally
{
     if(rdr!=null)
          rdr.Close();
}
Code Block
vb.net
vb.net
titleVB.NETvb.net
Dim cmd As SqlCommand

'--- CloseConnection will close the associated connection when
'--- the reader is closed
Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

Try
     Dim wt As New WordTemplate();
     wt.Open(templatePath)
     wt.SetDataSource(rdr)
     wt.Process()
     wt.Save(Page.Response, "DataReader.doc", False)
Finally
     If Not rdr Is Nothing Then
          rdr.Close()
     End If
End Try