Caching Automatically

With Auto Cache the RSSBus ADO.NET Provider for Google Spreadsheets will automatically load data into the cache database each time you execute a SELECT statement. Each row returned by the query will be inserted or updated into the corresponding table in the cache database.

This is handled in a streaming fashion: each row is processed into the cache database from the original result set as you read the row from the returned GoogleSheetsDataReader object, so the data is not loaded twice. Any rows you do not read from the returned GoogleSheetsDataReader will not be updated in the cache.

Tip: When using Auto Cache, ensure you always read all rows from the returned GoogleSheetsDataReader.

When using Auto Cache, statements other than SELECT will be executed against the real data table, and not against the cache. This can cause the data in the cache to become out of date.

To access data in the cache with Auto Cache enabled, you need to use the pseudo-table name <table>#Cache. For example, to query the MySpreadsheet table in the cache, execute a "SELECT * FROM [MySpreadsheet#Cache]" statement.

Note: If you run non-SELECT statements on a table_name#Cache table, the changes will not be replicated on the real data source. You will need to keep track of any changes that you make to the cache if you want to keep them in sync with the real data source.

Cache the MySpreadsheets Table

The following example caches data in the .db file specified by the "Cache Location" property of the connection string. The database contains the Accounts table which is populated with the data from the GoogleSheets data source.

C#

String connectionString = "Cache Location=C:\\cache.db;Auto Cache=true;Offline=false;user=myuseraccount;password=mypassword;";
using (GoogleSheetsConnection connection = new GoogleSheetsConnection(connectionString)) {
  GoogleSheetsCommand cmd = new GoogleSheetsCommand("SELECT * FROM MySpreadsheets", connection);
  GoogleSheetsDataReader rdr = cmd.ExecuteReader();
  while (rdr.Read()) {
    Console.WriteLine("Read and cached the row with ID " + rdr["Id"]);
  }
}

VB.NET

Dim connectionString As [String] = "Cache Location=C:\\cache.db;Auto Cache=true;Offline=false;user=myuseraccount;password=mypassword;"
Using connection As New GoogleSheetsConnection(connectionString)
  Dim cmd As New GoogleSheetsCommand("SELECT * FROM MySpreadsheets", connection)
  Dim rdr As GoogleSheetsDataReader = cmd.ExecuteReader()
  While rdr.Read()
	  Console.WriteLine("Read and cached the row with ID " + rdr("Id"))
  End While
End Using