Use a .NET Data Connector to Locally Cache Data from Online or CRM Data Sources
When working with web services, it is prudent to cache data locally to improve performance. This results in two advantages: network roundtrip is saved when querying the same recordset, and second is if the web service employs some kind of throttling on the number of requests and size of data, then denial of service is avoided. This blog will demonstrate what caching mechanism could get used when getting data from online data sources such as Salesforce CRM using ComponentOne DataConnectors.
ComponentOne DataConnectors are part of DataServices edition. This edition also includes various services such as:
- DataEngine: Can be used for the fast in-memory analysis of large data
- CollectionView: Can be used to provide filtering, grouping sorting, and data virtualization capabilities to data-aware controls,
- CalcEngine: An Excel-like calculation library
- TextParser: A text parsing library to extract data from text, HTML, CSV, PDF and Doc files.
- Web API's for:
- Report generation and export that also integrates with MVC/JavaScript FlexViewer
- PDF generation, which also integrates with MVC/JavaScript FlexViewer
- Cloud Storage perform CRUD operation over Azure, AWS, DropBox, OneDrive & GoogleDrive
- Website visitor tracking
- Excel API's to generate excel from data or based on the template, merge & split Excel files
- BarCode API to generate barcode on the fly
Default Cache
DataConnectors have an in-built default caching that uses SQLite. To enable it, we can use a connection string with "Use Cache" and "Cache Location" (optional) keys.
const string GCSalesforceServerConnectionString = @"Username=**********;Password=***********;Security Token=************;
OAuth Client Id=***********;
OAuth Client Secret=***************;
OAuth Token Endpoint=https://ap16.salesforce.com/services/oauth2/token;
Url=https://ap16.salesforce.com/services/data/v45.0";
// Configure connection string with cache settings
static String connectionString = $@"{GCSalesforceServerConnectionString}; Use Cache = true; Cache Tolerance = 600; Cache Location = 'C:\Windows\Temp\c1cache.db'";
C1SalesforceConnectionStringBuilder builder = new C1SalesforceConnectionStringBuilder();
builder.ConnectionString = connectionString;
SQL Server Cache
Since the 2021 v2 release of DataServices, DataConnectors support caching data to SQL Server. This caching technique additionally uses the Cache Provider and Cache Connection settings to specify the cache settings for SQL Server. The cache provider used is "Microsoft.Data.SqlClient", cache connection can be the connection string to the SQL Server database that is supposed to store cached data. Note that it is a prerequisite to creating the cache database in SQL Server. The connection object cannot create this database. In this example, SalesforceCache database was created beforehand.
string sfconstring = @"username=abc@email.com;password=xxxxx;security token=xxxxxx;" +
"oauth client id=xxxxxxxx; " +
"oauth client secret = xxxxx; " +
"oauth token endpoint = https://login.salesforce.com/services/oauth2/token;url=https://ap16.salesforce.com/services/data/v42.0; " +
"use pool=True;max page size = 200; api version = 42.0; ; Use Cache = 'true';cache tolerance=60000; cache provider = 'Microsoft.Data.SqlClient'; " +
"cache connection = 'Server=.\\SQLEXPRESS;Initial Catalog=SalesforceCache;Persist Security Info=false; " +
"Integrated Security=true;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30'";
C1.AdoNet.Salesforce.C1SalesforceConnection con = new C1.AdoNet.Salesforce.C1SalesforceConnection();
con.ConnectionString = sfconstring;
C1.AdoNet.Salesforce.C1SalesforceDataAdapter da = new C1.AdoNet.Salesforce.C1SalesforceDataAdapter(con, "select * from Account");
DataTable dt = new DataTable();
da.Fill(dt);
Incremental Cache
In applications dealing with a large amount of data, historical data may seldom change. On the other hand, new data may get added, or recent data could be updated. But the application may still need all this data for comprehensive analysis. Such an application can benefit by using the Incremental Cache feature of the data connector. Any table that needs incremental updates to cache must have a timestamp column that notes when the record was updated\added. This column helps the data connector determine if the record needs to be updated to the cache database. Fortunately, most internet-based data sources support this kind of column in the tables. To enable this feature on data connectors, set the IncrementalUpdate to true and the name of the timestamp column to IncrementalUpdateColumn. For a list of timestamp column names, please refer to the documentation.
Caching in FlexReport Designer Application
FlexReport designer has an inbuilt feature to connect to various cloud data sources using data connectors. It too supports caching with the data connectors to enhance performance. The cache settings can be configured using the property grid that opens when clicking on the ellipses button next to the connection string textbox on the DataSources window. However, it does need that cache database folder already exist, meaning, if we set the path of Cache Location to "C:\MyAppCache\FRtest.db", then there should be a folder named MyAppCache already present so that cache database could be created by data connector if using SQLite as cache database.
Caching when Using Visual Studio Server Explorer
DatConnectors could be used in Visual Studio Server Explorer to connect to cloud data sources without writing a single line of code. Using Server Explorer features, queries could be built either using SQL or query builder. However, setting caching through connection dialog is not supported. The cache settings can be done in applications using code or configuration.
Tips & Tricks
When working with a cache database, you may require to troubleshoot or remove cache db for data corruption or any reason. You should also delete the CacheMetaData file below location before creating a new cache database.
C:\Users\(UserName)\AppData\Roaming\C1DataConnector
Prabhakar Mishra