Automatically caching data is useful when you do not want to rebuild the cache for each query. When you query data for the first time, the provider automatically initializes and builds a cache in the background. When AutoCache = true, the provider uses the cache for subsequent query executions, resulting in faster response times.

If replication is enabled, the data is generated once and then copied to local and cloud data stores. With incremental updates, the provider achieves a performance advantage over dropping the cached tables and retrieving the entire table again on every refresh. With iterative updates, the provider only performs the query from the last time that the date was refreshed. If replication is not enabled, updates to the cache require downloading the entire data set.

Configuring Automatic Caching

To automatically update the cache and return results from the local cache, set the following connection string properties:

• AutoCache: This property automatically updates the cache when the value is set to true.
• CacheTolerance: This property ensures that the data retrieved from the database is the most current version. The default value is 600 seconds (10 minutes). The provider checks with the data source for newer records after the tolerance interval has expired. Otherwise, it returns the data directly from the cache.

Caching the TradingAccounts Table

The following example caches the TradingAccounts table in the file specified by the CacheLocation property of the connection string.

C#

String connectionString = "Cache Location=C:\\cache.db;AutoCache=true;URL=http://localhost:5493/sdata/accounts50/GCRM/{C4C863BE-B098-4A7D-A78B-D7A92B8ADB59};User=Manager;Password=xxxxxx;";
using (Sage50UKConnection connection = new Sage50UKConnection(connectionString)) {
  Sage50UKCommand cmd = new Sage50UKCommand("SELECT TradingAccountUUID, Name FROM TradingAccounts WHERE TradingAccountUUID = 'c2ef66a5-a545-413b-9312-79a53caadbc4'", connection);
  Sage50UKDataReader rdr = cmd.ExecuteReader();
  while (rdr.Read()) {
    Console.WriteLine("Read and cached the row with TradingAccountUUID " + rdr["TradingAccountUUID"]);
  }
}

VB.NET

Dim connectionString As [String] = "Cache Location=C:\\cache.db;AutoCache=true;URL=http://localhost:5493/sdata/accounts50/GCRM/{C4C863BE-B098-4A7D-A78B-D7A92B8ADB59};User=Manager;Password=xxxxxx;"
Using connection As New Sage50UKConnection(connectionString)
  Dim cmd As New Sage50UKCommand("SELECT TradingAccountUUID, Name FROM TradingAccounts WHERE TradingAccountUUID = 'c2ef66a5-a545-413b-9312-79a53caadbc4'", connection)
  Dim rdr As Sage50UKDataReader = cmd.ExecuteReader()
  While rdr.Read()
	  Console.WriteLine("Read and cached the row with TradingAccountUUID " + rdr("TradingAccountUUID"))
  End While
End Using

Common Use Case

A common use for automatically caching data is to improve driver performance when making repeated requests to a live data source, such as building a report or creating a visualization. With auto caching enabled, repeated requests to the same data may be executed in a short period of time, but within an allowable tolerance (CacheTolerance) of what is considered "live" data.