Choosing a Server-less Database for a Desktop Application

[silverblatt]

I'm about to start coding a Windows Form application using Visual Studio 2019. The application will provide hobby collection management and a few other hobby related functions. Since it will be used mostly (if not entirely), by non-technically savvy adults without any IT infrastructure or support, the critical application requirements include:

1. Installation and configuration must be highly automated and idiot-proof.
2. On first launch, the application must automatically either create an initialized database, or copy one from the installation package.
3. The database platform must be server-less and maintenance-free, except for any utility functions that may be automated into the application itself (e.g., backup, compact/repair, etc.).

The actual data load will be pretty light. The main table will be tested with at least 10,000 records but the average user will probably only have a few hundred, or perhaps a thousand or two. There will be a handful of other tables that would seldom have more than a hundred records each, plus a dozen or so small lookup tables. The database just needs to support ordinary CRUD operations, as well as foreign key and NOT NULL constraints. Support for strong typing is highly desirable, but its absence MAY not be a deal-breaker.

I've been coding and supporting database applications for long enough that implementing all the intended features and meeting all the above requirements is stuff I've already done. My one uncertainty concerns the choice of database platform.

My first inclination is to use a Jet database, because I've already used that in my 9-5 job to implement a similar project that meets all the above requirements, as well as a number of other desktop projects. My only hesitation is a concern that future versions of Windows may break applications that use a deprecated technology like Jet. If including the appropriate data access type library in the compiled application (along with targeting a recent version of the Dot Net Framework) will assure that the application continues to work on any future version of Windows which supports the targeted Dot Net version, then I'll go with Jet.

However, if the future viability of an application using a Jet database is sketchy, I'll probably want to choose another technology. The other platform I read about a lot is SQLite. I'm familiar with its many virtues, but I'm also concerned about its quirks (including some that are claimed to be features rather than bugs). My main concern is the lack of strong typing in SQLite.

I'll be grateful for any insight anyone may have regarding Jet, SQLite or any other database platform that might be suitable for this project.

 

[divjoy]

Hi ,

IMHO id recommned using MS SQL Server Express, its fantastic, Its easy to install but then its relatively simple to use too. Also tons of support online too!

AS well as SQL you can create Stored Procedure, it requires a password to access by default so all very good! No corruption, and simple too once you get used to it!

 

[jmcilhinney]

Hi ,

IMHO id recommned using MS SQL Server Express, its fantastic, Its easy to install but then its relatively simple to use too. Also tons of support online too!

AS well as SQL you can create Stored Procedure, it requires a password to access by default so all very good! No corruption, and simple too once you get used to it!

That doesn't satisfy the requirement of being serverless. SQL Server Express is still a SQL Server instance. Even LocalDB is a SQL Server instance but, although simpler, is intended only for developers.

 

[jmcilhinney]

Probably your best bet for a file-based database is SQLite. Microsoft used to provide SQL Server CE but they halted development of that and now recommend SQLite. The SQLite ADO.NET provider can create the database on demand, which satisfies one of your requirements too. If you're only using the database from within your app then the ADO.NET provider will probably take care of any concerns like strong-typing.

 

[chrisjlocke1]

This is an old topic and the original author never replied back, but thought I'd chip in as many people like databases.

SQLite is a database library and to 'install' it, you just need three files - a common DLL, then two DLLs to cater for 32bit and 64bit applications. To use it in your application, you add an 'imports' line, then about four lines of code. It really is painless.

SQLite uses SQL so its 95% compatible with other database engines, like SQL Server, mySQL, etc.
Data-wise, it only uses four types - text, integers, real (or single to you and me) and binary 'blobs' (to store images, sound files, etc).
When you store your data, it puts it in one file. A single .db file. Its not like other database engines that spew out log files, cache files, etc. Its not a mammoth task to set up a database.
Its fully relational, so has foreign key support, etc. It has triggers, views and constraints. What, what and what? Triggers are small 'scripts' that fire when something happens - copy a database record from here to there if a record is deleted, for example. A view is a selection of data across one or more tables. Constraints means you can't put the value '999' in an 'age' column for example. Or set it to '-6'. Equally an email address field can't have three @ signs in it, etc.

I'll gladly do a tutorial if it interests anyone else but if you're learning VB or c#, then creating a database application can be a great and rewarding experience.

 

[SteveAtSynapse]

This is an old topic and the original author never replied back, but thought I'd chip in as many people like databases.

SQLite is a database library and to 'install' it, you just need three files - a common DLL, then two DLLs to cater for 32bit and 64bit applications. To use it in your application, you add an 'imports' line, then about four lines of code. It really is painless.

SQLite uses SQL so its 95% compatible with other database engines, like SQL Server, mySQL, etc.
Data-wise, it only uses four types - text, integers, real (or single to you and me) and binary 'blobs' (to store images, sound files, etc).
When you store your data, it puts it in one file. A single .db file. Its not like other database engines that spew out log files, cache files, etc. Its not a mammoth task to set up a database.
Its fully relational, so has foreign key support, etc. It has triggers, views and constraints. What, what and what? Triggers are small 'scripts' that fire when something happens - copy a database record from here to there if a record is deleted, for example. A view is a selection of data across one or more tables. Constraints means you can't put the value '999' in an 'age' column for example. Or set it to '-6'. Equally an email address field can't have three @ signs in it, etc.

I'll gladly do a tutorial if it interests anyone else but if you're learning VB or c#, then creating a database application can be a great and rewarding experience.

Apologies for replying to an old thread, but I'm starting a new project and, despite AGES of experience with SQL Server, I do NOT want to make that a requirement for my app. Exploring options for server-less data management. I would love to see your tutorial/notes if you have something prepared.

Thanks!
Steve

 

[chrisjlocke1]

Presume in vb.net? Sure, I'll knock up a screencast. SQLite is free and super fast and runs on anything. Just a couple of DLLs. If you need a tool to build an SQLite database, I recommend DB Browser for SQLite, and I'm a 'volunteer' for their support.
DB Browser for SQLite
Or their main Git repo, GitHub - sqlitebrowser/sqlitebrowser (where the issues forum is)
It may be 'less noisy' to open a discussion over there if you've any problems - depends on how touchy the guys here are regarding 'off topic' discussions...
Presume too you're developing on Windows? desktop? .Net 4.8 or 6?