33

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

Alan Le
  • 8,683
  • 7
  • 36
  • 31

6 Answers6

23

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 (2^41 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

antiduh
  • 11,853
  • 4
  • 43
  • 66
John Rennemeyer
  • 455
  • 9
  • 17
  • 42
    SQL Server Compact Edition does not require a separate install. Like SQL Lite, that database is a single file. – Brad Leach Sep 16 '08 at 03:29
  • 5
    According to performance tests(http://www.codeproject.com/Articles/220131/Benchmarking-the-performance-of-embedded-DB-for-Ne) SQL Lite is not faster at all! You should remove the bullet from the list to not mislead people. – Alexander Vasilyev Feb 25 '15 at 15:45
  • ` An open source ADO.NET provider for the SQLite database engine` link, doesn't exists anymore – Ghasem Aug 12 '15 at 03:43
  • @BradLeach Of course `SqlCE` needs a separate install. It's just fast and easy. – Ghasem Aug 12 '15 at 03:45
  • 7
    SQL Server Compact Edition does **not** require an install as it can be delivered privately with your application. We use this approach to keep it out of the Add/Remove Programs list. http://erikej.blogspot.com/2011/02/using-sql-server-compact-40-with.html – Matt Davis Jan 14 '17 at 00:18
  • Performance from 2017: http://www.diericx.net/post/benchmark-embedded-dotnet-databases/ – p__d Apr 23 '18 at 09:15
  • Also do note that, as of July 13th 2021, MS SQL CE is not supported anymore. The last version was 4.0 and no new versions are planned. https://learn.microsoft.com/en-us/lifecycle/products/microsoft-sql-server-compact-40 – SolarBear Sep 13 '21 at 15:38
19

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.

AndrewS
  • 3,450
  • 1
  • 21
  • 26
9

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!).

Bob King
  • 25,372
  • 6
  • 54
  • 66
3

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.

2

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

Allan Wind
  • 23,068
  • 5
  • 28
  • 38
0

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

Paul Dolphin
  • 758
  • 2
  • 8
  • 18