キャッシング

Caching data offers several advantages that can improve overall process performance, including reduced API requests and faster data access.

The DataConnector features an easy-to-use caching procedure, which can also be shared by multiple connections. This article is going to demonstrate the two types of caches that are currently supported:

Default cache: An internally managed database (in SQLite).
Specified cache: An externally managed database (for now in SQL Server). For details, see External Caching.

To enable the caching feature, necessary connection properties are available, e.g.:

CacheLocation: The location of the cache in the underlying file system (default is %AppData%/C1DataConnector).
CacheTolerance: Cache expiration interval time (in seconds). If this interval has expired, the cached data will be updated with new items from the data source (default is 600 seconds).

Internal caching

The following code demonstrates how to enable caching for a table by setting the UseCache property to True (by default, it is set to False). The cached data is stored in the file specified by the CacheLocation property in the connection string. For more information on Incremental Caching, refer to this topic.

C#	コードのコピー
static void LocalCache() { //接続文字列を定義します。 string connectionString = $@"Username = *****; Password = ***; Oauth Client Id = ***; Oauth Client Secret = ***; Oauth Token Endpoint = http:////; Url= http:////*; Use Cache = true; Cache Tolerance = 500; Cache Location = 'C:\temp\c1cache.db';"; using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString)) { conn.Open(); var cmd = conn.CreateCommand(); cmd.CommandText = "SELECT from incident"; var rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine(String.Format("\t{0} --> \t\t{1}-->\t\t\t{2}", rdr["caller_id"], rdr["category"], rdr["description"])); } Console.WriteLine("End Time " + DateTime.Now); } }

コードのコピー

static void LocalCache()
{
    //接続文字列を定義します。
    string connectionString = $@"Username = *******; Password = *******; Oauth Client Id = *******;
            Oauth Client Secret = *******; Oauth Token Endpoint = http://****/****/****; Url= http://****/****/****; 
            Use Cache = true; Cache Tolerance = 500; Cache Location = 'C:\temp\c1cache.db';";

    using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
    {
        conn.Open();
        var cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT * from incident";
        var rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            Console.WriteLine(String.Format("\t{0} --> \t\t{1}-->\t\t\t{2}", rdr["caller_id"], rdr["category"], rdr["description"]));
        }
        Console.WriteLine("End Time " + DateTime.Now);
    }
}

External Caching

The ADO.NET provider for ServiceNow support external caching, allowing users to store cached data in a separate database. This external database serves as a constant repository and can be accessed by multiple connection objects simultaneously. It includes all tables exposed by the ServiceNow provider and offers configuration options specific to the cache provider.

To utilize external caching, the provider supports a specified cache, such as SQL Server. You can specify the connection string using the UseCache, CacheProvider, and CacheConnection keys to enable external caching.

Note: With SQL Server, Cache Provider = ‘Microsoft.Data.SqlClient’ is mandatory so you must create your own database as our cache doesn’t create new Database.

The following code example implements external caching, by setting Use Cache to True, and using 'Microsoft.Data.SqlClient' as the cache provider.

C#	コードのコピー
static void ExternalCache() { //明示的な MS SQL キャッシュ構成。 string connectionString = $@"Username= *****; Password= ***; Oauth Client Id= ***; Oauth Client Secret= ***; Oauth Token Endpoint= http:////"; Url = http:////*; Use Cache = true; Cache provider = 'Microsoft.Data.SqlClient'; Cache connection = 'Server= yourserverid; Database= databasename; User Id= yourId; Password= yourpassword;'"; static void DataReaderSelect() { Console.WriteLine("Start Time " + DateTime.Now); using (C1ServiceNowConnection con = new C1ServiceNowConnection(connectionString)) { con.Open(); var cmd = con.CreateCommand(); cmd.CommandText = "SELECT from incident"; var rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine(String.Format("\t{0} --> \t\t{1}-->\t\t\t{2}", rdr["caller_id"], rdr["category"], rdr["description"])); } Console.WriteLine("End Time " + DateTime.Now); } } }

コードのコピー

static void ExternalCache()
{
    //明示的な MS SQL キャッシュ構成。
    string connectionString = $@"Username= *******; Password= *******; Oauth Client Id= *******;
    Oauth Client Secret= *******; Oauth Token Endpoint= http://****/****/****"; Url = http://****/****/****; Use Cache = true; 
    Cache provider = 'Microsoft.Data.SqlClient'; Cache connection = 'Server= yourserverid; Database= databasename; User Id= yourId; Password= yourpassword;'";

    static void DataReaderSelect()
    {
        Console.WriteLine("Start Time " + DateTime.Now);
        using (C1ServiceNowConnection con = new C1ServiceNowConnection(connectionString))
        {
            con.Open();
            var cmd = con.CreateCommand();
            cmd.CommandText = "SELECT * from incident";
            var rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                Console.WriteLine(String.Format("\t{0} --> \t\t{1}-->\t\t\t{2}", rdr["caller_id"], rdr["category"], rdr["description"]));
            }
            Console.WriteLine("End Time " + DateTime.Now);
        }
    }
}