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


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

-- For some reason, Hangfire seems to set the inserted date/time as 4 hours ahead of actual time

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

    -- 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) + 

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

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

        StateId = @StateId
        id = @JobId

    -- Insert records into Hangfire JobParameter table showing the CurrentCulture
    INSERT INTO [dbo].[JobParameter]
       [JobId] ,
       [Name] ,
       @JobId ,
       'CurrentCulture' ,

    -- Insert record into Hangfire JobParameter table showing the CurrentUICulture
    INSERT INTO [dbo].[JobParameter]
       [JobId] ,
       [Name] ,
       @JobId ,
       'CurrentUICulture' ,

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

    RETURN 1

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.

  • Cool, one suggestion I can make to help you figure out what code runs and what tables it updates when a Hangfire job is created in C# (etc), you can look into some profiling tools like the Profiler or Extended Events. These SQL Server utilities will tell you the exact queries being executed on the server at a given point in time.
    – J.D.
    Commented May 21, 2021 at 0:07

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.