Database functionality with WPF app: SQLite, SQL CE, other?

Question

I want to extend a WPF application with database functionality. Which database engine would you suggest and why? SQLite, SQL CE, other?

Answer 1

Depending on the applications use, I would recommend using SQL Lite because it doesn't require you to install any other software (SQL CE or Express, etc. usually would require a separate install).

A list of the most important benefits for SQL Lite from the provider link at the bottom of this post:

SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine. Features include:

• Zero-configuration - no setup or administration needed.
• Implements most of SQL92. (Features not supported)
• A complete database is stored in a single disk file.
• Database files can be freely shared between machines with different byte orders.
• Supports databases up to 2 terabytes (241 bytes) in size.
• Small code footprint: less than 30K lines of C code, less than 250KB code space (gcc on i486)
• Faster than popular client/server database engines for most common operations.
• Simple, easy to use API.
• Self-contained: no external dependencies.
• Sources are in the public domain. Use for any purpose.

Since you're using WPF I can assume you're using at least .NET 3.0. I would then recommend going to .NET 3.5 SP1 (sames size as .NET 3.5 but includes a bunch of performance improvements) which includes LINQ.

When using SQLite, however, you would want to use the following SQLite Provider which should provide LINQ support: An open source ADO.NET provider for the SQLite database engine

Answer 2

I would agree that SQLite is the way to go. Subsonic 2.1 now includes SQLite support as well.

Answer 3

SQLite is a really nice product although I miss features from PostgreSQL. There are other, especially non-SQL, databases you may to consider like Berkeley DB.

/Allan

Answer 4

Just to throw out a differing opinion, we've been using SQL Compact Edition for the last year and have been generally satisfied with. The configuration is cake and it behaves very similar to a regular MS SQL database. There are things missing, like triggers and stored procedures, but SQL 3.5 CE has virtually everything else we'd need. It's about 2Mb of .dlls to install. It offers database encryption, transactions, and supports VS's typed dataset designer (3.1 had some problems, but CE 3.5 is great!).

Answer 5

I used SQL Compact Edition with my WPF app and I'm happy with my decision. Everything just works (since WPF and SQLCE are both MS they play nicely together), and the installation of the runtime is small enough and smooth enough for my needs. I created and modified the database through visual studio.

Answer 6

SQL CE DLLs can be packaged into your own application and need not require a separate install. But MS provides a default install package, if you dont want to learn about setup ...etc.

More ot it, SQL CE supports private deployment.

Answer 7

Check belows link. it should help.

http://ayende.com/Blog/archive/2008/01/22/SQLite-vs.-SQL-CE.aspx

http://dotnetperls.com/sqlite-versus-sqlce

http://www.sqlite.org/speed.html