This section describes how to enable caching metadata and how to update the metadata cache.

Before being able to query data, the connector requires relevant metadata to be retrieved. By default, metadata is cached in memory and shared across connections. But if you want to persist across processes, or if metadata requests are expensive, the solution is to cache the metadata to disk.

Enable Caching Metadata

To enable caching of metadata, set CacheMetadata = true and see Configuring the Cache Connection for instructions on how to configure your connection string. The provider caches the metadata the first time it is needed and uses the metadata cache for subsequent requests.

Update the Metadata Cache

Because metadata is cached, changes to metadata on the live source, for example, adding or removing a column or attribute, are not automatically reflected in the metadata cache. To get updates to the live metadata, you need to delete or drop the cached data.

Cache Metadata from Code

The following code can be used to build the metadata cache. This is especially useful when using the data provider in Entity Framework.

C#

String connectionString = "Cache Location=C:\\cache.db;Cache MetaData=True;Url=https://myorg.sharepoint.com;[email protected];Password=password;File=Book1.xlsx;";
using (Microsoft SharePoint ExcelConnection conn = new Microsoft SharePoint ExcelConnection(connectionString)) {
  conn.Open();
  DataTable table = conn.GetSchema("Tables");
  Console.WriteLine("Total number of tables found: " + table.Rows.Count);

  foreach (DataRow row in databaseSchema.Rows) {
    foreach (DataColumn column in databaseSchema.Columns) {
      Console.WriteLine(row[column]);
    }
  }
  Console.WriteLine();
  Console.WriteLine("All tables cached.");
}

VB.NET

Dim connectionString As String = "Cache Location=C:\\cache.db;Cache MetaData=True;Url=https://myorg.sharepoint.com;[email protected];Password=password;File=Book1.xlsx;"
Using conn As New Microsoft SharePoint ExcelConnection(connectionString)
  conn.Open()
  Dim databaseSchema As DataTable = conn.GetSchema("Tables")
  Console.WriteLine("Total number of tables found: " + databaseSchema.Rows.Count.ToString())

  For Each row As DataRow In databaseSchema.Rows
    For Each column As DataColumn In databaseSchema.Columns
      Console.WriteLine(row(column))
    Next
  Next
  Console.WriteLine()
  Console.WriteLine("All tables cached.")
  Console.ReadKey()
End Using