Databases for Windows Desktop Development: Welcome to the Jungle

Microsoft forgot a detail — or is knowingly telling us something.

I was a Microsoft enthusiast. And I’m a Windows desktop developer. You can judge, and stop reading here. Or you might be in the same boat and wondering why Microsoft decided to abandon the development for Windows.

I’m about to ship a desktop app. I’ve been far from swdev for a few years but I’ve written code for the previous thirty years. A dozen years with .NET.

So, for my new application, I decided on the excellent Windows Presentation Foundation. WinForms are gone, and UWP looks orphan and confined to Windows 10. I know people who buy Windows desktop apps (I’m one of them, of course). But I know nobody who buys them on Microsoft Store, or even checks that once in a while, and I fear that this is not going to get better anytime soon. I still keep my old Windows Phone on a shelf, as a warning.

Without saying, WPF goes with Entity Framework. Not necessarily, of course, but preferably.

As local db I started with an mdf (so, SQL Server). I told myself: “We’ll see what will be best, maybe SQL Compact or something. That’s not the problem.” Being a personal app, it was mandatory to have a single setup, all included, but that has never been a problem in my life.

The moment of the final touches came, and what seemed a “final touch” turned out to be a pain in the ass. I’ve been away from swdev for a while. Too much, maybe.

Microsoft forgot a detail

Or is knowingly telling us something. Remember that Nadella is a cloud man.

Everything is cloud nowadays — I get it — but a lot of business and productivity still runs offline. And Microsoft was rather good at it.

Surprise — my bad — SQL Server Express needs a separate setup in any case. I should have imagined.

I was betting on LocalDb, the simplified flavor of SQL Server Express. From Microsoft:

“ SQL Server Express LocalDB is a lightweight version of Express that has all of its programmability features, runs in user mode and has a fast, zero-configuration installation and a short list of prerequisites.”

User instances. Excellent. I was hoping to deliver also to users without admin rights.

But… You can get a msi file of LocalDb (so you can more or less have a silent install) but admin rights are required in any case for the installation (user mode, not user permissions…). Also, it cannot be password protected nor encrypted. It’s intended for being used by developers, but it’s not for developers. It’s for design time.

In a few words, LocalDb is not exactly the file-based database you could expect from Microsoft 26 years after the introduction of Access.

But the story doesn’t end here. Windows 7 and x86 are not supported by SQL Server Express 2017. Microsoft decided that half of my possible customers have an obsolete OS (Windows 7 still serve a good third of the global desktop/laptop users) and cannot be addressed.

Also, try to install a previous SQL Server Express — for Windows 7 support — and… it can’t open your mdf because you’ve created it with Visual Studio 2017 or the most recent SQL Server. No downgrade tool available for such a common problem, nor any option to create an “older” mdf. In the hope to read your mdf in Windows 7, you have to manually migrate your mdf to a previous version — using an “older” SQL Server tool — and renounce to use Visual Studio to manage it. Wow. They really should have had developers in mind, when designing this trap.

OK, let’s rollback. SQL Compact. Private install, no admin privileges required, password protection. Perfect.

Well, no. SQL Compact still lives in a dark corner of Microsoft servers but, as of February 2013, it’s been deprecated. You can go with it, at your risk, but Windows 8 and 10 are not in the specifications and zapping the external setup requires some workaround. Also, Visual Studio and SQL Server Management Studio clearly tell that SQL Compact is stone dead and forgotten. You’re on your own.

More rollback. Access? (Oh, my…)

Entity Framework does not support Access. Yes, you read it correctly. Microsoft does not support its own popular database with its own recommended ORM.

Implementations are there, for example JetEntityFrameworkProvider, but it supports only code first. And it’s from bubibubi. I’m sure Bubi is a great developer but I hoped in something more… official.

I once wrote an ORM for Access and .NET, but my new app is already all based on EF.

Using Linq to DataSet, with Access? Never used, but I’m sure it pays a significant toll to performance — among other issues, you have to entirely populate the Dataset — , and I’ve already to be careful on that side. It doesn’t seem a mainstream solution. I’m not ready to put in a lot of work for getting other surprises.

You can guess that I started sweating.

Okay, let’s step out of Microsoft territory. What’s the most renowned self-contained SQL database engine, coming with EF support? SQLite, of course.

But SQLite comes with a few limitations. You can’t drop a column, for example, or add a constraint. Imagine supporting full database copies through every schema change.

And password/encryption are not natively supported by SQLite. Of course, someone will tell you that a plugin is there for everything. With some limitations. But then again there’s the other plugin, which works differently, or fades to void after a couple of years…

MySQL? Oracle, the fiercest competitor of Microsoft. I’m sure they do their best, to support .NET against Java — and that I won’t have further surprises — but let me worry. Then, you have to find your way for a silent install, crossing fingers.

And you can go on, and on, and on… Sailing many seas and dreaming of endless discoveries and refactoring.

So?

So, we’re left with a lot of options and no solution.

Microsoft has two clear answers: SQL Express on the desktop (with a separate and bulky installer, no password, no encryption), or Azure (that means cloud and it’s not appropriate for too many desktop apps).

Microsoft forgot — or wants to forget — desktop apps and their need for integrated file-based databases. You can find many options but no straight and complete solution is there, clearly and fully supported by Microsoft now and in the long term.

What will I do?

First of all, I have to accept that I do not belong to this Cloud&Chaos era. I’ll likely struggle with that for the rest of my life.

Now what, with my desktop app?

Well, I plan to expand my product to teams. So, SQL Server is on my path, given that I prefer to stay in the Microsoft territory, if possible. Better to start with that tech since the beginning.

I’ll stay with an mdf file, migrating it to an older version (so, use an older SQL Server) and renouncing to manage it from Visual Studio.

Then I thought to deploy LocalDb silently, using the msiexec command line, embedded in my setup (given that the msi is provided):

msiexec /i c:\temp\SqlLocalDB2017.msi

But… Msi cannot run inside another msi (and I’m using a Visual Studio Installer project, which I prefer to other options for many reasons). So, I decided to run it at the first run of the application (else, I could have written a setup wrapper, or used a different setup builder). Of course, I have to

• detect possible previous SQL Server installations (by checking the registry and then files),
• decide which engine to install (LocalDb 2014 on Windows 7 — x86 or x64 — , else LocalDb 2017 — x64 — ),
• prompt the user (who will surely be happy of the extra setup, especially likely being a consumer),
• and likely distribute the SQL setup/s together with the application, or as an optional fat setup (else, a separate LocalDb download will be required).

No comment, Microsoft.

No encryption, and users can be added only by application logic or at SQL Server level.

It works, but I’m not happy. Also, it took me days, to make my decision, implement, and test.

Now, if you’ll excuse me, I have some backlog to work on, since I didn’t expect to waste so much time on the local database after 22 years from the first Visual Studio.