How to: Retrieve Cached Data from a Workbook on a Server

Applies to
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Project type Document-level projects Microsoft Office version Excel 2007 Excel 2003 For more information, see Features Available by Application and Project Type.

You can retrieve data from the cache of a Microsoft Office Excel workbook that is part of a document-level Visual Studio Tools for Office project without running Excel. This makes it possible to get data from Excel workbooks that are stored on a server.

The code to retrieve the data must be outside of the Visual Studio Tools for Office project assembly that is associated with document you are working with, for example in an ASP.NET Web page, a console application, or a Windows Forms application.

For step-by-step instructions for using the code example in this topic, see Walkthrough: Retrieving Cached Data from a Workbook on a Server.

Example

The following code example first creates an instance of a typed dataset named AdventureWorksLTDataSet. Next, the code uses the CachedData property of the ServerDocument class to access a filled instance of the same typed dataset that is already cached in an Excel workbook, and then reads the data from the cached dataset into the local dataset.

Dim productDataSet As New AdventureWorksDataSet.AdventureWorksLTDataSet()
Dim workbookPath As String = System.Environment.GetFolderPath( _
    Environment.SpecialFolder.MyDocuments) & _
    "\AdventureWorksReport\bin\Debug\AdventureWorksReport.xlsx" 
Dim serverDocument1 As ServerDocument = Nothing 

Try
    serverDocument1 = New ServerDocument(workbookPath)
    Dim dataHostItem1 As CachedDataHostItem = _
        serverDocument1.CachedData.HostItems("AdventureWorksReport.Sheet1")
    Dim dataItem1 As CachedDataItem = dataHostItem1.CachedData("AdventureWorksLTDataSet")

    If dataItem1 IsNot Nothing Then
        Console.WriteLine("Before reading data from the cache dataset, the local dataset has " & _
            "{0} rows.", productDataSet.Product.Rows.Count.ToString())

        ' Read the cached data from the worksheet dataset into the local dataset. 
        Dim schemaReader As New System.IO.StringReader(dataItem1.Schema)
        Dim xmlReader As New System.IO.StringReader(dataItem1.Xml)
        productDataSet.ReadXmlSchema(schemaReader)
        productDataSet.ReadXml(xmlReader)

        Console.WriteLine("After reading data from the cache dataset, the local dataset has " & _
            "{0} rows.", productDataSet.Product.Rows.Count.ToString())
    Else
        Console.WriteLine("The data object is not found in the data cache.")
    End If 
Catch ex As System.IO.FileNotFoundException
    Console.WriteLine("The specified workbook does not exist.")
Catch ex As System.Xml.XmlException
    Console.WriteLine("The data object has invalid XML information.")
Finally 
    If Not (serverDocument1 Is Nothing) Then
        serverDocument1.Close()
    End If
    Console.WriteLine(vbLf & vbLf & "Press Enter to close the application.")
    Console.ReadLine()
End Try

AdventureWorksDataSet.AdventureWorksLTDataSet productDataSet =
    new AdventureWorksDataSet.AdventureWorksLTDataSet();
string workbookPath = System.Environment.GetFolderPath(
    Environment.SpecialFolder.MyDocuments) +
    @"\AdventureWorksReport\bin\Debug\AdventureWorksReport.xlsx";
ServerDocument serverDocument1 = null;

try
{
    serverDocument1 = new ServerDocument(workbookPath);
    CachedDataHostItem dataHostItem1 =
        serverDocument1.CachedData.HostItems["AdventureWorksReport.Sheet1"];
    CachedDataItem dataItem1 = dataHostItem1.CachedData["adventureWorksLTDataSet"];

    if (dataItem1 != null)
    {
        Console.WriteLine("Before reading data from the cache dataset, the local dataset has " +
            "{0} rows.", productDataSet.Product.Rows.Count.ToString());

        // Read the cached data from the worksheet dataset into the local dataset.
        System.IO.StringReader schemaReader = new System.IO.StringReader(dataItem1.Schema);
        System.IO.StringReader xmlReader = new System.IO.StringReader(dataItem1.Xml);
        productDataSet.ReadXmlSchema(schemaReader);
        productDataSet.ReadXml(xmlReader);

        Console.WriteLine("After reading data from the cache dataset, the local dataset has " +
            "{0} rows.", productDataSet.Product.Rows.Count.ToString());
    }
    else
    {
        Console.WriteLine("The data object is not found in the data cache.");
    }
}
catch (System.IO.FileNotFoundException)
{
    Console.WriteLine("The specified workbook does not exist.");
}
catch (System.Xml.XmlException)
{
    Console.WriteLine("The data object has invalid XML information.");
}
finally
{
    if (serverDocument1 != null)
    {
        serverDocument1.Close();
    }

    Console.WriteLine("\n\nPress Enter to close the application.");
    Console.ReadLine();
}

Compiling the Code

The code example in this topic is designed to be used with the following applications:

• A console application that has access to a class library project that defines a typed dataset. The code runs in the console application.

• An Excel workbook that is part of a document-level customization for Excel 2003 or Excel 2007. The workbook has a cached dataset named AdventureWorksLTDataSet that contains some data.

For step-by-step instructions for using the code, see Walkthrough: Retrieving Cached Data from a Workbook on a Server.

See Also

Tasks

Walkthrough: Retrieving Cached Data from a Workbook on a Server

How to: Cache Data for Use Offline or on a Server

How to: Change Cached Data in a Workbook on a Server

Concepts

Accessing Data in Documents on the Server

Data Model in Document-Level Customizations