5. VSTO Bug Tracker : Using Cached Data
We are now going to convert our previous sample to use cached data. In our current solution we have code that when the document loads, it calls a web service to get the bug data. We are going to use the cached data feature to allow us to call the web service at a different time to fill in the dataset in the document without having to start Excel.
Why would we want to do this? Well, several reasons. First, we can improve the load time of our document by updating the data in the document in advance. This also is handy to update the data in the document if the document is going to be opened outside the firewall where the web service can’t be accessed. So a process running on a machine within the firewall can update the data in the document and then give the document to a machine running outside the firewall. As mentioned in the last article, it can also allow me to update the bug data in my workbooks in the middle of the night via a scheduled process so I can hit the web service when traffic is low.
To convert our previous solution to used cached data, we simply add the CachedData attribute to the DataSet we declared in Sheet1.
<Cached()> Public bugDataset As DataSet
Next, we’ll modify our implementation of PopulateSpreadsheet. The change here is to uncomment out the if statement If NeedsFill("bugDataset") Then which now brackets the code that calls the web service to update bugDataset. What will happen now is that there are two ways of initializing the dataset in the document. If the document gets run immediately after it is built, bugDataSet will not yet be cached in the document, so the bugDataSet will be updated by the web service call. On subsequent runs after the document is saved, the web service won’t be called because the dataset will be cached in the document already. The second way that the dataset can be initialized is through a console app that we will write presently.
If NeedsFill("bugDataset") Then
Application.StatusBar = "Updating data from bug web service..."
Dim bugService As BugWorkbook.BugService.BugService = New BugWorkbook.BugService.BugService()
bugDataset = bugService.BulkDataExport("", "")
End If
Now, lets create a console application that can populate the data set into the document without starting Excel. Use File > Add > New Project… to add a new console application to the solution (under Language, Windows, Console Application) and call it UpdateExcelWorkbook.
In this console application, we will be using a class called ServerDocument which lets us access the cached data without starting up Excel. Right click on the project node for the console application and choose “Add Reference…”. From the Add Reference dialog, locate the .NET assembly called Microsoft.VisualStudio.Tools.Applications.ServerDocument.v9.0 and add it as a reference.
Also add using Microsoft.VisualStudio.Tools.Applications; (Imports Microsoft.VisualStudio.Tools.Applications for VB) and using System.Data; (Imports System.Data) to the Program.cs (or Program.vb) file.
We must also add a web reference to BugService—follow the steps given in article 4, and be sure to name it BugService.
Now we’ll write the code for the console application. The code creates a new instance of the ServerDocument class, iterates over the cached data until it finds a cached dataset called “bugDataSet”—the name of the public variable we added to Sheet1. It then updates that dataset by calling the web service, then using a method called SerializeDataInstance which writes the dataset into the document. Finally it calls doc.Save then exits.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.VisualStudio.Tools.Applications;
using System.Data;
namespace UpdateExcelWorkbook
{
class Program
{
static void Main(string[] args)
{
if (args.Length != 1)
{
Console.WriteLine("Usage:");
Console.WriteLine(" UpdateExcelWorkbook.exe myfile.xlsx");
return;
}
string filename = args[0];
ServerDocument doc = null;
try
{
doc = new ServerDocument(filename);
foreach (CachedDataHostItem view in
doc.CachedData.HostItems)
{
foreach (CachedDataItem item in view.CachedData)
{
if (item.Id == "bugDataset")
{
BugService.BugService bugService = new BugService.BugService();
DataSet ds = bugService.BulkDataExport("", "");
item.SerializeDataInstance(ds);
doc.Save();
continue;
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("Unexpected Exception:" + filename);
Console.WriteLine(ex.ToString());
}
finally
{
if (doc != null)
{
doc.Close();
}
}
}
}
}
Now, to make this run, go back to the BugWorkbook project node, right click on it, and choose “Open Folder in Windows Explorer”. We are trying to get the full path to the excel workbook that is being built by Visual Studio. Double click on the bin directory, then the debug directory, then copy the whole path to the clipboard. On my computer it is C:\Users\ecarter\Documents\Visual Studio 2008\Projects\BugService\BugWorkbook\bin\Debug\BugWorkbook.xlsx
Right click on the UpdateExcelWorkbook project node, and choose properties. Click the Debug tab. In that tab, paste into the Command line arguments text box the full path to the workbook as shown below.
Now, set the UpdateExcelWorkbook project to be the startup project by right clicking on the UpdateExcelWorkbook project node and choosing “Set as Startup Project”. Clean and rebuild the solution. This will rebuild the Excel workbook “ExcelWorkbook.xlsx” which will now have an empty data set in it called bugDataSet after the rebuild. After the console application runs, bugDataSet will have been populated into the document.
To verify that is so, navigate back to the directory where the excel workbook (now modified by the console application) lives (e.g. C:\Users\ecarter\Documents\Visual Studio 2008\Projects\BugService\BugWorkbook\bin\Debug\BugWorkbook.xlsx) and launch the workbook. As it loads, note that the Status Bar of Excel does not display the text “Updating data from web service” because the dataset is already in the document. When it starts up, it immediately binds the already populated dataset to the list object in the document.