Importing from a Database

You can use a database table as a data source for a WordWriter template by passing the SetDataSource method either an ADO.NET DataTable or an ADO.NET DataSet. If you pass SetDataSource a DataSet, WordWriter will use the first DataTable in the DataSet as the data source.

The following sample generates a file from the template DataSetTemplate.doc (included in WordWriter\doc-samples\samples\basic\DataSet). This template contains the merge fields TitleOfCourtesy, FirstName, LastName, and Title. Using ADO.NET and OleDB, the VB.NET code gets the values for these fields from the Employees table in the NorthWind Traders database.

To set a template's data source to a DataSet using OleDB:

1. Import the System.Data and System.Data.OleDB namespaces to the .aspx page:

   <%@ Import namespace="System.Data" %>
   <%@ Import namespace="System.Data.OleDb" %>

2. Create an OleDbConnection object and open a database connection, for example:

   '--- Define the connection string we'll use to connect to the Access database.
   '--- This string is stored in an application variable WordWriter\doc-samples\global.asax.
   Dim strConnString As String = Application("connString").ToString()
   ...				
   '--- Declare and initialize an OleDbConnection object.
   Dim oConn As OleDbConnection = Nothing
   ...
   '--- Instantiate and open an OleDbConnection, passing the connection string.
   oConn = New OleDbConnection(strConnString)
   oConn.Open()
   

3. Create a SQL query to get data from the database. This SQL query gets TitleOfCourtesy, FirstName, LastName, and Title from the first row in the Employee's table:

   Dim strSQL As String = "SELECT TOP 1 TitleOfCourtesy, FirstName, LastName, Title FROM Employees"

4. Create an OleDbDataAdapter that will execute the SQL command at the data source and fill the DataSet, for example:

   Dim oAdpt As OleDbDataAdapter = Nothing
   ...
   oAdpt = New OleDbDataAdapter(strSQL, oConn)

5. Create a DataSet and use the OleDbDataAdapter to fill it with the data retrieved from the database:

Dim oDS As DataSet = Nothing
...		
oDS = New DataSet()
oAdpt.Fill(oDS)

6. After creating a WordTemplate object, and opening the template Word file, call SetDataSource and pass it the DataSet:

   oWW.SetDataSource(oDS)

7. To enter the values in the template and either save the file or stream it to the browser, call Process and then Save.
   
   
8. Close the connection to the database:

   If Not oConn Is Nothing Then
   	oConn.Close()
   End If

Copyright © 2003, SoftArtisans, Inc.