Adding T-SQL for Data in Action or Data in Motion

The most efficient place to add code to implement a custom function is within the TRELLIS Core database itself. If you can build a T-SQL Stored Procedure and add it to the database, it can be complied and cached, improving its performance. Since the functionality is directly within the database, it can operate using set-based logic and doesn’t need to move data in and out of the database server.

Custom Action

The following code fragment outlines the general design of a custom action that could be directly invoked from the TRELLIS data presentation model. Custom actions are invoked directly by Commands and as a result of State Transitions.


    CREATE PROCEDURE [MyCustomAction]
        @SessionGUID AS UNIQUEIDENTIFIER,
        @UserGUID AS UNIQUEIDENTIFIER,
        @InstanceGUID AS UNIQUEIDENTIFIER
    AS
    BEGIN

        DECLARE @ReturnStatus AS INTEGER

        EXECUTE @ReturnStatus = dbo.V1CF_ValidateSession @SessionGUID, @UserGUID
        IF @ReturnStatus > 0
        BEGIN
            EXECUTE @ReturnStatus = dbo.V1CF_LogEvent @UserGUID, 'dbo.MyCustomAction – Invalid session', 1, NULL
            RAISERROR ('dbo.MyCustomAction - Invalid session.', 16, 1)
            RETURN 100
        END

        BEGIN TRY

            // Add your custom action code here…
            RETURN 0

        END TRY
        BEGIN CATCH

            DECLARE @ErrorMessage AS VARCHAR(1000) = ERROR_MESSAGE()
            EXECUTE @ReturnStatus = dbo.V1CF_LogEvent @UserGUID, 'dbo.MyCustomAction - failed to …', 1, @ErrorMessage
            RAISERROR ('dbo.MyCustomAction - Error ...', 16, 1)
            RETURN 1

        END CATCH

    END

This example demonstrates the basic interface of a TRELLIS T-SQL Stored Procedure which will accept THREE parameters: Session GUID, User GUID and Instance GUID. The Session GUID and User GUID must represent a currently active user session, and they are passed into V1CF_ValidateSession T-SQL before any attempt is made to execute the actual custom T-SQL. Any attempt to access the database without a valid Session GUID and User GUID will be immediately rejected.

If the request passes initial muster, the next step is to perform your custom functionality within the context of an exception handler. That way, if an error occurs, you will be able to extract the SQL Server error message, log the error and initiate any required recovery action.

Typically, your custom action will execute against an existing Instance record within the database, which is what the Instance GUID is for. The TRELLIS solution components automatically extract the Session GUID, User GUID and Instance GUID from the data you where interacting with when you initiated the event that requested this action.

Custom List

The following code fragment outlines the general design of a custom list that could be directly invoked from the TRELLIS data presentation model to populate a data grid. Custom lists are invoked directly by the dynamic page generation code of both web and mobile applications.


    CREATE PROCEDURE [MyCustomList]
        @SessionGUID AS UNIQUEIDENTIFIER,
        @UserGUID AS UNIQUEIDENTIFIER,
        @InstanceGUID AS UNIQUEIDENTIFIER
    AS
    BEGIN

            DECLARE @ReturnStatus AS INTEGER

            EXECUTE @ReturnStatus = dbo.V1CF_ValidateSession @SessionGUID, @UserGUID
            IF @ReturnStatus > 0
            BEGIN
                    EXECUTE @ReturnStatus = dbo.V1CF_LogEvent @UserGUID, 'dbo.MyCustomList – Invalid session', 1, NULL
                    RAISERROR ('dbo.MyCustomList - Invalid session.', 16, 1)
                    RETURN 100
            END

            BEGIN TRY

                    // Add your custom list code here…
                    // You must have EXACTLY ONE SELECT statement that returns the rows
                    RETURN 0

            END TRY
            BEGIN CATCH

                    DECLARE @ErrorMessage AS VARCHAR(1000) = ERROR_MESSAGE()
                    EXECUTE @ReturnStatus = dbo.V1CF_LogEvent @UserGUID, 'dbo.MyCustomList - failed to …', 1, @ErrorMessage
                    RAISERROR ('dbo.MyCustomList - Error ...', 16, 1)
                    RETURN 1

            END CATCH

    END

This example follows the same basic pattern as the custom action T-SQL. The big difference here is that the T-SQL should include EXACTLY ONE SELECT statement that will return the required rows. The dynamic page builder will call your custom list command when it needs to populate the data grid to which it is bound.

You can also provide a paged version of your custom list if required by adding extra parameters:


        @PageSize AS INT,
        @PageNumber AS INT

Custom Test

The following code fragment outlines the general design of a custom test that could be directly invoked from the TRELLIS data presentation model to determine a True/False condition. Custom tests are used for form/field permission validation, and by Gatekeepers to restrict execution of Commands and State Transitions.


    CREATE PROCEDURE [MyCustomTest]
        @SessionGUID AS UNIQUEIDENTIFIER,
        @UserGUID AS UNIQUEIDENTIFIER,
        @InstanceGUID AS UNIQUEIDENTIFIER
    AS
    BEGIN

            DECLARE @ReturnStatus AS INTEGER

            EXECUTE @ReturnStatus = dbo.V1CF_ValidateSession @SessionGUID, @UserGUID
            IF @ReturnStatus > 0
            BEGIN
                    EXECUTE @ReturnStatus = dbo.V1CF_LogEvent @UserGUID, 'dbo.MyCustomTest – Invalid session', 1, NULL
                    RAISERROR ('dbo.MyCustomTest - Invalid session.', 16, 1)
                    RETURN 100
            END

            BEGIN TRY

                    // Add your custom list code here…
                    // You must have EXACTLY ONE SELECT statement that returns the rows
                    RETURN 0

            END TRY
            BEGIN CATCH

                    DECLARE @ErrorMessage AS VARCHAR(1000) = ERROR_MESSAGE()
                    EXECUTE @ReturnStatus = dbo.V1CF_LogEvent @UserGUID, 'dbo.MyCustomTest - failed to …', 1, @ErrorMessage
                    RAISERROR ('dbo.MyCustomTest - Error ...', 16, 1)
                    RETURN 1

            END CATCH

    END

This example follows the same basic pattern as the custom action T-SQL. The big difference here is that the T-SQL should include EXACTLY ONE SELECT statement that will return the required rows. The dynamic page builder will call your custom list command when it needs to populate the data grid to which it is bound.