ADO.NET Provider for Microsoft Dynamics 365

Build 22.0.8462

Automatically Caching Data

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 GoalHeadings Table

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

C#

String connectionString = "Cache Location=C:\\cache.db;AutoCache=true;InitiateOAuth=GETANDREFRESH;OrganizationUrl=https://myaccount.operations.dynamics.com/;Edition=Sales;";
using (Dynamics365Connection connection = new Dynamics365Connection(connectionString)) {
  Dynamics365Command cmd = new Dynamics365Command("SELECT GoalHeadingId, GoalHeadingId FROM GoalHeadings WHERE Name <> 'MyAccount'", connection);
  Dynamics365DataReader rdr = cmd.ExecuteReader();
  while (rdr.Read()) {
    Console.WriteLine("Read and cached the row with GoalHeadingId " + rdr["GoalHeadingId"]);
  }
}

VB.NET

Dim connectionString As [String] = "Cache Location=C:\\cache.db;AutoCache=true;InitiateOAuth=GETANDREFRESH;OrganizationUrl=https://myaccount.operations.dynamics.com/;Edition=Sales;"
Using connection As New Dynamics365Connection(connectionString)
  Dim cmd As New Dynamics365Command("SELECT GoalHeadingId, GoalHeadingId FROM GoalHeadings WHERE Name <> 'MyAccount'", connection)
  Dim rdr As Dynamics365DataReader = cmd.ExecuteReader()
  While rdr.Read()
	  Console.WriteLine("Read and cached the row with GoalHeadingId " + rdr("GoalHeadingId"))
  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.

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462