Which local database fits my situation?

Question

I will be building a set of applications. One of these apps is unattended application (written in VB6) which will write data it receives from various sources to a local database. All the other applications (will be written in VS 2008/c# 3.0) will read this data for mostly reporting reasons.

I don't want SQL Server/MySql/Sybase or even its express editions, because distributing it is a problem (e.g. requires separate install, etc...). I am looking for a local database that has drivers for both VB6 (so probably OLEDB, or ODBC if I have to settle for it) and ADO.NET. It would also be nice, if the database had support for ORMs, like NHibernate or at least 1-to-1 mappers, like Subsonic, but that's not a deal breaker. But my main requirement is that the database should be massively solid, so that I don't have to waste time fiddling with it, recovering lost data, etc...

I have experience with MS Access from back in the day and don't have too many good recollections (nor was it bad either). I've heard of SQLite, SQL Server Compact Edition, VistaDB, etc... but have zero experience with them and I don't know anyone who has actually used it.

What's out there that best fits my situation?

Answer 1

SQLite might just fit the bill. Make sure you check out all the available wrappers as well.

Answer 2

I don't have a lot of experience with SQLite either but I have used it a little. It is used in a LOT of situations and applications. There really are 100s of little DBs out there but I'd recommend SQLite as a starting point at least.

Answer 3

Have you thought of FireBird SQL?

Answer 4

I can vouch for VistaDB. Another database you might want to look at is Blackfish

Answer 5

It is possible to use SQL Server as a local database connecting directly to a database file. But this requires that you have sql server express installed.

Look at the default setup of ASP.Net with a Membership Provider. It will create a local file based database for the website.

Using this approach you might be ok since you can use all your familiar tools when developing.

EDIT:
I just noticed the embedded tag. If you want to run the app on Windows CE devices you could opt for SQL Server Compact. It doesn't support stored procedures AFAIK but it does support most other features of SQL Server.

Answer 6

I'd recommend SQLite unless the database. I have experience using it and I was in the same shoes you were. The System.Data.Sqlite project is really nice and they support ADO.net 2.0 and all that fanciness. They are currently working on getting full Linq support on the wrapper as well. I recommend it for the following reasons.

1. Low Footprint - The engine is only 700 KB or less.
2. Scalable - It can handle fairly large databases. Order of GBs. Anything larger than this probably will require a server based database engine with LOTS of RAM.
3. Durable - Their testing suite overs most of the code and has been proven to work on many commercial applications such as the iPhone
4. Fast - There's very little overhead with this engine.
5. Current - The .NET project is very up to date with the latest .NET 3.5 technology microsoft is bringing up.
6. Portable - Can use it on embedded systems or desktop.

Check the System.Data.Sqlite project here http://sqlite.phxsoftware.com/

Answer 7

SQLite is very low impact and VERY well written. It is perfect for a local client database

Answer 8

SQLite as others have mentioned with the SQLite.net ADO.net provider, for easy .NET access to it.

Answer 9

I have used ScimoreDB, and wrote my appender for log4net, for my application. Have had mixed results and spent a lot of time trying to get through the documentation to get things to work as per needs.

I am moving towards SqlLite and there was a link floating around about using SubSonic with SqlLite.

Answer 10

Check out Advantage Database Server. It has a silent install option, has a very small footprint and is easy to maintain. It has a lot of client support: ODBC, OLE DB, .net data provider, jdbc, dbi, php, etc. Royalty free local engine (just a dll that runs in-process with your application), inexpensive client/server, SQL and direct navigational table access.

The DevZone

Answer 11

SQL Server Compact Edition is in-proc, so there is nothing separate to install.