DEV Community: Vico Biscotti

Finally Switching from Microsoft LocalDb to SQLite

Vico Biscotti — Thu, 28 Oct 2021 19:03:20 +0000

Why didn't I do that before? I know, it doesn't make sense.
Well, for my new Windows app I wanted to stay in the Microsoft stack - as usual - and plans were about scaling up to SQL Server. Only option left nowadays, now that SQL Compact is deprecated and Access is not supported by Entity Framework, is Microsoft SQL Server Express LocalDb.
Yes, it's that bad. Microsoft forgot file-based databases for Windows apps.
Now, LocalDb comes with plenty of drawbacks. Let's recap just two of them.
First one is that it needs a separate setup. It's a Microsoft database... it's intended for small apps... and yet... you cannot embed it in your setup from Visual Studio. You need to handle a separate install - trying not to annoy your customers - and handle different setups/versions for different Windows versions. Just madness.
Second, SQL Server databases have versions. SQL Server Management Studio automatically upgrades databases, so you need to use an old version and be extremely careful, if you want previous version of Local Db to work.
More on on this mess on Databases for Windows Desktop Development: Welcome to the Jungle.
Well, despite all the universe telling me "don't use LocalDb," I released the 1.0 with LocalDb.
Later, I developed a twin Android app, and used SQLite for it. So, late-genius in me said: "time to try it for the desktop too."
And so did I.
Well, having it working it's not been fun as it's usually "advertised" but it finally worked perfectly. No performance improvements, but a huge improvement in terms of schema changes (no mess with database versions), smaller setup (127 Mb to 7 Mb) and... unique setup.
I feel so... light, now!

From Designing to Publishing My Windows Application

Vico Biscotti — Thu, 02 Jul 2020 16:51:56 +0000

Behind the scenes of an indie Windows application.

The last time that I published a Windows application, it was ages ago. So, when a few months ago I decided to develop and publish my product I had to learn and decide everything again.
Here’s my journey from the idea to publishing. Not a step to step tutorial but a journal about decisions and pitfalls.

The idea

The idea is quite simple. A task manager, with Kanban view and everything, for Windows. Focused on personal needs, to begin, with teams in mind for the future.
Why yet another task manager, and why Windows?
Well, I’m a productivity geek, and you can bet that I’m never satisfied with the productivity tools. I’ve tried so many of them that at some point I lost faith in finding the chosen one.
I won’t annoy you about what I was searching for. Suffice to say that I wanted a hierarchical logic, compact but informative cards and views, easy integration with the file system, and more. Replace these requirements with the app you have in mind.
Also, while the cloud is a great place to be, I missed Windows. And Windows can be an alternative. You own the database, you (usually) don’t pay subscriptions, you don’t depend on the Internet connection or third-party servers, no web latency, and so on.
The Windows offer for task managers is not crowded. Okay, there are reasons for that, but I wanted my Windows app.
So, like thousands of indie projects start, mine started from my own needs.
And my being a Microsoft freak. Which indeed had a role in seeing opportunities in a Windows app.

Baby steps

At the beginning, a product was not in my mind, at least not in the foreground. I just wanted a custom tool and to experiment.
So, a couple of years ago I started playing with a prototype based on Windows Forms, connected with Evernote. Evernote was the database, and Windows Forms handled logic and UX. I could have an enhanced interface, but also use Evernote on the go.
Not good. Terribly slow and Evernote APIs are too limited for the purpose.
So, I soon switched to another project, still Windows Forms, but without Evernote. Or, better, with Evernote integration but based on the SQL Server Compact database.

That worked much better, and I actually started to use it.
But at some point, I hit two main limits.
Windows Forms too are slow and limited. While I’ve successfully used them in many projects in the past, the interface doesn’t scale well, and I now needed good reactivity with a dynamic layout. I was using Windows Forms just because I knew them well, and I didn’t have much experience with Windows Presentation Foundation (WPF).
Also, the indie developer in me had started to think about a product, and needed more. Too late to stop him.
So, I had to start from scratch and, instead of a custom tool, I had to design a product.

The beginning

A few months ago I restarted from scratch and had to decide where to go, according to the perspective of a product.
The requirements part was clear to me, and I also had a sufficient idea of what the MVP (Minimum Viable Product) should be.
So, I went straight to the next decision: the technology.
Windows Forms were not an option. I’ve loved them but, apart from their limits, they had been put on life support a long time ago.
UWP (Universal Windows Platform) didn’t (and doesn’t) seem that mature – and maybe even already dead in its pure form – apart from its being confined to Windows 10 and some limited multiplatform. Is it the future? It seems so, but it will take long, and Windows 7 users are still a big crowd. Meanwhile, I’ve still nightmares about Metro interface waving in and out my desktop and my old Windows Phones collecting dust.
UWP has the advantage of the Microsoft Store, and touch support. But… Microsoft Store doesn’t seem that popular. And, since my app is primarily intended for the desktop, I’ve seen very few people tapping on the touch screen of their laptop.
No, I’m not going to follow the UWP road until a significant change in the panorama, which I bet won’t happen anytime soon.
WPF is the Purgatory. But a pretty large and durable Purgatory, as it often happens, with Microsoft.
WPF is an excellent and modern technology. While Microsoft is already headed away, WPF is mature, and people will keep wanting traditional desktop apps for many, many years to come.
So, WPF.
WPF with .NET 4.6.1 and Entity Framework 6 (EF6), in C#. The prototypes were in Visual Basic, language that I’ve used since its origins, but it definitely was time to switch to C#.
Why not .NET Core? Because it’s too early. WPF is not supported by the current .NET Core 2.

Ready, set, go!

In September 2018 I started rewriting XPlan from scratch.
It took me a few weeks to adapt to the WPF approach and XAML but, once I did, it felt like Paradise. The full power of .NET with a declarative user interface design.

It’s been easier to adapt to EF, even if I’m still not a fan of it. Having designed my own ORM (object-relational mapping) .NET tool in the past, using EF makes me feel like I’m not in control of too many details, especially when something goes wrong. Anyway, once you get accustomed to a few idiosyncrasies, and to LINQ, it works like a charm.
Apart from Visual Studio and .NET, you can guess that a lot was on the table, even if the application had a relatively small scope. User interface, logic, licensing, upgrades, documentation, website, sales, etc.
I wished it could be possible to put the app on sale by the end of the year, but the target soon switched to the end of February 2019, which anyway meant to have the app ready by January, at least.

The surprise

Microsoft has a peculiar approach to surprises. They get you used to have state-of-art dev tools with whistles and bells, and when you’re off-guard, they place the surprise. Maybe it’s a coaching tactic to keep you alert.
When I was starting to be satisfied with my app and started to think about finalizing, the surprise popped up.
In previous decades, the default database for desktop apps had always usually been Access. SQL Server Compact (.sdf) was an option, which I never used.
With the prototypes of my app, seen the full support of Visual Studio to mdf files (SQL Server), I instantly switched to mdf. I wanted the option to scale up. Using the same file format for the desktop app and, in future, for a client-server setup was indeed an advantage.
Anyway. I thought that the database was a minor matter and that I could refine the choice later. SQL Compact or something, the local database had never been a problem.
How was I wrong.
My bad, mdf files cannot be managed by the executable. They need an engine anyway. And SQL Server (Express version included) is the engine. During the development period, a LocalDb instance (a vanilla version of SQL Server Express) was silently there.
It turned out that Microsoft forgot years ago that file-based databases are needed.
Access is not supported by Entity Framework. Yes, Microsoft does not support its own popular database with its own recommend ORM. There are workarounds, but they are… well, workarounds.
SQL Compact is (was) perfect for desktop apps. Private install, no admin privileges required, password protection. But… it’s stone dead. As of February 2013, it’s been deprecated.
So, we’re only left with SQL Server Express, which is not file-based and comes with a separate engine. But… Windows 7 and x86 are not supported by SQL Server Express 2017! Also, SQL Server needs a separate setup, with admin permissions, which is not exactly what you hope for customer experience. Not a unique setup, not a single sale, in my case.
Well, for the problem of compatibility with Windows 7 you can think that installing a previous version of SQL Server can solve but… it can’t use the database file you’ve designed because Visual Studio 2017 automatically upgrades the mdf file to its latest version and no backward compatibility nor tool is there. So, you are obliged to use an old SQL Server instance outside Visual Studio for the database design and be careful never to let Visual Studio upgrade it.
What is that, a trap for developers, to keep them and their customers on the latest version of everything?
For the separate setup issue there seemed to be hope in LocalDb, the only remaining option, once Access and SQL Compact are gone.
But LocalDb still needs admin rights. So, forget customers with no admin rights. And it’s still not file-based, so no embedded security.
Also, it comes with a msi but… you can’t run a msi from another msi, so you also can’t use a distribution project for your app.
Of course, there are options outside the Microsoft territory, but none of them convince me. For example, the popular SQLite doesn’t allow you to drop columns or add constraints (maybe schema upgrades became an old-style practice while I was away?).
Anyway, it’s pretty weird that you have everything you need for desktop development, in Visual Studio, and the most basic need of most desktop apps – a file-based database – is not there.
I spent days on that dilemma, for which Microsoft has no straightforward and complete solutions.
I decided to stay with SQL Server Express – LocalDb version – and accepted to renounce to customers without admin permissions, and to encryption, at least in the first period. Sigh.
Also, some ugly workarounds had to be put in place: detect possible previous SQL Server installations, detect which engine to install depending on the operating system and the hardware platform, prompt the user for the extra setup, distribute the extra setup. I included two LocalDb versions (more than 100Mb) in my app setup and then run the DB engine setup at the first run of the application, but it’s not an ideal scenario. I’ll refine it in future with automatic download options to limit the setup size, at least.
More about the problem and the solution here.
That surprise was hard take, especially for an app intended for customers, and with the little time at my disposal. But I had to take the blow and step ahead.
Microsoft, really a bad slip on that.

License management

There’s plenty of solutions for license management. But they usually cost a lot and need some kind of integration with your e-commerce and user management.
I didn’t want to spend much and to depend on third-party technologies for that.
Also, I didn’t want my customers to depend on a website to reinstall their program, sending installation codes to have a key back. I wanted to give them their key, valid for any reinstallation. It’s a risk, of course. But I can black-list pirates in the upgrades (and even change the logic with major upgrades), and an unpopular app is unlikely to be cracked. Reasonable risks for offering honest customers to own their license.
Of course, I had to implement the solution. I had some experience, but the whole thing took more than a couple of weeks. You have to design the key format (which has, of course, to be scrambled/encrypted), the key generation, the protection system (manage expiration or other limitations), and so on. Key will be provided by email. No website automation for now.

Upgrades

Another important item of the to-do list is automatic upgrades.
The concept can be simple. Periodically check your website (tip: HttpWebRequest.GetResponse().GetResponseStream()) for an upgrade (maybe just a txt file with the data of the latest release), prompt the user, download, install.
But I opted for the long road.
I have an xml file on the website with the history metadata. The app downloads that file and scans it checking for upgrades, distinguishing recommended or just optional upgrades. That way the app is also able to prompt the user with all the cumulative changes from the installed version to the possible upgrade. The user can accept, skip the upgrade, be prompted later, or decide to run the upgrade check later manually.
When an upgrade is there, the user can decide for the download. Once downloaded, on the restart, the app prompts for the install. If confirmed, the app runs a separate launcher (simple exe that will run the setup), and closes.

Trustworthiness issues

During a limited beta testing, another issue popped up. The antivirus.
It can block the download. I still have no statistics but it’s certainly a significant problem.
They’ll tell you that you just need to sign your app with a certificate, but it’s not that simple, and far from free. At the bare minimum, you have to sign the exe and the setup with a certificate and add a timestamp (to avoid the effects of the certificate expiry).
So, I postponed solving this issue – unless it becomes blocking – but I guess I have to address that soon.
A similar but minor issue is the firewall warning the user that the app is contacting the website (for the upgrades).

Sales

Finally, there’s the sales thing.
I decided for a one-time license, to limit complexity. A single payment with all minor future upgrades included.
For e-commerce, a lot of possible platforms are already there, of course at some cost, but I wanted to start with my own platform to have full control.
Since the first version of the website is a WordPress setup, I opted for the popular and mature WooCommerce.
For payments, there are various possibilities, but I opted to begin with PayPal, handled by WooCommerce. I had already successfully used PayPal in the past. I can add other payment channels later.
For the regulatory and accounting matters you certainly need some advice, but the mandatory aspects (privacy certainly being the one which needs most attention) usually boils down to a few things, especially if what you have in your hands to start with is a microbusiness or no business at all. An annoying part, but maybe you don’t need to overcomplicate at the beginning.

Ready!

By the beginning of March 2019, XPlan was on sale.

Not all of what I had in mind as a minimum was there, but a good and solid part was. The cornerstone to build on is there.
However, I’ve done little promotion before, except a landing page and a newsletter that very few subscribed, so nobody knows about the app. Everything has to be done, and I guess that link building is step one (yes, this article is part of it).
Anyway, selling is a different story. And you can bet that for an indie application it’s the hardest part.

Databases for Windows Desktop Development: Welcome to the Jungle

Vico Biscotti — Wed, 01 Jul 2020 18:17:55 +0000

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.