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:

DELETE FROM TestQueue WITH (READPAST) OUTPUT DELETED.*
WHERE TestQueue.Id IN
(
  SELECT TOP(10) Id -- maximum batch size
  FROM TestQueue
  WHERE Data >= 0 AND Data <= 1000 -- selection criteria
  ORDER BY Id ASC
)

...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...

Using my WCF http Basic Authentication code in VS 2012

I blogged about the implementation of http basic authentication that I made in this post here, but this is a more detailed description of how to get it working in a simple WCF service in Visual Studio 2012.

First, you'll need to set up a basic out-of-the-box WCF service in Visual Studio 2012:

...and change it slightly so that it can be used from a browser, as described in this post here. You should now be able to call your service from any web browser to see it's working properly.

Personally, I just stuck to the default Microsoft code example and added a [WebGet] attribute, like this:

[WebGet(UriTemplate="getdata?num={value}")]
public string GetData(int value)
{
   return string.Format("You entered: {0}", value);
}

...which means that I can call http://localhost:8080/Service1.svc/getdata?num=1 to pass a querystring value to my method.

Now, add these files to your project (BasicAuthenticationAttribute.cs, BasicAuthenticationHttpHeaderInjector.cs and BasicAuthenticationPasswordValidator.cs). When you've done that, just add the [BasicAuthentication] attribute to your service class. That's it! Try running the project again.

You should be asked for a username and password by the browser. Now, the WCF service will only return some data when you have used these details: username='user' and password='password'. You can change the BasicAuthenticationPasswordValidator class to implement whatever means you like to check that the user is authorised.

You now have a code-only implementation of http basic authentication for WCF. If you're using IIS, you don't need to configure anything to make this work, as far as IIS is concerned you are using anonymous access. But it should also work in IIS Express or the Visual Studio Development Server.

NOTE: this code makes no attempt to encrypt the username and password you type into the browser. The details will be sent in clear text as an http header. You should at least consider using https to encrypt the data. The purpose of this code is just to show how http basic authentication works, and how it could be done in code.

WCF HTTP Basic Authentication in Code

So I wanted to build a WCF service with a very simple authentication mechanism, and I thought that it would be good to start with my own implementation of http basic authentication - and then I could go from there, adding my own extras to the protocol if I wanted. But I thought that basic authentication over https would at least be a start.

And even though the username and password are sent in clear text, doing that over an https connection should be safe since it's encrypted before going over the wire.

Finally, I wanted to do the whole lot in code, so that I can deploy it to some hosted space without fiddling around in IIS. But also because I like code :-)

The good thing is that basic authentication is supported by most browsers, so I could expose the WCF service as web methods and test it very easily. I have assumed that since it works with all the major browsers I have implemented the protocol correctly.

The end result was a custom attribute which can be added to a WCF service and does all the work for you. The only other thing that needs to be done is modify the method which decides if a user is valid or not.

Here is the source code (click the image to download):

I'll post an example of how to use it (in Visual Studio 2012) in another blog.

Virtualise, Wipe and Reinstall

My home PC had been getting slower over time ... over the years I have installed a lot of software that I don't really use anymore, and there were still a bunch of Lenovo tools on there from when it was new. So it was time for a clean reinstall - a reformatted disk with a fresh copy of Windows can work wonders. But I still wanted a full backup of the old machine, just in case. So I thought that it would be good to virtualise the whole machine before I wiped it. Then I could always go back to the old version if I needed anything. Which is normally the case...

Anyway, I used the excellent Disk2VHD tool which can virtualise a hard disk whilst it is still running. So you can create the VHD on the machine that you're virtualising. Very cool.

So I'm now using VirtualBox to boot up the Virtual Hard Disk which has been created. It was a very nice way to achieve a clean install without losing anything, and it was pretty painless. I might do the same thing on my laptop actually...

Halfords Car Battery Problem

If you go to your favourite search engine and search for the term "halfords battery unserviceable warranty", you’ll likely find many threads on various forums from people who have experienced the same problem as me.

A few weeks ago, my car battery, which I bought from Halfords with a 3 year guarantee stopped holding a charge. I would get in the car in the morning and it would not have enough power to start the engine. So I took it back for replacement under the guarantee. The people in the store tested it, and the test machine said the battery was 'unserviceable'.

However, according to the staff at Halfords, this means that the battery had failed for some reason which is "not covered by the guarantee". I could not understand how the battery can fail to hold a charge after normal use and not come under the guarantee. What seemed worse to me, was that they were happy to solve the problem by selling me an identical battery. That just didn't feel right to me.

But... I felt like I didn't have much choice, so under protest I paid for the new battery, even though I thought that it should have been done under the guarantee. But I decided to take this up with Halfords by sending an e-mail to their customer services department when I got home.

An e-mail discussion followed for about two weeks. At first they held their position saying that the fault with the battery was not covered by the guarantee. However, I started pointing out that under the Sale Of Goods Act it would be reasonable for me to expect the battery to last for 3 years, since the battery has "Maintenance Free Battery 3 Year Guarantee" written on the side in big letters:

In the end, whilst Halfords stuck to their guns and even suggested that something may be wrong with the electrics in my car, they decided to refund the cost of the replacement battery anyway. Personally, I don't have any reason to think there is anything wrong with the car. It's a well maintained Honda and everything seems to be fine. But Halfords did indeed send me a cheque for the full price of the battery - so fair play to them. I didn't end up out of pocket.

So if you have a similar problem, then complian to customer services and don't give up. A few e-mails to Halfords customer services may get you your money back. When writing to them, be nice, but firm... and persistent. It may come out in your favor, just for the effort of writing a few e-mails.