Table – Dictionary

Purpose

Used to maintain a list of words, or phrases, used by String Dictionary fields rendered by the Dynamic Page Builder.

Definition

CREATE TABLE [dbo].[Dictionary](
	[DictionaryGUID] [UNIQUEIDENTIFIER] NOT NULL,
	[Word] [VARCHAR](500) NOT NULL,
 CONSTRAINT [PK_Dictionary] PRIMARY KEY CLUSTERED 
(
	[DictionaryGUID] ASC,
	[Word] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Column Definitions

DictionaryGUID is the InstanceGUID of the Dictionary Instance record.

Word is the word, or phrase, contained in the Dictionary.

Key Functionality

The contents of the Dictionary can be maintained directly by the Dictionary Field type. The full list of Words in the dictionary are returned by the GI_ListDictionary TSQL Stored Procedure. Words can be added to/removed from the Dictionary using the GI_AddWordToDictionary / GI_RemoveWordFromDictionary TSQL Stored Procedures respectively.

CREATE PROCEDURE [dbo].[GI_ListDictionary] 
(
	@SessionGUID AS UNIQUEIDENTIFIER,
	@UserGUID AS UNIQUEIDENTIFIER,
	@CurrentInstanceGUID AS UNIQUEIDENTIFIER,
	@TargetInstanceGUID AS UNIQUEIDENTIFIER
)
AS -- DECRYPT
-- WITH ENCRYPTION AS
BEGIN

	DECLARE @ReturnStatus AS INTEGER

	EXECUTE @ReturnStatus = dbo.GI_ValidateSession @SessionGUID, @UserGUID
	IF @ReturnStatus > 0
	BEGIN
		EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_ListDictionary - Invalid session', 1, NULL
		RAISERROR ('DB.GI_ListDictionary - Invalid session.', 16, 1)
		RETURN 100 + @ReturnStatus
	END

	BEGIN TRY
	
		SELECT
			Word 
		FROM 
			dbo.Dictionary  WITH (NOLOCK)
		WHERE 
			DictionaryGUID = @CurrentInstanceGUID
		ORDER BY
			Word

		RETURN 0

	END TRY
	BEGIN CATCH

		-- Log and raise the error...
		DECLARE @ErrorMessage AS VARCHAR(1000) = ERROR_MESSAGE()
		EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_ListDictionary - Unknown error', 1, @ErrorMessage
		RAISERROR ('DB.GI_ListDictionary - Unknown error', 16, 1)
		RETURN 1
	
	END CATCH
	
END

CREATE PROCEDURE [dbo].[GI_AddWordToDictionary] 
(
	@SessionGUID AS UNIQUEIDENTIFIER,
	@UserGUID AS UNIQUEIDENTIFIER,
	@CurrentInstanceGUID AS UNIQUEIDENTIFIER,
	@TargetInstanceGUID AS UNIQUEIDENTIFIER,
	@InputString AS VARCHAR(500))
AS -- DECRYPT
-- WITH ENCRYPTION AS
BEGIN

	DECLARE @ReturnStatus AS INTEGER

	EXECUTE @ReturnStatus = dbo.GI_ValidateSession @SessionGUID, @UserGUID
	IF @ReturnStatus > 0
	BEGIN
		EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_AddWordToDictionary - Invalid session', 1, NULL
		RAISERROR ('DB.GI_AddWordToDictionary - Invalid session.', 16, 1)
		RETURN 100 + @ReturnStatus
	END

	BEGIN TRY
	
		DECLARE @Count AS INTEGER = 0
		SELECT
			@Count = COUNT(*)
		FROM
			dbo.Dictionary WITH (NOLOCK)
		WHERE
			DictionaryGUID = @CurrentInstanceGUID 
			AND Word = @InputString

		IF @Count = 0
		BEGIN

			INSERT INTO dbo.Dictionary (DictionaryGUID, Word)
			VALUES (@CurrentInstanceGUID, @InputString)

		END

		RETURN 0

	END TRY
	BEGIN CATCH	

		-- Log and raise the error...
		DECLARE @ErrorMessage2 AS VARCHAR(1000) = ERROR_MESSAGE()
		EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_AddWordToDictionary - Unknown error', 1, @ErrorMessage2
		RAISERROR ('DB.GI_AddWordToDictionary - Unknown error', 16, 1)
		RETURN 1
	
	END CATCH

END

CREATE PROCEDURE [dbo].[GI_RemoveWordFromDictionary] 
(
	@SessionGUID AS UNIQUEIDENTIFIER,
	@UserGUID AS UNIQUEIDENTIFIER,
	@CurrentInstanceGUID AS UNIQUEIDENTIFIER,
	@TargetInstanceGUID AS UNIQUEIDENTIFIER,
	@InputString AS VARCHAR(500)
)
AS -- DECRYPT
-- WITH ENCRYPTION AS
BEGIN

	DECLARE @ReturnStatus AS INTEGER

	EXECUTE @ReturnStatus = dbo.GI_ValidateSession @SessionGUID, @UserGUID
	IF @ReturnStatus > 0
	BEGIN
		EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_RemoveWordFromDictionary - Invalid session', 1, NULL
		RAISERROR ('DB.GI_RemoveWordFromDictionary - Invalid session.', 16, 1)
		RETURN 100 + @ReturnStatus
	END

	BEGIN TRY
	
		DELETE FROM 
			dbo.Dictionary 
		WHERE 
			DictionaryGUID = @CurrentInstanceGUID 
			AND Word = @InputString
	
		RETURN 0

	END TRY
	BEGIN CATCH	

		-- Log and raise the error...
		DECLARE @ErrorMessage AS VARCHAR(1000) = ERROR_MESSAGE()
		EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_RemoveWordFromDictionary - Unknown error', 1, @ErrorMessage
		RAISERROR ('DB.GI_RemoveWordFromDictionary - Unknown error', 16, 1)
		RETURN 1
	
	END CATCH

END

The contents of the Dictionary can be used directly by workflow or rendered by the Dynamic Page Builder in the String Dictionary field type. A String Dictionary can be configured to either enable, or prevent, a user from adding new words to the Dictionary using the GI_AddWordToDictionary TSQL Stored Procedure.

The String Dictionary uses the TelerikComboBox on demand setting to filter the contents of the Dictionary using the GI_ListDictionaryOnDemand TSQL Stored Procedure.

CREATE PROCEDURE [dbo].[GI_ListDictionaryOnDemand]
(
    @SessionGUID AS UNIQUEIDENTIFIER
    ,@UserGUID AS UNIQUEIDENTIFIER
    ,@CurrentInstanceGUID AS UNIQUEIDENTIFIER
    ,@TargetInstanceGUID AS UNIQUEIDENTIFIER
    ,@Filter AS VARCHAR(500)
)
AS -- DECRYPT
-- WITH ENCRYPTION AS
BEGIN

    DECLARE @ReturnStatus AS INTEGER

    EXECUTE @ReturnStatus = dbo.GI_ValidateSession @SessionGUID, @UserGUID
    IF @ReturnStatus > 0
    BEGIN
    EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'dbo.[[GI_ListDictionaryOnDemand]] - Invalid session', 1, NULL
        RAISERROR ('dbo.[[GI_ListDictionaryOnDemand]] - Invalid session.', 16, 1)
        RETURN 100 + @ReturnStatus
    END

    BEGIN TRY

        SELECT 1 AS Sequence, @CurrentInstanceGUID AS InstanceGUID, Word AS InstanceName
        FROM dbo.Dictionary I WITH (NOLOCK)
        WHERE Word LIKE @Filter + '%'
		AND I.DictionaryGUID = @CurrentInstanceGUID
        ORDER BY Word

        RETURN 0

    END TRY
    BEGIN CATCH

        DECLARE @ErrorMessage AS VARCHAR(1000) = ERROR_MESSAGE()
        EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'dbo.[GI_ListDictionaryOnDemand] - failed to list the required instances', 1, @ErrorMessage
        RAISERROR ('dbo.[GI_ListDictionaryOnDemand] - Error selecting from view.', 16, 1)
        RETURN 1

    END CATCH

END