Skip to main content

Actions

In combination with the import service, stored procedures can be modified to implement business logic. In the database, stored procedures with the prefix "mboc_" can be invoked by the import service.

Anatomy of an import stored procedure​

In essence, the whole exercise of connecting Dime.Scheduler to a different system is to transfer data from point A to point B. To put it in the words of Joey Tribbiani, the processor guy, it's mostly a matter of "putting numbers from one column into another column". Let's have a look at a simple one, the pins. The data model of a pin couldn't be any simpler: it contains an id, name and a color. This is reflected in its stored procedure.

The parameters contain the data that should be stored in the database. At this point, you may be wondering why there is no "Id" (or any PK) in there. Recall that data may originate from other systems, which makes identifiers utterly redundant. The best next thing would be to map data using the "Name" field. The function fGetPinId does actually that, querying the Pins table for any record that contains the name that was specified in the @Name parameter. If the record exists, that particular will be updated. If it doesn't, a new item will be created. Hence the name of the stored procedure: "upsert".

CREATE PROCEDURE [dbo].[mboc_upsertPin]
    @Name nvarchar(100),
    @HexColor nvarchar(50) = '',
    @ColorR int = NULL,
    @ColorG int = NULL,
    @ColorB int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @PinId int

    IF @Name = '' BEGIN
        RAISERROR(N'Parameter "Name" cannot be empty when creating pins.',16, 0)
        RETURN
    END

    SET @PinId = dbo.fGetPinId(@Name)

    IF @ColorR IS NOT NULL AND @ColorG IS NOT NULL AND @ColorB IS NOT NULL
        SET @HexColor = '#' + CONVERT(nvarchar(10), CONVERT(VARBINARY(1), @ColorR) + CONVERT(VARBINARY(1), @ColorG) + CONVERT(VARBINARY(1), @ColorB), 2)

    IF @PinId IS NOT NULL
        IF @HexColor = ''
            SELECT @HexColor = Color
            FROM [Pins]
            WHERE Id = @PinId

    IF @PinId IS NULL
        INSERT INTO [Pins] ([Name],[Color])
        VALUES (@Name,@HexColor)
    ELSE
        UPDATE [Pins]
        SET [Name] = @Name,
            [Color] = @HexColor
        WHERE [Id] = @PinId
END

CREATE FUNCTION [dbo].[fGetPinId]
(
    @PinName nvarchar(100)
)
RETURNS int
AS
BEGIN
    IF COALESCE(@PinName,'') = ''
      RETURN NULL

    RETURN(SELECT TOP 1 [Id] FROM [Pins] WHERE [Name] = @PinName)
END

That's all there is to it. Clearly, there are more complicated sprocs like the ones concerned with jobs and tasks but they basically do the same thing, which is to connect other systems' data with Dime.Scheduler.

List of supported actions​

The names of the stored procedures and their purpose are fairly self-explanatory. For example, it doesn't take a rocket scientist to figure out that mboc_deleteAppointment is responsible for deleting appointments from the database.

Nonetheless, for the sake of being complete, the table below enlists all the stored procedures which can be invoked by the import service. Of course, feel free to peruse the stored procedures in the database.

Last updated on by Hendrik Bulens