Visual Studio Tools for Office: Using Visual Basic 2005 with Excel, Word, Outlook, and InfoPath

2017-07-07 02:10:07

When a customized document with data-bound controls starts, the datasets have to be filled in somehow before the controls display the data. As you saw at the beginning of this chapter, if you use the Data Sources pane to create data-bound controls, Visual Studio automatically emits code to fill the datasets using custom-generated adapters:

Private Sub Sheet1_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup If Me.NeedsFill("NorthwindDataSet") Then Me.ProductsTableAdapter.Fill(Me.NorthwindDataSet.Products) End If

But under what circumstances would the dataset ever not need to be filled at startup? Consider a spreadsheet with a dataset containing a single table. One worksheet has a single datum bound to a named range. If you save that spreadsheet, only that one datum is going to be saved; all the other information in the dataset is just a structure in memory at runtime that will be lost when the workbook is closed. The data is potentially going to have to be fetched anew every time the worksheet host control starts.

One of the key benefits of Word and Excel documents is that they are useful even on machines that are not connected to networks. (Working on a spreadsheet or document on a laptop on an airplane is the canonical scenario.) It would be unfortunate indeed if a data-bound customized document required your users always to be connected.

Fortunately, VSTO solves this problem. Click the icon for the typed dataset in the component tray and then look at the Properties pane for this component. A Cached property defaults to False. If you set it to TRue, when you save the document, the VSTO runtime will turn the dataset into XML and store the XML in a data island inside the document.

The next time the document starts, the VSTO runtime detects that the data island contains a cached dataset and fills in the dataset from the cache. The call to NeedsFill in the Startup event will then return False, and the startup code will not attempt to fill in the data from the adapter. Essentially, the NeedsFill method returns False if the object was loaded from the cache automatically, true otherwise.

Caching Your Own Data Types

You can cache almost any kind of data in the XML data island, not just datasets. To be cacheable by the VSTO runtime, the data must meet the following criteria:

The data must be stored in a public member variable or property of a host item (a customized worksheet, workbook, chart sheet, or document class).

If stored in a property, the property must have no parameters and must be both readable and writable.

The runtime type of the data must be dataset (or a subclass), data table (or a subclass), or any type serializable by the System.Xml.Serialization.XmlSerializer object.

To tell Visual Studio that you would like to cache a member variable, just add the Cached attribute to its declaration. Make sure that you check whether the member was already filled in from the cache; the first time the document is run, there will be no data in the cache, so you have to fill in the data somehow. You could use the code in Listing 17.5, for example.

Listing 17.5. Auto-Generated Table-Filling Code

Public Class Sheet1 <Cached()> _ Public CustomerName As String Private Sub Sheet1_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup If Me.NeedsFill("CustomerName") Then Me.CustomerName = "Unknown Customer" End If End Sub End Class

Dynamically Adding and Removing Cached Members from the Data Island

Cached data can be large. What if you decide that at some point you want to stop caching a particular dataset in the data island? Or, conversely, what if you do not want to fill in a dataset automatically and store it in the cache on the first run of the document, but you want to start caching a member based on some other criterion? It would be unfortunate if the only way to tell VSTO to cache a member in the data island was to tag it with the Cached attribute at design time.

Therefore, all customized view item classes generated by a VSTO project expose four handy functions that you can call to query and manipulate the caching semantics, as follows:

Function NeedsFill(ByVal memberName As String) As Boolean

Function IsCached(ByVal memberName As String) As Boolean

Sub StartCaching(ByVal memberName As String)

Sub StopCaching(ByVal memberName As String)

NeedsFill, we have already seen. If the named member was initialized from the data island by the VSTO runtime when the customization started, this returns False; otherwise, it returns true.

IsCached might seem like it is just the opposite of NeedsFill, but it is not. NeedsFill tells you whether the item in question was loaded out of the data island; IsCached tells you whether the item will be saved to the data island when the user saves the document.

StartCaching and StopCaching dynamically add members to and remove members from the set of members that will be saved to the data island. It is illegal to call StartCaching on a member already in the cache or StopCaching on a member not in the cache; use IsCached to double-check, if you need to. The same rules that apply to cached members added to the cache by the Cached attribute apply to members added dynamically; only call StartCaching on public fields or public readable/writable properties.

Note

If a cached member is set to Nothing at the time that the document is saved, the VSTO runtime assumes that you intended to call StopCaching on the member, and it will be removed from the data island.

Advanced Topic: Using ICachedType

Suppose that you have a large cached dataset that you loaded out of the data island when the customization started. Serializing a dataset into XML can be a time- and memory-consuming process, so if there have been no changes to the dataset when the document is saved, the VSTO runtime is pretty smart about skipping the serialization.

This is also important if the user closes Word or Excel without saving the document. The host application needs to know whether to create the "Do you want to save changes?" dialog box. If the dataset is clean, there are no changes to save, and the dialog box should not be created.

How can VSTO tell whether a custom class added to the cached members is dirty? The VSTO runtime can track the Change events on a dataset or data table to tell whether they are dirty, but in general, any other types simply have to be written out every time. To prevent the "Do you want to save?" dialog box, the VSTO runtime must pessimistically serialize the object and compare it with the state that it loaded; again, this is potentially time-consuming.

If you require more finely grained control over the caching process for a particular member, you can implement the ICachedType interface. This interface enables you not only to hint to the VSTO runtime whether the item needs to be reserialized, but also allows you to abort a save or load dynamically and receive notification when the save or load is done. Listing 17.6 shows its members.

Listing 17.6. The ICachedType Interface

Public Interface ICachedType Sub AfterLoad() Sub AfterSave() Function BeforeLoad() As Boolean Function BeforeSave() As Boolean ReadOnly Property IsDirty As Boolean End Interface

If you implement this interface on a particular class and then add a member containing an instance to the class, the VSTO runtime will do the following:

Call your BeforeLoad method when the item is loaded out of the cache. If you return False, the load will be aborted.

Call your AfterLoad method when the XMLSerializer is done loading your object. (If you are tracking the dirty state of the object, this would be a good time to set it to clean.)

Call IsDirty before saving the document; if the object has no changes since it was last loaded or saved, return False to avoid unnecessary expensive serializations.

Call BeforeSave before saving the member to the data island. If for some reason you determine that the object is not in a state that can be saved, you can return False, and the object will be removed from the cache.

Call AfterSave when the XMLSerializer is done saving the document to the data island. (Again, this would be a good time to note that the object is clean.)

Manipulating the Serialized XML Directly

Chapter 18, "Server Data Scenarios," discusses how to view and edit the contents of the data island, start and stop caching members, and so on without actually starting Word or Excel.