Using Ado.net Datasets in Microsoft Office System (vi)

ado| data Using Visual Studio Tools for the Microsoft Office System

So far, examples have been to use standard. NET Windows form applications to insert data from a DataSet object into a Microsoft Office document. This is a technology that is very effective for various applications in Microsoft Office system, regardless of how the object model for each application changes, the entire process is the same. In essence, managed code can run office because it loads the Office object model into the process space that runs the assembly. However, VBA developers are accustomed to the code they write in the background of the document, and the document is tightly linked to the VBA code.

This is part of the purpose behind visual Studio Tools for office. Visual Studio Tools for Office is a new set of Visual Studio. NET project template, which leverages PIAs and COM Interop (COM Interop) to run managed code in the background of Excel and Word documents. The network impact is similar in Office documents, whether you are writing or debugging VBA code. When you use Visual Studio Tools for Office only, Visual Studio. NET 2003 makes it possible to write and debug managed code that is tightly linked to the document at a very clear level.

The next section describes how to populate a new Word document with the entire document created from a template. The sample code for this document also contains a visual Studio Tools for Office project that inserts data into an Excel worksheet, which is exactly like the example described earlier. In fact, all two projects can be implemented using only PIAs without using visual Studio Tools for office. However, Visual Studio Tools for office makes it easier to connect managed code and Office documents at design time and at run time.

Populating a Word2003 template with a dataset

Bookmarks in Office documents have long provided a convenient way to insert noncontiguous fragments of data into a Word document. Create a new Word 2003 template, insert the bookmark in the appropriate format, and then programmatically access the collection of bookmarks to insert the data, which is easy to implement.

When you create a visual Studio Tools for Office Project, you have the option of creating a new Word document or an Excel document. In the following example, the Project Wizard creates a template named Vstowordtemplate.dot, as shown in Figure 8.



Figure 8: A Word 2003 template

Note that in Figure 8, the insertion point of the bookmark is the container for the name and address. For easy coding, the document designer names the bookmark with the same name as the corresponding field in the datasheet.

The code used to populate the bookmark's value is very short, and it runs in the ThisDocument_New event procedure, which is triggered when a new document is created from the template. It first calls the GetDataSet method to create the DataSet object, and the simplified version of GetDataSet is described earlier in this article. This version only uses the Customers table to create a dataset.

Private Sub thisdocument_new () Handles thisdocument.new

Dim ds as DataSet = GetDataSet ()

Then, get a reference to the bookmark, related table, and six rows of data in the document. The final code is arbitrary, but the real application should contain mechanisms that allow the user to select a specific user name to generate the envelope.

' Fill in the document bookmarks

Dim bookmarks as Word.bookmarks = Thisdocument.bookmarks

Dim Bookmark as Word.bookmark

Dim dt as DataTable = ds. Tables ("Customers")

Dim dr as DataRow = dt. Rows (5)

Finally, the code iterates through the collection of bookmarks, replacing the text of each bookmark with the value of the same field as the name in the datasheet.

For each bookmark in bookmarks

Dim bookmarkname as String = bookmark. Name

Bookmark. Range.Text = DR (BookmarkName)

Next

End Sub

Figure 9 shows the results of running this process



Figure 9 populates the bookmark text with data from the dataset, running the results

About Memory management

When interacting with the COM-based Office object model in. NET, the release of COM objects (disposal) and memory management require special attention. When used. NET when you write Office solutions, you have one. NET clients communicate with COM through runtime callable wrappers (Runtime-callable wrapper, RCW). An RCW is a proxy that is dynamically created at run time by a CRL based on metadata information in an interop assembly. For. NET client, the RCW behaves like any other CLR object, but the RCW actually plays the role of an agent, which is responsible for the. NET client and COM object (marshalling) calls. The side effect of this relationship is the mismatch between the object lifetime management determined by COM world and the uncertain garbage collection in the. NET world. This mismatch can result in objects not being freed from memory, application hangs, or other unpredictable behavior.

One of the main ways to reconcile the two is to invoke the Marshal.ReleaseComObject method for each COM object that you have instantiated. The Marshal.ReleaseComObject method defines the release that a COM object must determine, and after it is invoked you can be sure that there is no more unused object left in memory.