skip navigation
  • Product Bundles

    DevCraft

    All Telerik .NET tools and Kendo UI JavaScript components in one package. Now enhanced with:

    • NEW: Design Kits for Figma
    • Online Training
    • Document Processing Library
    • Embedded Reporting for web and desktop
    Web
    Kendo UI UI for jQuery UI for Angular UI for React UI for Vue UI for Blazor UI for ASP.NET Core UI for ASP.NET MVC UI for ASP.NET AJAX UI for Silverlight UI for PHP UI for JSP
    Mobile
    UI for .NET MAUI UI for Xamarin
    Document Management
    Telerik Document Processing
    Desktop
    UI for .NET MAUI UI for WinUI UI for WinForms UI for WPF UI for UWP
    Reporting & Mocking
    Telerik Reporting Telerik Report Server Telerik JustMock
    Automated Testing
    Test Studio Test Studio Dev Edition
    CMS
    Sitefinity
    UI/UX Tools
    ThemeBuilder Design System Kit
    Debugging
    Fiddler Fiddler Everywhere Fiddler Classic Fiddler Jam FiddlerCap FiddlerCore
    Extended Reality
    UI for Unity XR
    Free Tools
    JustAssembly JustDecompile VB.NET to C# Converter Testing Framework
    View all products
  • Overview
  • Demos
    • What's New
    • Roadmap
    • Release History
  • Support and Learning

    • Support and Learning Hub
    • First Steps
    • Docs
    • Demos
    • Virtual Classroom
    • Use Reports in Applications
    • System Requirements
    • Forums
    • Videos
    • Blogs
    • Submit a Ticket
  • Pricing
  • Search
  • Shopping cart
    • Account Overview
    • Your Licenses
    • Downloads
    • Support Center
    • Forum Profile
    • Payment Methods
    • Edit Profile
    • Log out
  • Login
  • Contact Us
  • Try now
Search all

Class MsSqlServerStorage

IStorage implementation utilizing the MS SQL server database engine.

Inheritance
System.Object
MsSqlServerStorage
Inherited Members
System.Object.ToString()
System.Object.Equals(System.Object)
System.Object.Equals(System.Object, System.Object)
System.Object.ReferenceEquals(System.Object, System.Object)
System.Object.GetHashCode()
System.Object.GetType()
System.Object.MemberwiseClone()
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
IStorage.AcquireLock(String)
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
IStorage.AddInSet(String, String)
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
IStorage.Delete(String)
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
IStorage.DeleteInSet(String, String)
See Also
DeleteInSet(System.String, System.String)

DeleteMasterKey(String)

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

Implements
IStorage2.DeleteMasterKey(String)

DeleteSet(String)

Deletes a set of values denoted by the given key.

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

Implements
IStorage.DeleteSet(String)
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
IStorage.Exists(String)
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
IStorage.ExistsInSet(String, String)
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
IStorage.GetAllMembersInSet(String)
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
IStorage.GetBytes(String)
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
IStorage.GetCountInSet(String)
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
IStorage.GetString(String)
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
IStorage.SetBytes(String, Byte[])
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
IStorage.SetString(String, String)
See Also
SetString(System.String, System.String)

Was this article helpful?

Tell us how we can improve this article

Skip
Getting Started
  • Install Now
  • Online Demos
Support Resources
  • Documentation
  • Knowledge Base
  • Videos
  • Reporting Samples Repository
  • Reporting Release History
Community
  • Forums
  • Blogs
  • Reporting Feedback Portal

Copyright © 2018 Progress Software Corporation and/or its subsidiaries or affiliates.
All Rights Reserved.

Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.