2

I am working on a standalone C# Winforms app which interacts with a local SQL Server database. At a certain point, I migrated from Access to SQL Server LocalDB and created a SSDT project for versioning the database. I don't use EF at the moment in my project.

I have reached a stage where I need to deploy the app via an installer. I am using clickonce publish to generate it, but I am a bit confused on what is the best option for deploying the database.

These are all the solutions I have found so far:

  • Add SQL Server Express LocalDB Prerequisites and use SqlPackage to publish a .dacpac during installation and update. According to Microsoft (https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb?view=sql-server-ver16), localdb is a feature meant to be used during development. Is there a reason for that beside having to installing SQL server on user's OS?

  • Get rid of SSDT and use a service-based database .mdf file, then deploy it as an application file and use AttachDBFilename=<.mdf file> in the connection string. This will require to install SQL Server Express LocalDB anyway so, what is the improvement over the previous solution? Does .mdf support schema changes without data loss during updates?

  • Move to EF and rely on migration to create a new database, then, connect to it using, for instance, Sqlite. Is it possible to have both SSDT (for the development environment) and EF?

leoxama
  • 63
  • 1
  • 4

1 Answers1

1

"localdb is a feature meant to be used during development"

I don't believe that is strictly the case. You are perfectly fine to package that up and deploy it with your app.

Just be aware of its limitations as it's based on Express edition: primarily a 10GB memory limit and only local connections (not remote).

Alternatively, package up the full SQL Server Express edition. It is generally a more heavyweight installation, and cannot be segregated by user, but has more features.

To use DACPAC or MDF is a separate question from which version of SQL Server to use. Either works, although it's generally recommended to use DACPAC. For MDF you need to make sure you include the LDF also, and ensure the database is offline when copying it.

AttachDbFilename is a really bad idea, as mentioned elsewhere. It is not necessary even if you package an MDF, as you can just use CREATE DATABASE ... FOR ATTACH and use a normal connection string.

Using migrations through EF is a third option, and possibly the best in your case. It is entirely separate from using SQLite (below).

SQLite is another option. It will be embedded in your app, and is obviously local only. It uses your process memory, which may be good or bad.

Be aware of its limitations: there are some SQL features that are not supported and lanaguage differences. Triggers work very differently, and a lot of migrations involving ALTER TABLE do not work. The optimizer is significantly different (and arguably much more underpowered).

Charlieface
  • 52,284
  • 6
  • 19
  • 43