Which local database fits my situation?

A

11

3

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?

Antedate answered 27/1, 2009 at 5:54 Comment(0)

T

9

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

Turnstile answered 27/1, 2009 at 5:56 Comment(0)

H

8

Have you thought of FireBird SQL?

Hodges answered 27/1, 2009 at 6:48 Comment(1)

+1 on that. Have a look at the SO questions tagged with firebird, there are a few that deal specifically with using Firebird as an embedded database. You can integrate it into your own setup, and it should definitely be stable enough for your needs. – Incredible 27/1, 2009 at 7:36

T

3

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.

Low Footprint - The engine is only 700 KB or less.
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.
Durable - Their testing suite overs most of the code and has been proven to work on many commercial applications such as the iPhone
Fast - There's very little overhead with this engine.
Current - The .NET project is very up to date with the latest .NET 3.5 technology microsoft is bringing up.
Portable - Can use it on embedded systems or desktop.

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

Tatro answered 27/1, 2009 at 7:45 Comment(0)

O

3

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

Osteoclast answered 27/1, 2009 at 21:18 Comment(0)

W

2

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

Waiver answered 19/3, 2009 at 17:47 Comment(0)

B

1

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.

Blackpool answered 27/1, 2009 at 5:58 Comment(0)

B

1

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

Beauvoir answered 27/1, 2009 at 7:9 Comment(0)

C

1

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.

Catechize answered 27/1, 2009 at 7:24 Comment(2)

Can you elaborate on how to run SQL Server locally without having to install it? – Antedate 27/1, 2009 at 16:3

I did some more checking and found out that you need sql express to use it. You can then start a "user instance" of a database that connects directly to a database file. So not quite what you were after. I'll modify my answer. – Catechize 27/1, 2009 at 20:4

H

1

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

Holston answered 27/1, 2009 at 8:55 Comment(0)

A

1

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

Aberration answered 27/1, 2009 at 12:44 Comment(0)

C

1

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.

Corin answered 27/1, 2009 at 12:45 Comment(0)

Recommended topics

Hot tags