...or rather... if VistaDB was a woman... and I wasn't already married... and I was into chicks who were thread-safe and crash-safe.... then I'd marry it.

Since it is (by far) the most popular article on my blog, I'm assuming that anyone reading this knows about my long and painfully tortuous journey through the dregs of .NET embedded databases in an attempt to find a stable backend for TaskRunner. Well... I have finally arrived at a solution which I am perfectly content with. (yes, I realize that's bad grammer... sue me.)

Last you heard, I was going ahead with my implementation of SharpHSQL as the backend. I had a few "issues", but I thought I had a way of getting around them... well.... that has all changed.

I was able to somewhat compensate for the shortcomings of SharpHSQL in regards to being crash-safe. But I still had a number of issues. Sometimes, when restoring the db from a crash, it would insert a record that already existed in a table that had a unique index on it. So the next time my code tried to hit that table, I'd get a index violation error. Other problems arose when the db was not closed properly, so I had to write in some code that would check if it had closed properly the last time TR was open, and if not it would force a restart of the app, just to "clear out" some of the issues that would have arisen from the crash. But that wasn't all... there were certain records that wouldn't get restored, so I had to code to account for that (by manually writing my own "restore" process for certain tables in case they got corrupted). Basically, I just kept coding and coding and coding to account for it's issues with multi-threading and crashing and after I had done all I could think of, I still had some instabilities that I just couldn't get rid of.

So finally, I had enough (again).... and as much as I hated to... I went back out on my quest for an embedded database to use for TaskRunner. Much to my (quite unexpected) delight... my search didn't last long. One of the very first results in my first Google search was for this blog entry, which mentioned that VistaDB was giving away a copy of it's .NET db to anyone who would plug the product on their blog. Well... even though I mentioned in my embedded database rant that I had trouble getting VistaDB to work when I tried the demo version, I figured I'd be an idiot not to take them up on this offer. So I posted the required information on my blog, and that day I received my copy of VistaDB.

Well... I figured out what I was doing wrong when I tried implementing VistaDB the first time (basically, an extremely stupid over-sight on my part ). But, at that time, I wasn't sure if I was willing to pay the ~180 bucks it would cost to use VistaDB, so I didn't really pursue it too much. This time, since I had the full .NET db for free, I was obviously more motivated to get it working. So, after a bit of hacking, and a couple minor issues I had to work around, I had VistaDB up and running in TaskRunner.

The first thing I tested was its thread-safe... uh... ness. This was the deal-breaker for a number of the other db's, so I wanted to see how VistaDB did. But, I threw everything I could at it, and didn't get a single irregular result, nor did I run into any access violations. So... I moved on to crash testing (which was the downfall of SharpHSQL). I crashed VistaDB every way from Sunday... I crashed it right after making an update... right after making multi-threaded updates... right after making updates and reverting to previous states... etc... etc... etc... and every time I restarted the app... it looked just like it had before the crash ever happened.

At this point, it has been running in TaskRunner on both of my machines for about a week or so, and I haven't found a single issue with it.

So it looks like my quest is finally over. I have a stable backend for TaskRunner that meets my requirements. It's SQL capable... It has a .NET provider (available as source code or a dll)... It has a small footprint (~500KB for the engine)... It's thread-safe... It's crash-safe... It was free (for me). The only minor requirement that it didn't meet was being 100% managed .NET code. The engine itself is an external COM dll that you have to include with your application. But that was not a deal-breaker by any means... just a matter of preference. And the next version of VistaDB (3.0) is supposed to be 100% .NET, so even that issue won't exist in the future.

So.... if you're looking for an free embedded database for your .NET app... and you've been pounding your head against a wall (like me) trying to get all those other free db engines to work for your app... then hopefully you have a blog.  Because getting a free copy of VistaDB could be the best thing that ever happened to you....... you know... aside from stuff like getting married and having kids and all that crap.

WATYF