Simple queuing with SQL Server and C#

I've been working on some simple queuing code using a SQL Server database and some C#. Items are queued by being inserted into a SQL table and then taken off the queue in batches. Multiple processes should be able to remove items from the queue at the same time. Oh, and you should be able to remove a subset of queued records in case you only want to dequeue records that match some criteria. What I came up with is surprisingly simple, the key part is the following SQL:

WHERE TestQueue.Id IN
  SELECT TOP(10) Id -- maximum batch size
  FROM TestQueue
  WHERE Data >= 0 AND Data <= 1000 -- selection criteria

...this way you can set a batch size, meaning you can remove a number of records at a time and you can specify a criteria when you only wish to remove certain items from the queue. The beauty of the above SQL command is that it deletes the records and returns them at the same time. If there aren't enough records in the queue to fill a batch then it returns whatever it finds.

Here is how you could try it out in a C# program:

  1. Create a new Console App in Visual Studio 2012
  2. Add the NuGet package called 'PetaPoco' (which is a micro-ORM)
  3. Create the Queue Table, like this
  4. Set a connection string called "SQLq" in the App.Config of your Console App
  5. Use this program code

The example code will clear the table, then queue 1000 items. Next it will spin up six threads each dequeuing different ranges of items using a variery of different batch sizes. After the process is complete the table should be empty - all the items should have been removed.

Well, OK, this is just test code to show how it works, you'd write it differently in a live system, but hopefully it shows the idea...