Faster Reporting using the QuickBooks Provider

The CData ADO.NET Provider for QuickBooks exposes caching features that can be used to avoid the delay of executing commands over the network, to create applications with offline functionality, to use a particular SQL dialect, and more. Caching can be transparent to the application. This article shows how to enable the Auto Cache feature to meet one of the common use cases for caching: to provide fast reporting from the cache, even when working with large data sets.

Creating the Cache

You can reduce network load times by querying a local cache. The Auto Cache feature enables you execute expensive queries against a local cache, while also ensuring that reporting is from the current data. When Auto Cache is enabled, all queries are executed against the data source and, additionally, SELECT queries will create and update the cache. You can explicitly query the cache with the syntax below:


To enable the Auto Cache feature, set the Auto Cache connection string property to True and open a connection. To create the cache and store a table, execute a SELECT query. The code example below shows how to connect and create the cache.

string connectionString = "URL=http://localhost:2080;User=test;Password=test;";
"Auto Cache=true;";
using(QuickBooksConnection conn = new QuickBooksConnection(connectionString)){
  QuickBooksDataAdapter dataAdapter = new QuickBooksDataAdapter("Select * From Customers", conn);
  DataTable table = new DataTable();

Implementation of a Caching Strategy

You can download the sample application to see how to create a complete application using the code examples in this article. The included sample project integrates the basic components of an effective caching solution with a simple application that displays the current QuickBooks data.