MsSqlServerStorage Class
Inheritance Hierarchy
Telerik.Reporting.CacheMsSqlServerStorage
Assembly: Telerik.Reporting (in Telerik.Reporting.dll)
Syntax
The MsSqlServerStorage type exposes the following members.
Constructors
Name | Description | |
---|---|---|
![]() | MsSqlServerStorage |
Initializes a new instance of the MsSqlServerStorage class.
|
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.
|
![]() | 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).
--- 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