Progress® Telerik® Reporting R1 2020

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

public class MsSqlServerStorage : IStorage2, 
	IStorage

Public Class MsSqlServerStorage
	Implements IStorage2, IStorage

The MsSqlServerStorage type exposes the following members.

	Name	Description
	MsSqlServerStorage	Creates and initializes an instance of MsSqlServerStorage.

	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.
	DeleteMasterKey
	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.

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

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='tr_AppLock' and xtype='U')
        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

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='tr_Object' and xtype='U')
        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

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='tr_Set' and xtype='U')
        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

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='tr_String' and xtype='U')
        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

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'sp_tr_AcquireLock'
            AND type = 'P'
      )
     DROP PROCEDURE [dbo].[sp_tr_AcquireLock]
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

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'sp_tr_AddInSet'
            AND type = 'P'
      )
     DROP PROCEDURE [dbo].[sp_tr_AddInSet]
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

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'sp_tr_ClearAll'
            AND type = 'P'
      )
     DROP PROCEDURE [dbo].[sp_tr_ClearAll]
GO

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

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'sp_tr_Delete'
            AND type = 'P'
      )
     DROP PROCEDURE [dbo].[sp_tr_Delete]
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

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'sp_tr_DeleteInSet'
            AND type = 'P'
      )
     DROP PROCEDURE [dbo].[sp_tr_DeleteInSet]
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

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'sp_tr_Exists'
            AND type = 'P'
      )
     DROP PROCEDURE [dbo].[sp_tr_Exists]
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

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'sp_tr_ExistsInSet'
            AND type = 'P'
      )
     DROP PROCEDURE [dbo].[sp_tr_ExistsInSet]
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

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'sp_tr_GetBytes'
            AND type = 'P'
      )
     DROP PROCEDURE [dbo].[sp_tr_GetBytes]
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

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'sp_tr_GetCountInSet'
            AND type = 'P'
      )
     DROP PROCEDURE [dbo].[sp_tr_GetCountInSet]
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

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'sp_tr_GetMembersInSet'
            AND type = 'P'
      )
     DROP PROCEDURE [dbo].[sp_tr_GetMembersInSet]
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

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'sp_tr_GetString'
            AND type = 'P'
      )
     DROP PROCEDURE [dbo].[sp_tr_GetString]
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

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'sp_tr_SetObject'
            AND type = 'P'
      )
     DROP PROCEDURE [dbo].[sp_tr_SetObject]
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

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'sp_tr_SetString'
            AND type = 'P'
      )
     DROP PROCEDURE [dbo].[sp_tr_SetString]
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

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'sp_tr_DeleteLike'
            AND type = 'P'
      )
     DROP PROCEDURE [dbo].[sp_tr_DeleteLike]
GO

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

        DELETE 
        FROM [dbo].[tr_String] 
        WHERE [Id] LIKE @Key + '%'

        DELETE 
        FROM [dbo].[tr_Object] 
        WHERE [Id] LIKE @Key + '%'

        DELETE 
        FROM [dbo].[tr_Set] 
        WHERE [Id] LIKE @Key + '%'
END
GO

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'sp_tr_DeleteSet'
            AND type = 'P'
      )
     DROP PROCEDURE [dbo].[sp_tr_DeleteSet]
GO

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

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

Version Information

Supported in: 1.0.1

MsSqlServerStorage Class

Inheritance Hierarchy

Syntax

Constructors

Methods

Remarks

Version Information

See Also

Reference

Was this article helpful?

Give article feedback

Getting Started

Support Resources

Community