Self configuring embedded database in C#

I recently needed to write a C# desktop application which used its own small database. I wanted something that could be easily deployed with xcopy, so I didn’t want to connect to any external database engine. So I used SQL Compact Edition v4. Since I’ve recently been having success with PetaPoco as a micro-ORM type thing, I thought that I’d try that too. I had the idea that the database could configure itself when the program starts, so that’s what I set out to try. I was surprised how easy it was. This is what I did to make a proof-of-concept:

  • Create an empty Console Application in Visual Studio 2012
  • Use NuGet to add references to PetaPoco.Core (v5.0.1) and Microsoft.SqlServer.Compact (v 4.0.8876.1)
  • Have this code as the C# program

That’s all there is to it, easy. If you corrupt the database, then the code will re-create a clean database file next time it runs. You don’t need to include an sdf file in your project, because the database will be created by the code. Obviously this is an extremely trivial example, but it seems to work great.

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