Caching Metadata
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;InitiateOAuth=GETANDREFRESH;Spreadsheet=NorthwindOrders"; using (GoogleSheetsConnection conn = new GoogleSheetsConnection(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;InitiateOAuth=GETANDREFRESH;Spreadsheet=NorthwindOrders" Using conn As New GoogleSheetsConnection(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