Table – SelectedInstances

Purpose

Keeps track of relationships between Instance records for the GI_SelectedItem user control.

Definition

CREATE TABLE [dbo].[SelectedInstances](
	[InstanceGUID] [UNIQUEIDENTIFIER] NOT NULL,
	[SelectedInstanceGUID] [UNIQUEIDENTIFIER] NOT NULL,
 CONSTRAINT [PK_SelectedInstances] PRIMARY KEY CLUSTERED 
(
	[InstanceGUID] ASC,
	[SelectedInstanceGUID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Column Definitions

InstanceGUID is the InstanceGUID of the current record that was being selected against.

SelectedInstanceGUID is the InstanceGUID of the record that was selected for the current instance.

Key Functionality

This functionality is used the GI_SelectedItem field user control (an example of which is shown below). It allows the user to indicate which items are selected against the current record being displayed.

The left-hand side of the control shows which instances are currently available for selection. Clicking on an available item will add it to the SelectedInstances table, remove it from the left-hand table, and add it to the right-hand table.

The right-hand side shows which instances have already been selected. Clicking on a currently selected instance will remove it from the SelectedInstances table, remove it from the right-hand table and add it back into the left-hand table.

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

	DECLARE @ReturnStatus AS INTEGER

	-- Try to validate the session data...
	EXECUTE @ReturnStatus = dbo.GI_ValidateSession @SessionGUID, @UserGUID
	IF @ReturnStatus <> 0
	BEGIN
		
		-- Log and raise the error...
		EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_AddSelectedItem - Invalid session', 1, NULL
		RAISERROR ('DB.GI_AddSelectedItem - Invalid session.', 16, 1)
		RETURN 100 + @ReturnStatus
			
	END

	BEGIN TRY
		
		INSERT INTO dbo.SelectedInstances (InstanceGUID, SelectedInstanceGUID)
		VALUES (@CurrentInstanceGUID, @TargetInstanceGUID)

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

END

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

	DECLARE @ReturnStatus AS INTEGER

	-- Try to validate the session data...
	EXECUTE @ReturnStatus = dbo.GI_ValidateSession @SessionGUID, @UserGUID
	IF @ReturnStatus <> 0
	BEGIN
		
		-- Log and raise the error...
		EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_RemoveSelectedItem - Invalid session', 1, NULL
		RAISERROR ('DB.GI_RemoveSelectedItem - Invalid session.', 16, 1)
		RETURN 100 + @ReturnStatus
			
	END

	BEGIN TRY
		
		DELETE FROM dbo.SelectedInstances
		WHERE InstanceGUID = @CurrentInstanceGUID AND SelectedInstanceGUID = @TargetInstanceGUID

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

END

The TSQL Stored Procedures used to populate the available and currently selected tables will be custom coded for each control. One useful thing to remember to implement is to restrict the lists so that any particular instance only shows up in one table.

The SelectedInstances table also has an optional trigger SelectedInstancePull that can be used to add SelectedInstances changes to the InstanceChanges log.

ALTER TRIGGER [dbo].[SelectedInstancesPull]
ON [dbo].[SelectedInstances] FOR INSERT, UPDATE, DELETE
AS 
BEGIN

	DECLARE @InstanceGUID AS UNIQUEIDENTIFIER

	DECLARE DeletedRecords CURSOR FOR 
	SELECT InstanceGUID FROM Deleted

	OPEN DeletedRecords

	FETCH NEXT FROM DeletedRecords INTO @InstanceGUID

	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXECUTE dbo.GI_InstanceChanged @InstanceGUID, 'Selected Instances'
		FETCH NEXT FROM DeletedRecords INTO @InstanceGUID
	END

	CLOSE DeletedRecords;
	DEALLOCATE DeletedRecords;

	DECLARE InsertedRecords CURSOR FOR 
	SELECT InstanceGUID FROM Inserted

	OPEN InsertedRecords

	FETCH NEXT FROM InsertedRecords INTO @InstanceGUID

	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXECUTE dbo.GI_InstanceChanged @InstanceGUID, 'Selected Instances'
		FETCH NEXT FROM InsertedRecords INTO @InstanceGUID
	END

	CLOSE InsertedRecords;
	DEALLOCATE InsertedRecords;

END