Skip to content

Caching with SQL Server

Peter Chapman edited this page May 16, 2022 · 8 revisions

The GoTo.Bible application supports caching of provider API calls using Microsoft SQL Server, MySQL, or In-Memory Caching.

Table Structure

CREATE TABLE [dbo].[Cache](
	[Id] [nvarchar](449) NOT NULL,
	[Value] [varbinary](max) NOT NULL,
	[ExpiresAtTime] [datetimeoffset](7) NOT NULL,
	[SlidingExpirationInSeconds] [bigint] NULL,
	[AbsoluteExpiration] [datetimeoffset](7) NULL,
	PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

User Secret Configuration

The following configuration can be using with SQL LocalDB, assuming you have already created a database called GoToBible:

dotnet user-secrets set "Providers:Cache:DatabaseProvider" "mssql"
dotnet user-secrets set "Providers:Cache:ConnectionString" "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=GoToBible;Integrated Security=True;"
dotnet user-secrets set "Providers:Cache:SchemaName" "dbo"
dotnet user-secrets set "Providers:Cache:TableName" "Cache"

Windows Application Settings Configuration

If caching is being configured by the GoTo.Bible Windows app, the connection string is stored in the application configuration file (%LOCALAPPDATA%\Conglomo\GoToBible.Windows_StrongName_iyzagyb45p0rit0gsgf0v3s4zgb3fcuo\1.0.0.0\user.config), with the table name GoToBible:

Table Structure

CREATE TABLE [dbo].[GoToBible](
	[Id] [nvarchar](449) NOT NULL,
	[Value] [varbinary](max) NOT NULL,
	[ExpiresAtTime] [datetimeoffset](7) NOT NULL,
	[SlidingExpirationInSeconds] [bigint] NULL,
	[AbsoluteExpiration] [datetimeoffset](7) NULL,
	PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO