Anders G. Nordby

Senior Systems Consultant at CGI

Tag Archives: .Net

Implementing a Persisted Queue in SQL

In the project I’m currently working with, we need to update products. The product IDs (which are strings of 20 characters or less), are all we need to store, but we want to handle these in the order we got them. We receive lists of products to update faster than we can possibly handle synchronously, but it is not important that they are updated immediately, so we agreed on implementing a queue.

First, I created a SQL database, with a single table:

CREATE TABLE [dbo].[Queue](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ProductId] [varchar](20) NOT NULL
) ON [PRIMARY]

Next, I created two stored procedures, one for pushing items onto the queue, and one for retrieving them.

CREATE PROCEDURE [dbo].[PushToQueue]
	@productId varchar(20)
AS
BEGIN
	SET NOCOUNT ON;

	IF @productId IS NOT NULL AND @productId <> ''
		INSERT INTO queue (productId)
		SELECT @productId
		WHERE NOT EXISTS (SELECT ProductId FROM Queue WHERE ProductId = @productId)
END
CREATE PROCEDURE [dbo].[PopQueue]
	@productId varchar(20) out
AS
BEGIN
	SET NOCOUNT ON;
	BEGIN TRANSACTION

		SET @productId = (SELECT top 1 ProductId FROM [Queue] ORDER BY Id)

		IF @productId IS NOT NULL AND @productId <>''
			DELETE FROM [Queue] WHERE ProductId = @productId

	COMMIT
END

In C# I created a simple Queue class, which takes care of pushing/popping:

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace PimImageUpdateHelper
{
    public static class Queue
    {
        private static string ConnectionString => 
            ConfigurationManager.ConnectionStrings["productQueue"].ToString();

        public static void Push(string productId)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();

                const string commandText = "PushToQueue";
                var command = new SqlCommand(commandText, connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                var paramProductId = new SqlParameter("productId", SqlDbType.VarChar)
                {
                    Value = productId
                };

                command.Parameters.Add(paramProductId);
                command.ExecuteNonQuery();
            }
        }

        public static string Pop()
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();

                const string popText = "PopQueue";
                var popCommand = new SqlCommand(popText, connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                var popParameter = new SqlParameter("productId", SqlDbType.VarChar, 20)
                {
                    Direction = ParameterDirection.Output
                };

                popCommand.Parameters.Add(popParameter);
                popCommand.ExecuteNonQuery();

                return popParameter.Value.ToString();
            }
        }

        public static bool PopQueue(out string productId)
        {
            productId = Queue.Pop();
            return !string.IsNullOrEmpty(productId);
        }
    }
}

This makes the actual usage of the Queue reallly simple. For example, in a sceduled job that handles the product updates, the loop for going through the Queue simply looks like this:

string productId;
while (Queue.PopQueue(out productId))
{
	// Do stuff here...
}