Walkthrough: Retrieve cached data from a workbook on a server

Applies to: Visual Studio Visual Studio for Mac

This walkthrough demonstrates how to retrieve data from a dataset that is cached in a Microsoft Office Excel workbook without starting Excel by using the ServerDocument class.

Applies to: The information in this topic applies to document-level projects for Excel. For more information, see Features available by Office application and project type.

This walkthrough illustrates the following tasks:

• Defining a dataset that contains data from the AdventureWorksLT database.

• Creating instances of the dataset in an Excel workbook project and a console application project.

• Creating a ListObject that is bound to the dataset in the workbook, and populating the ListObject with data when the workbook is opened.

• Adding the dataset in the workbook to the data cache.

• Reading data from the cached dataset into the dataset in the console application, without starting Excel.

  Although this walkthrough assumes that you are running the code on your development computer, the code demonstrated by this walkthrough can be used on a server that does not have Excel installed.

Prerequisites

You need the following components to complete this walkthrough:

• An edition of Visual Studio that includes the Microsoft Office developer tools. For more information, see Configure a computer to develop Office solutions.

• Excel 2013 or Excel 2010.

• Access to a running instance of Microsoft SQL Server or Microsoft SQL Server Express that has the AdventureWorksLT sample database attached to it. You can download the AdventureWorksLT database from the CodePlex website. For more information about attaching a database, see the following topics:

  • To attach a database by using SQL Server Management Studio or SQL Server Management Studio Express, see How to: Attach a database (SQL Server Management Studio).

  • To attach a database by using the command line, see How to: Attach a database file to SQL Server Express.

Create a class library project that defines a dataset

To use the same dataset in an Excel workbook project and a console application, you must define the dataset in a separate assembly that is referenced by both of these projects. For this walkthrough, define the dataset in a class library project.

Create the class library project

1. Start Visual Studio.

2. On the File menu, point to New, and then click Project.

3. In the templates pane, expand Visual C# or Visual Basic, and then click Windows.

4. In the list of project templates, select Class Library.

5. In the Name box, type AdventureWorksDataSet.

6. Click Browse, navigate to your %UserProfile%\My Documents (for Windows XP and earlier) or %UserProfile%\Documents (for Windows Vista) folder, and then click Select Folder.

7. In the New Project dialog box, ensure that the Create directory for solution check box is not selected.

8. Click OK.

   Visual Studio adds the AdventureWorksDataSet project to Solution Explorer and opens the Class1.cs or Class1.vb code file.

9. In Solution Explorer, right-click Class1.cs or Class1.vb, and then click Delete. You do not need this file for this walkthrough.

Define a dataset in the class library project

Define a typed dataset that contains data from the AdventureWorksLT database for SQL Server 2005. Later in this walkthrough, you will reference this dataset from an Excel workbook project and a console application project.

The dataset is a typed dataset that represents the data in the Product table of the AdventureWorksLT database. For more information about typed datasets, see Dataset tools in Visual Studio.

Define a typed dataset in the class library project

1. In Solution Explorer, click the AdventureWorksDataSet project.

2. If the Data Sources window is not visible, display it by, on the menu bar, choosing View > Other Windows > Data Sources.

3. Choose Add New Data Source to start the Data Source Configuration Wizard.

4. Click Database, and then click Next.

5. If you have an existing connection to the AdventureWorksLT database, choose this connection and click Next.

   Otherwise, click New Connection, and use the Add Connection dialog box to create the new connection. For more information, see Add new connections.

6. In the Save the Connection String to the Application Configuration File page, click Next.

7. In the Choose Your Database Objects page, expand Tables and select Product (SalesLT).

8. Click Finish.

   The AdventureWorksLTDataSet.xsd file is added to the AdventureWorksDataSet project. This file defines the following items:

   • A typed dataset named AdventureWorksLTDataSet. This dataset represents the contents of the Product table in the AdventureWorksLT database.

   • A TableAdapter named ProductTableAdapter. This TableAdapter can be used to read and write data in the AdventureWorksLTDataSet. For more information, see TableAdapter overview.

     You will use both of these objects later in this walkthrough.

9. In Solution Explorer, right-click AdventureWorksDataSet and click Build.

   Verify that the project builds without errors.

Create an Excel workbook project

Create an Excel workbook project for the interface to the data. Later in this walkthrough, you will create a ListObject that displays the data, and you will add an instance of the dataset to the data cache in the workbook.

Create the Excel workbook project

1. In Solution Explorer, right-click the AdventureWorksDataSet solution, point to Add, and then click New Project.

2. In the templates pane, expand Visual C# or Visual Basic, and then expand Office/SharePoint.

3. Under the expanded Office/SharePoint node, select the Office Add-ins node.

4. In the list of project templates, select the Excel 2010 Workbook or Excel 2013 Workbook project.

5. In the Name box, type AdventureWorksReport. Do not modify the location.

6. Click OK.

   The Visual Studio Tools for Office Project Wizard opens.

7. Ensure that Create a new document is selected, and click OK.

   Visual Studio opens the AdventureWorksReport workbook in the designer and adds the AdventureWorksReport project to Solution Explorer.

Add the dataset to data sources in the Excel workbook project

Before you can display the dataset in the Excel workbook, you must first add the dataset to data sources in the Excel workbook project.

1. In Solution Explorer, double-click Sheet1.cs or Sheet1.vb under the AdventureWorksReport project.

   The workbook opens in the designer.

2. On the Data menu, click Add New Data Source.

   The Data Source Configuration Wizard opens.

3. Click Object, and then click Next.

4. In the Select the Object You Wish to Bind to page, click Add Reference.

5. On the Projects tab, click AdventureWorksDataSet and then click OK.

6. Under the AdventureWorksDataSet namespace of the AdventureWorksDataSet assembly, click AdventureWorksLTDataSet and then click Finish.

   The Data Sources window opens, and AdventureWorksLTDataSet is added to the list of data sources.

Create a ListObject that is bound to an instance of the dataset

To display the dataset in the workbook, create a ListObject that is bound to an instance of the dataset. For more information about binding controls to data, see Bind data to controls in Office solutions.

1. In the Data Sources window, expand the AdventureWorksLTDataSet node under AdventureWorksDataSet.

2. Select the Product node, click the drop-down arrow that appears, and select ListObject in the drop-down list.

   If the drop-down arrow does not appear, confirm that the workbook is open in the designer.

3. Drag the Product table to cell A1.

   A ListObject control named productListObject is created on the worksheet, starting in cell A1. At the same time, a dataset object named adventureWorksLTDataSet and a BindingSource named productBindingSource are added to the project. The ListObject is bound to the BindingSource, which in turn is bound to the dataset object.

Add the dataset to the data cache

To enable code outside the Excel workbook project to access the dataset in the workbook, you must add the dataset to the data cache. For more information about the data cache, see Cached data in document-level customizations and Cache data.

1. In the designer, click adventureWorksLTDataSet.

2. In the Properties window, set the Modifiers property to Public.

3. Set the CacheInDocument property to True.

Initialize the dataset in the workbook

Before you can retrieve the data from the cached dataset by using the console application, you must first populate the cached dataset with data.

1. In Solution Explorer, right-click the Sheet1.cs or Sheet1.vb file and click View Code.

2. Replace the Sheet1_Startup event handler with the following code. This code uses an instance of the ProductTableAdapter class that is defined in the AdventureWorksDataSet project to fill the cached dataset with data, if it is currently empty.

   private AdventureWorksDataSet.AdventureWorksLTDataSetTableAdapters.ProductTableAdapter productTableAdapter =
       new AdventureWorksDataSet.AdventureWorksLTDataSetTableAdapters.ProductTableAdapter();
   
   private void Sheet1_Startup(object sender, System.EventArgs e)
   {
       if (this.NeedsFill("adventureWorksLTDataSet"))
       {
           this.productTableAdapter.Fill(this.adventureWorksLTDataSet.Product);
       }
   }
   

   Private ProductTableAdapter As New  _
       AdventureWorksDataSet.AdventureWorksLTDataSetTableAdapters.ProductTableAdapter()
   
   Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
       If Me.NeedsFill("AdventureWorksLTDataSet") Then
           Me.ProductTableAdapter.Fill(Me.AdventureWorksLTDataSet.Product)
       End If
   End Sub
   

Checkpoint

Build and run the Excel workbook project to ensure that it compiles and runs without errors. This operation also fills the cached dataset and saves the data in the workbook.

Build and run the project

1. In Solution Explorer, right-click the AdventureWorksReport project, choose Debug, and then click Start new instance.

   The project is built, and the workbook opens in Excel. Verify the following:

   • The ListObject fills with data.

   • The value in the ListPrice column for the first row of the ListObject is 1431.5. Later in this walkthrough, you will use a console application to modify the values in the ListPrice column.

2. Save the workbook. Do not modify the file name or the location of the workbook.

3. Close Excel.

Create a console application project

Create a console application project to use to modify data in the cached dataset in the workbook.

1. In Solution Explorer, right-click the AdventureWorksDataSet solution, point to Add, and then click New Project.

2. In the Project Types pane, expand Visual C# or Visual Basic, and then click Windows.

3. In the Templates pane, select Console Application.

4. In the Name box, type DataReader. Do not modify the location.

5. Click OK.

   Visual Studio adds the DataReader project to Solution Explorer and opens the Program.cs or Module1.vb code file.

Retrieve data from the cached dataset by using the console application

Use the ServerDocument class in the console application to read the data into a local AdventureWorksLTDataSet object. To confirm that the local dataset was initialized with data from the cached dataset, the application displays the number of rows in the local dataset.

Retrieve data from the cached dataset

1. In Solution Explorer, right-click the DataReader project and click Add Reference.

2. On the .NET tab, select Microsoft.VisualStudio.Tools.Applications.ServerDocument.

3. Click OK.

4. In Solution Explorer, right-click the DataReader project and click Add Reference.

5. On the Projects tab, select AdventureWorksDataSet, and click OK.

6. Open the Program.cs or Module1.vb file in the code editor.

7. Add the following using (for C#) or Imports (for Visual Basic) statement to the top of the code file.

   using Microsoft.VisualStudio.Tools.Applications;
   

   Imports Microsoft.VisualStudio.Tools.Applications
   

8. Add the following code to the Main method. This code declares the following objects:

   • An instance of the AdventureWorksLTDataSet type that is defined in the AdventureWorksDataSet project.

   • The path to the AdventureWorksReport workbook in the build folder of the AdventureWorksReport project.

   • A ServerDocument object to use to access the data cache in the workbook.

     Note

     The following code assumes that the workbook is saved using the .xlsx extension. If the workbook in your project has a different extension, modify the path as necessary.

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

     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
     

9. Add the following code to the Main method, after the code you added in the previous step. This code performs the following tasks:

   • It uses the CachedData property of the ServerDocument class to access the cached dataset in the workbook.

   • It reads the data from the cached dataset into the local dataset.

   • It displays the number of rows in the local dataset, to confirm that it has data.

     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();
     }
     

     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
     

10. On the Build menu, click Build DataReader.

Test the project

When you run the console application, it displays the number of rows in the local dataset.

Test the workbook

1. In Solution Explorer, right-click the DataReader project, point to Debug, and then click Start new instance.

   Verify that the application reports that the local dataset has 295 rows.

2. Press Enter to close the application.

Next steps

You can learn more about working with cached data from these topics:

• Changing the data in a cached dataset without starting Excel. For more information, see Walkthrough: Change cached data in a workbook on a server.

See also

• Walkthrough: Insert data into a workbook on a server
• Walkthrough: Change cached data in a workbook on a server