Progress® Telerik® Reporting R2 2019

MsSqlServerStorage Class

IStorage implementation utilizing the MS SQL server database engine.

Inheritance Hierarchy

SystemObject
  Telerik.Reporting.CacheMsSqlServerStorage

Namespace:  Telerik.Reporting.Cache
Assembly:  Telerik.Reporting (in Telerik.Reporting.dll)

Syntax

C#

VB

Copy

public class MsSqlServerStorage : IStorage

Public Class MsSqlServerStorage
	Implements IStorage

The MsSqlServerStorage type exposes the following members.

Constructors

	Name	Description
	MsSqlServerStorage	Creates and initializes an instance of MsSqlServerStorage.

Top

Methods

	Name	Description
	AcquireLock	Acquires a lock on a named resource.
	AddInSet	Adds a single string value to a set of values denoted from the given key.
	ClearAllData	Utility method. Clears all data from the storage data tables.
	CreateSchema	Utility method. Creates the data schema (tables and stored procedures) needed from the storage.
	Delete	Deletes a key with its value (string or byte array) from the storage.
	DeleteInSet	Deletes a single string value from a set of values denoted from the given key.
	DeleteSet	Deletes a set of values denoted by the given key.
	Exists	Retrieves a value indicating if a single value (string or byte array) exists in the storage.
	ExistsInSet	Retrieves a value indicating if a set of values exists in the storage.
	GetAllMembersInSet	Retrieves all members in a set of string values.
	GetBytes	Retrieves a byte array value stored under particular key.
	GetCountInSet	Retrieves the count of the values in a set value stored in the storage.
	GetString	Retrieves a string value stored under particular key.
	SetBytes	Stores a byte array value under particular key.
	SetString	Stores a string value under particular key.

Top

Remarks

MsSqlServerStorage connects to SQL server using the .NET Framework Data Provider for SQL Server (System.Data.SqlClient Namespace).

The storage needs a host database with specific tables and stored procedures which may be created in dedicated or shared database. The necessary SQL objects may be created using the DatabaseCacheConfigurator tool (located under the Telerik Reporting installation folder / Tools) or manually executing the follow DDL script. It adds all needed objects to an existing MS SQL database (execute in the context of existing database):

Copy

--- Tables creation ---
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tr_AppLock](
        [Id] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_tr_AppLock] PRIMARY KEY CLUSTERED 
(
        [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

CREATE TABLE [dbo].[tr_Object](
        [Id] [varchar](255) NOT NULL,
        [Value] [image] NOT NULL,
 CONSTRAINT [pk_tr_Object] PRIMARY KEY CLUSTERED 
(
        [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

CREATE TABLE [dbo].[tr_Set](
        [Id] [varchar](255) NOT NULL,
        [Member] [varchar](255) NOT NULL,
 CONSTRAINT [pk_st_DE59633C] PRIMARY KEY CLUSTERED 
(
        [Id] ASC,
        [Member] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

CREATE TABLE [dbo].[tr_String](
        [Id] [varchar](255) NOT NULL,
        [Value] [nvarchar](4000) NOT NULL,
 CONSTRAINT [pk_strng_9912B79F] PRIMARY KEY CLUSTERED 
(
        [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

SET ANSI_PADDING OFF
GO

--- Stored Procedures creation ---

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_tr_AcquireLock] 
        @Key varchar(255) = 0
AS
BEGIN
        SET NOCOUNT ON;

        INSERT INTO [dbo].[tr_AppLock] 
        VALUES (@Key)
END
GO

CREATE PROCEDURE [dbo].[sp_tr_AddInSet] 
        @Key varchar(255) = 0, 
        @Value varchar(255) = 0
AS
BEGIN
        SET NOCOUNT ON;

        MERGE [dbo].[tr_Set] AS T 
        USING (SELECT @Key as [Id], @Value as [Member]) AS S 
                ON (T.Id = S.Id AND T.Member = S.Member) 
        WHEN NOT MATCHED BY TARGET THEN 
                INSERT(Id, Member) VALUES(S.Id, S.Member);
END
GO

CREATE PROCEDURE [dbo].[sp_tr_ClearAll] 
AS
BEGIN
        DELETE [dbo].[tr_Set]
        DELETE [dbo].[tr_Object]
        DELETE [dbo].[tr_String]
END
GO

CREATE PROCEDURE [dbo].[sp_tr_Delete] 
        @Key varchar(255) = 0
AS
BEGIN
        SET NOCOUNT ON;

        DELETE 
        FROM [dbo].[tr_String] 
        WHERE [Id] = @Key 

        DELETE 
        FROM [dbo].[tr_Object] 
        WHERE [Id] = @Key
END
GO

CREATE PROCEDURE [dbo].[sp_tr_DeleteInSet] 
        @Key varchar(255) = 0, 
        @Value varchar(255) = 0
AS
BEGIN
        SET NOCOUNT ON;

        DELETE FROM [dbo].[tr_Set] WHERE [Id] = @Key AND [Member] = @Value

        RETURN @@rowcount
END
GO

CREATE PROCEDURE [dbo].[sp_tr_Exists] 
        @Key varchar(255) = 0
AS
BEGIN
        SET NOCOUNT ON;

        IF EXISTS (SELECT 1 FROM [dbo].[tr_String] WHERE [Id] = @Key)
                RETURN 1
        ELSE 
                IF EXISTS (SELECT 1 FROM [dbo].[tr_Object] WHERE [Id] = @Key)
                        RETURN 1
                ELSE
                        RETURN 0
END
GO

CREATE PROCEDURE [dbo].[sp_tr_ExistsInSet] 
        @Key varchar(255) = 0, 
        @Value varchar(255) = 0
AS
BEGIN
        SET NOCOUNT ON;

        IF EXISTS (SELECT 1 FROM [dbo].[tr_Set] WHERE [Id] = @Key AND [Member] = @Value)
                RETURN 1
        ELSE
                RETURN 0
END
GO

CREATE PROCEDURE [dbo].[sp_tr_GetBytes] 
        @Key varchar(255) = 0
AS
BEGIN
        SET NOCOUNT ON;

        SELECT Value 
        FROM [dbo].[tr_Object] 
        WHERE Id = @Key
END
GO

CREATE PROCEDURE [dbo].[sp_tr_GetCountInSet] 
        @Key varchar(255) = 0
AS
BEGIN
        SET NOCOUNT ON;

        SELECT COUNT(1) 
        FROM [dbo].[tr_Set] 
        WHERE [Id] = @Key
END
GO

CREATE PROCEDURE [dbo].[sp_tr_GetMembersInSet] 
        @Key varchar(255) = 0
AS
BEGIN
        SET NOCOUNT ON;

SELECT [Member] 
FROM [dbo].[tr_Set] 
WHERE [Id] = @Key
END
GO

CREATE PROCEDURE [dbo].[sp_tr_GetString] 
        @Key varchar(255) = 0
AS
BEGIN
        SET NOCOUNT ON;

        SELECT Value 
        FROM [dbo].[tr_String] 
        WHERE Id = @Key
END
GO

CREATE PROCEDURE [dbo].[sp_tr_SetObject] 
        @Key varchar(255) = 0, 
        @Value image
AS
BEGIN
        SET NOCOUNT ON;

        MERGE [dbo].[tr_Object] AS T 
        USING (SELECT @Key as Id, @Value as Value) AS S 
                ON (T.Id = S.Id) 
        WHEN NOT MATCHED BY TARGET THEN 
                INSERT(Id, Value) VALUES(S.Id, S.Value) 
        WHEN MATCHED THEN 
                UPDATE SET T.Value = S.Value;
END
GO

CREATE PROCEDURE [dbo].[sp_tr_SetString] 
        @Key varchar(255) = 0, 
        @Value nvarchar(4000) = 0
AS
BEGIN
        SET NOCOUNT ON;

        MERGE [dbo].[tr_String] AS T 
        USING (SELECT @Key as Id, @Value as Value) AS S 
                ON (T.Id = S.Id) 
        WHEN NOT MATCHED BY TARGET THEN 
                INSERT(Id, Value) VALUES(S.Id, S.Value) 
        WHEN MATCHED THEN 
                UPDATE SET T.Value = S.Value;
END
GO

Version Information

Supported in: 1.0.1

See Also

Reference

Telerik.Reporting.Cache Namespace