Pexels

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 CompactPrivate 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…

#microsoft #windows #dotnet #software-development #database

Databases for Windows Desktop Development: Welcome to the Jungle
1.15 GEEK