.netdatabasesqlitecompact-database

Best Option for embedded databases using .Net 4.0/4.5


Does anyone know the best option for embedded databases using .Net 4.0/4.5

The application is targeting Windows.

I have been using SQLite for my project for years. However, technology has changed quite a bit since .Net 2.0. I am upgrading my project from .Net 2.0 to 4.0 or 4.5.The problem with SQLite is finding a good NATIVE .Net managed library that does not need the unmanaged DLL. Additionally, I am forced to compile the application in X-86 or 64 depending on what SQLite library I am using. So the ANY CPU option will cause the application to crash.

I am converting over to Compact Server 4.0. Although I am getting it to work perfectly, I am starting to wonder if this is yet another abandoned Microsoft Technology.

My questions are:

  1. Is Compact Server CE 4.0 feasible for current technologies and for the future?
  2. Is SQLite a better option, and is there any real managed code that will work in ANY CPU?
  3. Is there better open source options out there that can be used and converted without a lot of work.

Solution

  • This question involves a lot of opinions so remember here I'll write my opinion (as Community Wiki). It's an answer instead of a comment just because it's too long.

    Is Compact Server CE 4.0 feasible for current technologies and for the future?

    I'd say it is and it won't be abandoned soon. Of course we can't say what will happen in 10 years (or even in 5 years) but it's a stable, well-known technology. The point is if it's good for you; does it satisfy all your requirements? Compare it with other technologies (also from Microsoft) searching for comparisons (also here on SO).

    Is SQLite a better option, and is there any real managed code that will work in ANY CPU?

    You still can compile your application for Any CPU keeping an unmanaged library until you deploy just one of them (check techniques used, for example, in SharpDX). About 32/64 bit applications I also suggest to read this blog post.

    Is there better open source options out there that can be used and converted without a lot of work.

    This isn't a question for SO but yes there are a plethora of different solutions. We can't suggest which one is better because it depends on your requirements. How much data? Do you need to support SQL queries? Is it performance critical? Do you have to support transactions? Concurrent access? Each solution has its own pro and cons (with a big enterprise level DB you may be able to do almost everything but you'll pay that in deployment complexity and cost).