What is Cached Data ?

Cache the state of the host items - this is called the Data Island
Added in 2005 VSTO Runtime (v2)
A data cache refers to the capability of VSTO to store data locally within a document.
This is useful because data can read and write to the data cache when information resides in a database but you have disconnected scenarios


A data cache is a place where you can store and retrieve data within a document.
VSTO lets you cache data inside Excel and Word documents.
This only works at document level (not at application level)

2003 Solutions
Word XML format is full fidelity but the data island is not shared as a human readable, editable XML
Excel XML format is not - Excel loses information about the VSTO customisation when a document is saved in XML.

The data cache travels with a document when you copy or send it.
This enables an offline scenario when you are not connected to the server.

This is also useful when you bind data to a document and want the users to update the data when they are not connected to the server.
Cached data is stored in a hidden ActiveX control on Sheet1 called Runtime Storage Control.

At design time you can cache data in a document in two ways:
1) Setting the CacheInDocument property to True for the instance of the dataset.
2) Adding an attribute named Cached to an object or to the declaration that contains the data to be cached.

CachedInDocument property

Add the dataset to your project using the Visual Studio Designer
Add the datasource using the Data Source window
Create and select an instance of the dataset
In the properties set the CacheInDocument property to true.

Cached Attribute

Often referred to as a data island
When a cached data item in the document contains data the document interacts with the data island.
Data sources that are declared using the Cached attribute are persisted in the data island

You can mark a data item to be cached in the document, so that it is available offline
This also make it possible for the data in the document to be manipulated by other code when the document is stored on a server.

In VSTO create an Excel workbook project
Create 2 named ranges
Add the following lines of code to your worksheet class to create two public string fields
They must be Public as Private is the default)

Public sNamedRange1 As String

Public sNamedRange2 As String

On startup set the named range values to be the values that are cached
Update the cached values when the named range values are changed manually.

CacheInDocument Property

Reading from Data Cache

You can read and write this data using the ServerDocument
This requires adding a reference to the VSTO runtime
(Add Reference)(.NET tab)

Dim objServerDocument As ServerDocument 
Dim objCachedData As CachedData
Dim objCachedDataHostItem As CachedDataHostItem
Dim objCachedDataItem As CachedDataItem

objServerDocument = New ServerDocument(sFillPath)
objCachedData = objServerDocument.CachedData
objCachedDataHostItem = objCachedData.HostItems("ExcelWorkbook.Sheet1")
objCachedDataItem = objCachedDataHostItem.CachedData("NamedRange1")

The CachedDataItem is actually the XML so you can deserialise it to a string
For best performance when deserialising you should use the document object model (DOM) to parse the XML manually and extract the values.

For Each objCachedDataHostItem In objCachedData.HostItems 

Writing to Data Cache

The CachedDataItem class provides a method to automatically serialize the data into the XML property.



Clearing the Data Cache

Using ServerDocument you can clear the cached data from a document.
This is useful when you want to deploy a VSTO solution and you don't want your test data to be deployed with it.

objServerDocument = New ServerDocument(sFilePath) 
objCachedData = objServerDocument.CachedData
objCachedData.ClearData() ''this just removes the data
objCachedData.Clear() ''this completely removes the data cache

© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited