Offline and server-based Office document scenarios

What happens if you bind host controls in a document that's part of a Microsoft Visual Studio Tools for the Microsoft Office System, Version 2.0 solution to a remote data source (such as a Microsoft SQL Server database) and then you want to take the document offline? I mean, I do this with Microsoft Office Word and Microsoft Office Excel documents all the time: I routinely insert numbers and text into Word bookmarks and Excel cells and then take these documents with me in e-mail, store them in various file shares, and so on. Surely, you should be able to persist these numbers and text strings from the remote data source into the documents when you're not connected, right?

We have a unique solution to this scenario. Every Excel and Word document that is part of a Visual Studio Tools for Office, Version 2.0 project contains a data cache that we call a data island. The purpose of the data island is to store a copy of some or all of the source data inside of the document, primarily for offline use. (But, as I'll share later in this post, there is another use for the data island— enabling data mining or data filling of documents on a server without instantiating Word or Excel on that server.)

Here's how it works: when your Visual Studio Tools for Office, Version 2.0 document is online, your view controls (such as NamedRange view controls, ListObject view controls, Bookmark view controls, and so on) can be programmatically connected to the data source through ADO.NET objects. The first time the document starts up, if you have written code to cache the data, when you save the document, the data is cached from the data source into the document. If you later open the document and the connection to the data doesn't exist, the data is reconstituted from the cache. When you later reconnect to the source data, you can programmatically send the cached data back to the data source.

The other benefit of this data caching behavior is that you have the ability to read from and write to the data island inside of the document without starting up Word or Excel. This is very advantageous in server scenarios. Excel and Word are client applications, not designed (nor supported by Microsoft) to run on servers. Instead of instantiating Word or Excel on the server and using the object model to get at the data in cell E12 or the fourth bookmark in the document, you can now access the data island using an API that we've designed just for this purpose. This separation of data management code from visual representation code makes coding easier by not forcing developers to learn the entire Office object models to manipulate the data. We go even farther by enabling document data access that doesn't require Word or Excel to be running or even to be installed on the server.

This leads to very cool scenarios. For example, you can have the users enter the data into documents as they normally do, using the Word and Excel applications and document templates with which they're most productive. Your code can store their data inputs into the data islands and then post the documents to the server. The server can then grab the data out of the data islands and either throw the documents away or modify the data in the data islands and return the documents to the users for further work on their local computers.

I'll provide the code that lets you examine and modify the data islands' contents in a later post.

Many thanks to Eric Lippert and Eric Carter for their contributions to this information.

-- Paul Cornell

This posting is provided "AS IS" with no warranties, and confers no rights.