I want an easy embedded database for any relatively small database size (< 100MB, but generally < 20MB) and maximum 1-2 concurrent customers/connections normally. It has to have the ability to safeguard/secure it's data having a login.
Which database is the best for the task?
- Supports in-memory database
- Good performance
- Real XCopy installation (or real embedding)
- Free and platform independent
- Has C# porting -> http://code.google.com/p/csharp-sqlite/
- C API (low productive)
- Poor SQL support
- Is quite complicated with large data set
- No direct support of information file encryption (extensions available)
- Good query analyzer (regular queries could be distributed to SQL Server)
- Lots of information, good documentation (short learning time)
- Support OLE DB (C/C++) and ADO.Internet
- Native support of information file encryption
- Support SQL Replication (really awesome feature)
- No XCopy installation (could hinder another install)
- Well.. that's all?
For the scenario, certainly SQL CE, is not it?
File encryption isn't free in SQLite.
But it's super easy to embed using its non-invasive footprint and straightforward file copy install.
SQLite provider for .Internet is robust (a minimum of in single-user mode--I have never produced a multiple-concurrent-authors application using SQLite and most likely never would because SQLite utilizes a file-securing plan not really a record-securing plan and all sorts of my applications generally have just one user or many customers. So you'd need to spend some time handling concurrency issues.
Also, SQLite has quite good SQL support also it handles large datasets well.
So you have to assign important weight to every of the needs. How important is ease-of-install? How important is concurrency? How important is file encryption?
They ought to both handle the task as well.
If you are acquainted with SQL Server development, SQL Server Compact Edition will most likely provide you with the least quantity of head aches. The provider, queries, and connection string is going to be familiar.
Both are great IMO. But, whenever you can, I attempt to stay with MS stuff when employed in .Internet. This assumes the finances are in position to do this... consider you are searching at SQL Server CE and Sqlite both are free. Both will offer the EF however i would think that Sql Server CE may have better support in many areas.
Also, have you thought about searching at db4o to have an embedded solution? Pretty nice NoSQL solution... also it's free.