Progress® Telerik® Reporting R2 2017

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 : IStorage

The MsSqlServerStorage type exposes the following members.

Constructors

  NameDescription
Public methodMsSqlServerStorage
Creates and initializes an instance of MsSqlServerStorage.
Top

Methods

  NameDescription
Public methodAcquireLock
Acquires a lock on a named resource.
Public methodAddInSet
Adds a single string value to a set of values denoted from the given key.
Public methodClearAllData
Utility method. Clears all data from the storage data tables.
Public methodCreateSchema
Utility method. Creates the data schema (tables and stored procedures) needed from the storage.
Public methodDelete
Deletes a key with its value (string or byte array) from the storage.
Public methodDeleteInSet
Deletes a single string value from a set of values denoted from the given key.
Public methodDeleteSet
Deletes a set of values denoted by the given key.
Public methodExists
Retrieves a value indicating if a single value (string or byte array) exists in the storage.
Public methodExistsInSet
Retrieves a value indicating if a set of values exists in the storage.
Public methodGetAllMembersInSet
Retrieves all members in a set of string values.
Public methodGetBytes
Retrieves a byte array value stored under particular key.
Public methodGetCountInSet
Retrieves the count of the values in a set value stored in the storage.
Public methodGetString
Retrieves a string value stored under particular key.
Public methodSetBytes
Stores a byte array value under particular key.
Public methodSetString
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):

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

See Also