Choosing a Server-less Database for a Desktop Application

silverblatt

New member
Joined
Sep 28, 2021
Messages
4
Programming Experience
10+
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

Well-known member
Joined
Aug 25, 2013
Messages
158
Programming Experience
1-3
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,580
Location
Sydney, Australia
Programming Experience
10+
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,580
Location
Sydney, Australia
Programming Experience
10+
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.
 
Top Bottom