What is the best local-database solution for WPF applications?

Question

What is currently the best solution for WPF applications which are meant to be used on one computer where various users log into them and use them to get/save information locally? Here's how I see the options:

• MDF would seem to be the best choice since I assume you can lock it down quite well so that even though users can access the .mdf file, they still couldn't access the data in it except through the application itself. I assume the .MDF file would have to exist separately from the application so would deploy the .exe and the .mdf file. Oh, if you use an .MDF file, does the user need SQL Server installed to use it as is indicated in this stackoverflow question?

• I would think SDF wouldn't be recommended since it has quite a bit of limitations and used mainly for mobile storage.

• XML might be appropriate if security didn't matter, e.g. a small one-user application to save data that locally that he wouldn't secure anyway, and this file would be easily accessed by other applications/websites on the computer.

• SQLite might be a choice but from what I know the APIs for .NET for this database aren't that robust yet, are they?

• .mdb Access might be a choice if you needed users to also edit/view the data or generate reports with Access.

• there is also IsolatedStorage but I believe this has serious limitations, doesn't it, e.g. not able to access it from other applications?

• perhaps using a cloud service might be feasible and quite straight-forward nowadays, would have to investigate

Would appreciate any feedback or ideas.

Answer 1

I'd say SQLite. It's incredibly light-weight and easy to use, and the API is fine. You could always use DbLinq as ORM.

Answer 2

I would choose SQLite. You can get the ADO.NET Compatible driver here:

System.Data.SQLite

Answer 3

SQL Server Compact edition is also an option - http://www.microsoft.com/Sqlserver/2005/en/us/compact.aspx

Deployment is pretty easy using ClickOnce or by copying the engine dlls to the application directory.

Answer 4

SQL Server Compact edition is my choice because of the following benefits:

• A small runtime library that you can distribute with your app
• Compatible with Linq to Sql (search "SqlMetal.exe")
• Integrated design experience with Visual Studio 2008

It's a nice all rounder as a lightweight solution for a .NET app.

http://www.microsoft.com/Sqlserver/2008/en/us/compact.aspx

Answer 5

MDF does seem to be the most flexible solution but user need to have SQL server express installed to use it.

How do you plan on executing CRUD processes? If you are wanting to use Linq to SQL you are kinda limited offically it only supports SQL 2005, Sql 2008 and Sql Compact.

Also, does the application need to be disconnect, partially, fully connected to the internet? That may put some weight on your decision.

IMO, I would start with SQL Server Compact edition and if that was too limiting move to sql server express. (.MDF)