Background Jobs system with SQL Queue

Intermediate

2019-03-16

What is Background Jobs ?

A Background job is a functions that runs in the background with a periodical interval e.g every 1 minute.

Background jobs are very useful when we have a batch requests that we need to handle and the user shouldn't wait till all the requests queue is empty till his request is ready to be processed. We can batch the request into our queue, process it when we are ready and send the result to the user's email.

In the previous tutorial : How to Implement a Queue System in SQL we developed a queuing system based on SQL table. We also created "DequeueBatch" stored procedure that pulls from the queuing table based on a status flag and return the result to be processed.

In this tutorial we will register this dequeue function as a background job to periodically check for records ready to be pulled.

What Tools we will use ?

We are using SQL-Server and Hangfire Nuget Package.

Hangfire is a background jobs framework that works well with .NET and .NET Core. you don't need to install a windows service. or do any other configuration.

It has a great dashboard for tracking the jobs.

We are going to use the free license which is more than great.

If you didn't prepare the SQL table and stored procedure you can go to the previous tutorial and follow the steps.

How to install Hangfire ?

  • Open Visual Studio
  • From top left menu choose File >> New >> Project
  • Choose Console App (.Net Framework)
  • Write In the project name and press OK.

Once the project is created

  • Right click on the project name from the solution explorer from the right panel
  • Choose Manage Nuget Packages
  • Nuget manager window will open, Choose Browse and search for "hangfire"
  • Install the package
Nuget
Hangfire

Once the package installed a new readme text file iwll open with instructions on what code you need for the "Startup" file.

But first you need to create this Startup.cs file, Right click on the project >> Add >> new file.

type the file name as "Startup.cs" and click create.

Then edit the created file to contain the following code


Edit the namespaces to be the one you use for your application.

Hangfire needs a connection string to the database so that it can store and keep track of the jobs.

Adding SQL connection string

Since we are using Entityframework as mentioned in the previous tutorial, and since we already created our table and stored procedure.
Then we will include the schema with database first approach.

Let's start by installing Entityframework
  • Right click on project name
  • Manage Nuget Packages
  • Click browse and search for "entityframework"
  • click install
  • a multiple confirmation windows will pop up . Just click "yes to all"

Entity Framework

Then add the database model

  • Right click on project
  • Add new item
  • Choose Data item >> ADO.NET Entity Data Model
  • Choose EF designer from database
  • Click New Connection
  • In Server Name type ".\SQLEXPRESS" or whatever sql server you are hosting your DB in.
  • Choose the database from bottom
  • Choose what tables you want to import
  • The import process will edit the app.config file with the connection string to database

Ado Model1

Ado Model2

Ado Model3

Ado Model4

Ado Model5

Ado Model6





open app.config and copy the connection string name you have selected to that startup file. so the new startup file will be as follows



Adding Dequeue Batch Code

Open Program.cs file and edit the code like follows


A Dequeue Class is instantiated, it contains the background job registration logic in the Init method. We Registered it to run every minute

No If you checked the database, you can find the newly created tables for the background jobs queue.

Hangfire SQL


Summary

In the previous tutorial : How to Implement a Queue System in SQL we developed a queuing system based on SQL table. We also created "DequeueBatch" stored procedure that pulls from the queuing table based on a status flag and return the result to be processed.

In this tutorial we installed hangfire framework and registered the background job to work every minute and run the dequeue function to pull a count of "batchsize" records to be processed.

This solution can work well for your batch jobs or API Calls system or any heavy time consuming jobs.