SQL Server – Creating Hangfire Jobs via Stored Procedures

sql serverstored-procedurestrigger

I know how to create Hangfire jobs via .NET C# code, but there are a few occasions where I'd like for a database trigger to execute a Hangfire job. I searched the web but found nothing so I attempted to write my own stored procedure.

The Hangfire tables are in SQL Server so I assumed that I should be able to create a job by inserting records into the right tables.

It wasn't an easy process, but what I did was to create a job via C# code, study how Hangfire stored the data, the wrote a stored procedure to accomplish the same thing. The following code works, but is admittingly sloppy.

Can anyone suggest improvements to this, or suggest a better stored procedure?

    -- =============================================
    -- Author:      Gary Jorgenson, RN | robintek.com
    -- Create date: 3/23/2021
    -- Description: Create a Hangfire Job and Enqueue it
    -- =============================================
    ALTER PROCEDURE [dbo].[core_sp_CreateHangFireJob]
        @type AS VARCHAR(150) = NULL ,
        @Method AS VARCHAR(50) = NULL ,
        @AppCode AS VARCHAR(50) = NULL ,
        @Version AS VARCHAR(15) = NULL ,
        @Culture AS VARCHAR(50) = NULL ,
        @PublicKeyToken AS VARCHAR(50) = NULL ,
        @ParameterTypes AS VARCHAR(250) = NULL ,
        @Arguments AS VARCHAR(250) = NULL
    AS
    BEGIN

DECLARE @RandomString AS VARCHAR(MAX) = LOWER(RIGHT(CAST(NEWID() AS NVARCHAR(MAX)),8))

-- Build the JSON string Hangfire wants
DECLARE @InvocationData AS VARCHAR(2000) = 
'{"Type":"' + ISNULL(@type,'BackgroundJobs') + ', ' +
'App_Code.' + ISNULL(@AppCode, @RandomString) + ', ' +
'Version=' + ISNULL(@Version, '0.0.0.0') + ', ' +
'Culture=' + ISNULL(@Culture,'neutral') + ', ' +
'PublicKeyToken=' + ISNULL(@PublicKeyToken,'null') + '",' + 
'"Method":"' + ISNULL(@method,'UpdateActivityLog') + '","' +
'ParameterTypes":"' + ISNULL(@ParameterTypes,'[]') + '","' +
'Arguments":null}'

-- For some reason, Hangfire seems to set the inserted date/time as 4 hours ahead of actual time
DECLARE @inserted DATETIME = DATEADD(HOUR, 4, GETDATE());

-- Insert a record into Hangfire Job table. First of several inserts
INSERT INTO [dbo].[Job] 
    (
    [StateName] ,
    [InvocationData] ,
    [Arguments] ,
    [CreatedAt]
    )
    VALUES
    (
    'Enqueued' ,
    @InvocationData ,
    '['+ ISNULL( '"' + @Arguments + '"','') + ']',
    @inserted
    )

    -- Get the JobId as we'll need it for other inserts
    DECLARE @JobId AS BIGINT = IDENT_CURRENT('[dbo].[Job]');

    -- Break out date and time separately as well need to build
    -- a JSON string formatted properly for Hangfire
    DECLARE @insertedDate AS DATE = CONVERT(DATE, @inserted);
    DECLARE @insertedTime AS TIME = CONVERT(VARCHAR(10), @inserted, 108)

    DECLARE @EnqueuedAt AS VARCHAR(2000) = 
    '{"EnqueuedAt":"' +
    CONVERT(VARCHAR(20), @insertedDate) +
    'T'+ CONVERT(VARCHAR(20), @insertedTime) + 
    'Z","Queue":"default"}'

    -- Insert record into Hanfire state table showing this job
    -- as Enqueued
    INSERT INTO [dbo].[State]
           (
            [JobId] ,
            [Name] ,
            [Reason] ,
            [CreatedAt] ,
            [Data]
           )
     VALUES
           (
            @JobId ,
            'Enqueued',
            NULL ,
            @inserted ,
            @EnqueuedAt
           )

    -- Retrieve the new StateId as we have to update the job record with current StateId
    DECLARE @StateId AS BIGINT = scope_identity();

    UPDATE 
        [dbo].[Job]
    SET 
        StateId = @StateId
    WHERE
        id = @JobId

    -- Insert records into Hangfire JobParameter table showing the CurrentCulture
    INSERT INTO [dbo].[JobParameter]
       (
       [JobId] ,
       [Name] ,
       [Value]
       )
    VALUES
       (
       @JobId ,
       'CurrentCulture' ,
       '"en-US"'
       )

    -- Insert record into Hangfire JobParameter table showing the CurrentUICulture
    INSERT INTO [dbo].[JobParameter]
       (
       [JobId] ,
       [Name] ,
       [Value]
       )
    VALUES
       (
       @JobId ,
       'CurrentUICulture' ,
       '"en-US"'
       )

    -- Lastly, inserting the job Id into the Hangfire JobQueue table to start the job
    INSERT INTO [dbo].[JobQueue]
    (
        [JobId] ,
        [Queue] 
    )
    VALUES
    (   @JobId ,     -- JobId - bigint
        N'default'   -- Queue - nvarchar(50)
    )

    RETURN 1
    END

From the Hangfire website

Hangfire is an open-source framework that helps you to create, process and manage your background jobs, i.e. operations you don't want to put in your request processing pipeline.

It's become a popular tool for web application developers to manage background jobs. In most cases, the Hangfire job is created within application code or setup as a recurring task.

I initially planned to post this question on Stack Overflow as developers are more likely to be familiar with Hangfire. It was suggested though that I post more SQL oriented questions here.

Best Answer

On the Hangfire website I couldn't find any reference that it supports outside modifications of the SQL database. So I would assume that it is not supported.

When you modify database directly, you are skipping all caching and validation layers implemented in the application. It may lead to unstable application and it is very hard to debug. Also, database schema may change when product is updated.

I would recommend different solution:

  1. If you need fast triggers and semi-realtime jobs queening from triggers use service broker queues. In the trigger you will create a message and then use C# service, SSIS or regular windows service to respond to messages and create jobs.
  2. If trigger can be slower and you want to fail the trigger if the job creation fails, call web API from the trigger (C# stored proc or OLE).

As a side note, DATEADD(HOUR, 4, GETDATE()) will not work for winter time, you would need a proper conversion to UTC, using AT TIME ZONE in SQL Server.

Related Question