Caching | DataConnector

Caching

In This Topic

Caching data offers several advantages that can improve overall process performance, including reduced API requests and faster data access.

The ADO.NET provider features an easy-to-use caching procedure that can also be shared by multiple connections. This article demonstrates the two types of caches that are currently supported:

Default cache: An internally managed database (in SQLite).
Specified cache: An externally managed database (for now, in SQL Server). For details, see External Caching.

To enable the caching feature, necessary connection properties are available, such as:

Cache Tolerance: The duration during which the cached data is considered as not needing to be updated with a more recent version from the source database (default is 600 seconds).
Cache Location: The location of the cache in the underlying file system (default is %AppData%/C1DataConnector).

Internal caching

The following code demonstrates how to enable caching for a table by setting the UseCache property to True (by default, it is set to False). The cached data is stored in the file specified by the CacheLocation property in the connection string. For more information on Incremental Caching, refer to this topic.

C#	Copy Code
static string connectionString = $@"Company Id={CompanyId};Use SandBox=true;OAuth Client Secret={OAuthClientSecret};OAuth Client Id={OAuthClientId};OAuth Access Token={OAuthAccessToken}; OAuth Refresh Token={OAuthRefreshToken}; OAuth Token Endpoint={OAuthTokenEndpoint};Minor Version={MinorVersion}; Use Cache=true; Cache Tolerance=500; Cache Location='C:\temp\c1cache.db';"; static void LocalCache() { using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString)) { conn.OAuthTokenRefreshed += OAuthTokenRefreshed; Console.WriteLine("Start Time " + DateTime.Now); conn.Open(); C1QuickBooksOnlineCommand cmd = new C1QuickBooksOnlineCommand(conn, "Select * from Attachables'"); var rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine("Read and cached the row with Id " + rdr["Id"]); } Console.WriteLine("End Time " + DateTime.Now); } }

Copy Code

static string connectionString = $@"Company Id={CompanyId};Use SandBox=true;OAuth Client Secret={OAuthClientSecret};OAuth Client Id={OAuthClientId};OAuth Access Token={OAuthAccessToken};
                  OAuth Refresh Token={OAuthRefreshToken}; OAuth Token Endpoint={OAuthTokenEndpoint};Minor Version={MinorVersion}; 
                  Use Cache=true; Cache Tolerance=500; Cache Location='C:\temp\c1cache.db';";
static void LocalCache()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        conn.OAuthTokenRefreshed += OAuthTokenRefreshed;

        Console.WriteLine("Start Time " + DateTime.Now);
        conn.Open();
        C1QuickBooksOnlineCommand cmd = new C1QuickBooksOnlineCommand(conn, "Select * from Attachables'");
        var rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            Console.WriteLine("Read and cached the row with Id " + rdr["Id"]);
        }
        Console.WriteLine("End Time " + DateTime.Now);
    }
}

External Caching

The ADO.NET provider for QuickBooks Online support external caching, allowing users to store cached data in a separate database. This external database serves as a constant repository and can be accessed by multiple connection objects simultaneously. It includes all tables exposed by the QuickBooks Online provider and offers configuration options specific to the cache provider.

To utilize external caching, the provider supports a specified cache, such as SQL Server. You can specify the connection string using the UseCache, CacheProvider, and CacheConnection keys to enable external caching.

Note: With SQL Server, Cache Provider = ‘Microsoft.Data.SqlClient’ is mandatory so you must create your own database as our cache doesn’t create new Database.

The following code example implements external caching, by setting Use Cache to True, and using 'Microsoft.Data.SqlClient' as the cache provider.

C#	Copy Code
static string connectionString = $@"Url=http://**//*; Use Cache=true; Cache provider='Microsoft.Data.SqlClient'; Cache connection='Server= yourserverid; Database= databasename; User Id= yourId; Password= yourpassword;' Company Id={CompanyId}; Use SandBox=true;OAuth Client Secret={OAuthClientSecret};OAuth Client Id={OAuthClientId};OAuth Access Token={OAuthAccessToken}; OAuth Refresh Token={OAuthRefreshToken}; OAuth Token Endpoint={OAuthTokenEndpoint};Minor Version={MinorVersion}"; static void ExternalCache() { Console.WriteLine("Start Time " + DateTime.Now); using (C1QuickBooksOnlineConnection con = new C1QuickBooksOnlineConnection(connectionString)) { con.Open(); C1QuickBooksOnlineCommand cmd = new C1QuickBooksOnlineCommand(conn, "Select from Attachables'"); var rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine("Read and cached the row with Id " + rdr["Id"]); } Console.WriteLine("End Time " + DateTime.Now); } }

Copy Code

static string connectionString = $@"Url=http://****/****/****; Use Cache=true; Cache provider='Microsoft.Data.SqlClient'; 
                              Cache connection='Server= yourserverid; Database= databasename; User Id= yourId; Password= yourpassword;' Company Id={CompanyId};
                              Use SandBox=true;OAuth Client Secret={OAuthClientSecret};OAuth Client Id={OAuthClientId};OAuth Access Token={OAuthAccessToken};
                              OAuth Refresh Token={OAuthRefreshToken}; OAuth Token Endpoint={OAuthTokenEndpoint};Minor Version={MinorVersion}";

static void ExternalCache()
{
    Console.WriteLine("Start Time " + DateTime.Now);
    using (C1QuickBooksOnlineConnection con = new C1QuickBooksOnlineConnection(connectionString))
    {
        con.Open();
        C1QuickBooksOnlineCommand cmd = new C1QuickBooksOnlineCommand(conn, "Select * from Attachables'");
        var rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
           Console.WriteLine("Read and cached the row with Id " + rdr["Id"]);
                }
        Console.WriteLine("End Time " + DateTime.Now);
    }
}