Class MsSqlServerStorage

IStorage implementation utilizing the MS SQL server database engine.

Inheritance
System.Object
MsSqlServerStorage
Namespace: Telerik.Reporting.Cache
Assembly: Telerik.Reporting.dll

Syntax

public class MsSqlServerStorage : IStorage2, IStorage
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

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

Constructors

MsSqlServerStorage(String)

Initializes a new instance of the MsSqlServerStorage class.

Declaration
public MsSqlServerStorage(string connectionString)
Parameters
System.String connectionString

The connection string to the backend storage. Connection Strings in ADO.NET

MsSqlServerStorage(String, Int32)

Initializes a new instance of the MsSqlServerStorage class.

Declaration
public MsSqlServerStorage(string connectionString, int commandTimeout)
Parameters
System.String connectionString

The connection string to the backend storage. Connection Strings in ADO.NET
System.Int32 commandTimeout

Determines the CommandTimeout that will be used when executing database commands, in seconds. The default value is 30. SqlCommand.CommandTimeout

Methods

AcquireLock(String)

Acquires a lock on a named resource.

Declaration
public IDisposable AcquireLock(string key)
Parameters
System.String key

Returns
System.IDisposable

Implements
See Also
AcquireLock(System.String)

AddInSet(String, String)

Adds a single string value to a set of values denoted from the given key.

Declaration
public void AddInSet(string key, string value)
Parameters
System.String key

System.String value

Implements
See Also
AddInSet(System.String, System.String)

ClearAllData()

Utility method. Clears all data from the storage data tables.

Declaration
public void ClearAllData()

CreateSchema()

Utility method. Creates the data schema (tables and stored procedures) needed from the storage.

Declaration
public void CreateSchema()

Delete(String)

Deletes a key with its value (string or byte array) from the storage.

Declaration
public void Delete(string key)
Parameters
System.String key

Implements
See Also
Delete(System.String)

DeleteInSet(String, String)

Deletes a single string value from a set of values denoted from the given key.

Declaration
public bool DeleteInSet(string key, string value)
Parameters
System.String key

System.String value

Returns
System.Boolean

Implements
See Also
DeleteInSet(System.String, System.String)

DeleteMasterKey(String)

Declaration
public void DeleteMasterKey(string key)
Parameters
System.String key

Implements

DeleteSet(String)

Deletes a set of values denoted by the given key.

Declaration
public void DeleteSet(string key)
Parameters
System.String key

Implements
See Also
DeleteInSet(System.String, System.String)

Exists(String)

Retrieves a value indicating if a single value (string or byte array) exists in the storage.

Declaration
public bool Exists(string key)
Parameters
System.String key

Returns
System.Boolean

Implements
See Also
Exists(System.String)

ExistsInSet(String, String)

Retrieves a value indicating if a set of values exists in the storage.

Declaration
public bool ExistsInSet(string key, string value)
Parameters
System.String key

System.String value

Returns
System.Boolean

Implements
See Also
ExistsInSet(System.String, System.String)

GetAllMembersInSet(String)

Retrieves all members in a set of string values.

Declaration
public IEnumerable<string> GetAllMembersInSet(string key)
Parameters
System.String key

Returns
System.Collections.Generic.IEnumerable<System.String>

Implements
See Also
GetAllMembersInSet(System.String)

GetBytes(String)

Retrieves a byte array value stored under particular key.

Declaration
public byte[] GetBytes(string key)
Parameters
System.String key

Returns
System.Byte[]

Implements
See Also
GetBytes(System.String)

GetCountInSet(String)

Retrieves the count of the values in a set value stored in the storage.

Declaration
public long GetCountInSet(string key)
Parameters
System.String key

Returns
System.Int64

Implements
See Also
GetCountInSet(System.String)

GetString(String)

Retrieves a string value stored under particular key.

Declaration
public string GetString(string key)
Parameters
System.String key

Returns
System.String

Implements
See Also
GetString(System.String)

SetBytes(String, Byte[])

Stores a byte array value under particular key.

Declaration
public void SetBytes(string key, byte[] value)
Parameters
System.String key

System.Byte[] value

Implements
See Also
SetBytes(System.String, System.Byte[])

SetString(String, String)

Stores a string value under particular key.

Declaration
public void SetString(string key, string value)
Parameters
System.String key

System.String value

Implements
See Also
SetString(System.String, System.String)