DataMatch Enterprise Server: SQL Using Cached Data for Excel
You can enable an “offline” mode or explicitly query the cache with the tableName#CACHE syntax.
Offline: Select Cached Tables
With offline mode enabled, SELECT statements will always execute against the local cache database, regardless of whether you explicitly specify the cached table or not. (You can specify the cached table with the tableName#CACHE syntax.)
Modification of the cache is disabled in offline mode to prevent accidentally updating only the cached data. Executing a DELETE/UPDATE/INSERT statement while Offline is set will result in an exception.
Queries in offline mode do not have the same syntax limitations as queries run directly against the data source. In offline mode, any SQL query can be executed, including complex JOIN statements between multiple tables in the same database.
Examples
The command below will select from the local cache but not the live data source because Offline is set to true.
C#
using (ExcelConnection connection = new ExcelConnection("Excel File=C:\MyExcelWorkbooks\SampleWorkbook.xlsx;Offline=true;Cache Location=C:\\cache.db;") { String query = "SELECT * FROM Sheet WHERE FirstName='Bob' ORDER BY LastName ASC"; ExcelCommand cmd = new ExcelCommand(query, connection); cmd.ExecuteReader();} |
VB.NET
Using conn As New ExcelConnection("Excel File=C:\MyExcelWorkbooks\SampleWorkbook.xlsx;Offline=true;Cache Location=C:\\cache.db;") Dim query As String = "SELECT * FROM Sheet WHERE FirstName='Bob' ORDER BY LastName ASC" Dim cmd As ExcelCommand = New ExcelCommand(query, connection) cmd.ExecuteReader()End Using |
Online: Select Cached Tables
You can use the tableName#CACHE syntax to explicitly execute queries to the cache while still online:
SELECT * FROM Sheet#CACHE |