With explicit caching (AutoCache = false), you decide exactly what data is cached and when to query the cache instead of the live data. Explicit caching gives you full control over the cache contents by using CACHE Statements. This section describes some strategies to use the caching features offered by the provider.

Creating the Cache

To load data in the cache, issue the following statement.

CACHE SELECT * FROM tableName WHERE ...

Once the statement is issued, any matching data in tableName is loaded into the corresponding table.

Updating the Cache

This section describes two ways to update the cache.

Updating with the SELECT Statement

The following example shows a statement that can update modified rows and add missing rows in the cached table. However, this statement does not delete extra rows that are already in the cache. This statement only merges the new rows or updates the existing rows.

C#

Sage50UKCommand cmd = new Sage50UKCommand("CACHE SELECT * FROM TradingAccounts WHERE TradingAccountUUID = 'c2ef66a5-a545-413b-9312-79a53caadbc4'", connection);
cmd.ExecuteNonQuery();
connection.Close();

VB.NET

Dim cmd As Sage50UKCommand = New Sage50UKCommand("CACHE SELECT * FROM TradingAccounts WHERE TradingAccountUUID = 'c2ef66a5-a545-413b-9312-79a53caadbc4'", connection)
cmd.ExecuteNonQuery()
connection.Close()

Updating with the TRUNCATE Statement

The following example shows a statement that can update modified rows and add missing rows in the cached table. This statement can also delete rows in the cache table that are not present in the live data source.

C#

Sage50UKCommand cmd = new Sage50UKCommand("CACHE WITH TRUNCATE SELECT * FROM TradingAccounts WHERE TradingAccountUUID = 'c2ef66a5-a545-413b-9312-79a53caadbc4'", connection);
cmd.ExecuteNonQuery();
connection.Close();

VB.NET

Dim Sage50UKCommand As cmd = New Sage50UKCommand("CACHE WITH TRUNCATE SELECT * FROM TradingAccounts WHERE TradingAccountUUID = 'c2ef66a5-a545-413b-9312-79a53caadbc4'", connection)
cmd.ExecuteNonQuery()
connection.Close()

Query the Data in Online or Offline Mode

This section describes how to query the data in online or offline mode.

Online: Select Cached Tables

You can use the tableName#CACHE syntax to explicitly execute queries to the cache while still online, as shown in the following example.

SELECT * FROM TradingAccounts#CACHE

Offline: Select Cached Tables

With Offline = true, SELECT statements always execute against the local cache database, regardless of whether you explicitly specify the cached table or not. Modification of the cache is disabled in Offline mode to prevent accidentally updating only the cached data. Executing a DELETE/UPDATE/INSERT statement while in Offline mode results in an exception.

The following example selects from the local cache but not the live data source because Offline = true.

C#

using (Sage50UKConnection connection = new Sage50UKConnection("URL=http://localhost:5493/sdata/accounts50/GCRM/{C4C863BE-B098-4A7D-A78B-D7A92B8ADB59};User=Manager;Password=xxxxxx;Offline=true;Cache Location=C:\\cache.db;") {
  String query = "SELECT * FROM TradingAccounts WHERE TradingAccountUUID='c2ef66a5-a545-413b-9312-79a53caadbc4' ORDER BY Name ASC";
  Sage50UKCommand cmd = new Sage50UKCommand(query, connection);
  cmd.ExecuteReader();
}

VB.NET

Using conn As New Sage50UKConnection("URL=http://localhost:5493/sdata/accounts50/GCRM/{C4C863BE-B098-4A7D-A78B-D7A92B8ADB59};User=Manager;Password=xxxxxx;Offline=true;Cache Location=C:\\cache.db;")
  Dim query As String = "SELECT * FROM TradingAccounts WHERE TradingAccountUUID='c2ef66a5-a545-413b-9312-79a53caadbc4' ORDER BY Name ASC"
  Dim cmd As Sage50UKCommand = New Sage50UKCommand(query, connection)
  cmd.ExecuteReader()
End Using

Delete Data from the Cache

You can delete data from the cache by building a direct connection to the database. Note that the provider does not support manually deleting data from the cache.

Common Use Case

A common use for caching is to have an application always query the cached data and only update the cache at set intervals, such as once every day or every two hours. There are two ways in which this can be implemented:

• AutoCache = false and Offline = false. All queries issued by the application explicitly reference the tableName#CACHE table. When the cache needs to be updated, the application executes a tableName#CACHE ... statement to bring the cached data up to date.
• Offline = true. Caching is transparent to the application. All queries are executed against the table as normal, so most application code does not need to be aware that caching is done. To update the cached data, simply create a separate connection with Offline = false and execute a tableName#CACHE ... statement.