CData ADO.NET - use an External Cache Provider

Because some data sources are slow to query against, the CData ADO.NET Provider supports a simple caching model that can improve the performance of data queries.

Caching Data

Working with cached data will increase the speed of data retrieval and your software will perform faster. But there is other benefits as well; enabling the CData caching mechanisms allows the connection to be used in an offline mode when access to the data source is not possible. It also enables advanced SQL queries that are otherwise not supported by the underlying data source itself.

Internal Cache Datafile

By default, a file system-based database store the cache data. This database file (internally CData Provider uses SQLite for this) will be located at the value for Location or to the explicitly defined path value for CacheLocation specified in the connection string. If neither of these connection properties are specified, the provider will use a platform-dependent default location.

External Cache Provider

It is also possible to store cache data in a separate external database of your choice. The database is a persistent store which can be shared by multiple connection objects over time. You can select a database and configure it using the configuration options of the cache provider. Officially tested external cache providers include SQL Server, MySQL, Oracle and Access.

The cache database can contain each table exposed by the Data Model of the CData ADO.NET Provider with column types mapped from the data source to the corresponding data type available in the chosen cache database. String columns can be mapped to different data types depending on their length. Tables are created automatically by the CData ADO.NET Provider.

To use this feature there are two properties needed to be specified in the connection string. Cache Provider defines what provider the CData ADO.NET Provider should use for connecting to the external cache database and the Cache Connection defines the connection string to use for the cache database connection.

Examples

These examples shows the connection string additions to specify for usage of external cache in each of the officially tested database systems available.

//MySQL
Cache Provider=MySql.Data.MySqlClient;Cache Connection='Server=localhost;Port=3306;Database=cacheDb;Uid=root;Pwd=123456;'

//SQL Server
Cache Provider=System.Data.SqlClient;Cache Connection='Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;'

//Access
Cache Provider=Microsoft.ACE.OLEDB.12.0;Cache Connection='Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\\temp\\test.accdb'

//Oracle
Cache Provider=Oracle.DataAccess.Client;Cache Connection='Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));User Id=SYSTEM;Password=Nbanana1;'

Other options for the Cache Connection is available and based on the destination systems connection strings options. See respective cache providers connection string page for details on these options.