VistaDB, SQLite and Microsoft Access

Recently I've been carrying on my investigations into databases. SQLite originally looked promising as an alternative to Microsoft Access - read my SQLite investigation post for more info. SQLite looked small, fast, robust and very easy to install. However, I eventually decided it wasn't going to work as an Access replacement for me.

The main reason I decided not to replace Microsoft Access with SQLite was that I'd got too used to ease with which I could get when using the .Net datatypes seamlessly with Microsoft Access, e.g. I could read and write the .Net Decimal type straight to Access. With SQLite it seemed that I might have to convert to and from strings to preserve accuracy. This wasn't a big showstopper but I'm afraid I like my programming to be as easy as possible.

I'd pretty much resigned myself to using Microsoft Access but then I came across a really neat database system called VistaDB. VistaDB looks (I suspect) like it may have been originally based on SQLite but the great thing about it is that it has the rich data type support you get with Microsoft Access - plus extras like stored procedures - without losing the no hassle deployment of SQLite. I've spent a few hours evaluating VistaDB and so far it looks ace. For my particular application it solves all the downsides of both SQLite and Microsoft Access.

SQLite and ADO.Net: Acting as an Access replacement

I've had a bit of a play recently with SQLite and ADO.Net to see if I could slot in SQLite instead of MS Access in my desktop application. As yet I haven't made my mind up, but it's been a very interesting exercise. Here's a quick summary of my findings so far.

DataType Support

With my limited experience, SQLite seems to support fewer data types than MS Access. On the whole this isn't a problem apart from two areas - the .Net Decimal type and the .Net DateTime type. In both these cases I think you end up having to encode the data as strings and take care of any localisation issues yourself, e.g. making sure dates stored in France can be read in the UK and vice-versa. I haven't delved into Decimal storage too much but I think strings will need to be used instead of squeezing data into the Double data type and experiencing potential rounding errors.

Complete ADO.Net Support

The SQLite support is pretty comprehensive but misses out in one or two areas like the RowStatusUpdated event handling.

Error Handling

If you open a connection to a non-existent DB, SQLite seems to create an empty database at the specified path. This isn't a big issue but it means that the "DB doesn't exist" error turns into a "Table doesn't exist" error when you try to access a table in the DB.

Note: Since I wrote this post, I've found out that you can use connection strings to alter the default behaviour of creating an empty DB when the target DB can't be found. Read SQLite Connection Strings for more info.

Performance

On first impressions, data access with SQLite is very quick and certainly quicker than MS Access. However, if you have to start encoding and decoding data into strings (DateTime for example), I'm not sure that the performance wouldn't start to degrade.

Installation

With SQLite there is essentially nothing to install. All you need to do is copy the assembly into your application folder and you are up and running. SQLite wins hands down here.

Conclusions

None for now, except to say that SQLite looks very tempting as an Access replacement especially as it's so easy to deploy. I'll post more info as I gain more experience.