Best option for a local/standalone database in a desktop project?
-
There is only one answer to the question: "What database does this standalone desktop app need to use", and that answer is SQLite. Any other option is going to require more user management than necessary. If the end-user ever has to ask the question "how to I manage the data for this application" or "how do I install the database for this application", then you have failed the end-user.
The Local DB version of Sql Express also avoids those problems. It can be installed by the application with no extra interaction with the user.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason? Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful? --Zachris Topelius
-
The Local DB version of Sql Express also avoids those problems. It can be installed by the application with no extra interaction with the user.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason? Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful? --Zachris Topelius
That's an extra step, with an extra point of failure, with extra dependencies, some of which will be updated.[1] Using SQLite removes all of that; can be compiled *into* the application, so the application doesn't have to install anything. [1] I once had an application break when Windows performed an update that changed one of the libraries SQL express used.
-
I'm working on a winforms desktop side-project in c#, it's for my internal project management, storing milestones and tasks, client notes, mcq quiz, etc., the idea is to open source it later. I'm considering sqlite for database as it's the typical choice for standalone projects, isn't it? Another option is ms-access which works great but the caveat is that I don't have ms-office installed, I use LibreOffice instead. Is it possible to create and mange access databases (*.mdb) purely with ADO.NET code or do we need MSO installed? If not, what other database would you suggest for this kind of project?
Have you considered rolling your own? It really depends on what your project needs are. If you are just storing information and not linking (making relational), then simple serialize/deserialize of JSON files can work very easily and would not have any outside dependencies. If you are needing relational, LocalDB (SQLExpress) would be a solid choice but does come with a size limit. I think its 10GB now. Used to be 4. I would not use Access.
-
I've used 4 databases for standalone desktop work: - Access: tends to get corrupted, you must remember to compact regularly - SqlCE: works ok, but may be missing some of the features you'd like; also doesn't do concurrency at all - Sqlite: powerful, small, embedded - a good choice - Firebird: [Firebird: The true open source database for Windows, Linux, Mac OS X and more](https://firebirdsql.org/) - this is honestly a really big hitter for the "standalone database" requirement. Yes, Firebird can run as a server, but it also can run standalone. Good performance, great SQL compliance, good drivers, etc. I've used 3 of these to maintain a local db which is sync'd up to a master when connectivity allows. Out of all of them, Firebird was hands-down the best, and became what the project eventually used, after starting with SqlCE (and finding concurrency issues) then trying Sqlite (and finding some other issue, which may now be resolved - I _think_ it didn't like multi-process access at the time?), then settling on Firebird, which worked brilliantly.
------------------------------------------------ If you say that getting the money is the most important thing You will spend your life completely wasting your time You will be doing things you don't like doing In order to go on living That is, to go on doing things you don't like doing Which is stupid. - Alan Watts https://www.youtube.com/watch?v=-gXTZM\_uPMY
-
I'm working on a winforms desktop side-project in c#, it's for my internal project management, storing milestones and tasks, client notes, mcq quiz, etc., the idea is to open source it later. I'm considering sqlite for database as it's the typical choice for standalone projects, isn't it? Another option is ms-access which works great but the caveat is that I don't have ms-office installed, I use LibreOffice instead. Is it possible to create and mange access databases (*.mdb) purely with ADO.NET code or do we need MSO installed? If not, what other database would you suggest for this kind of project?
There are only two options currently for an embedded, desktop database engine... SQLite, which is the easiest to use with a .NET provider, which is now fully supported by the SQLite Development Group. To manage your SQLite database, get a copy of the free database manager tool for SQLite, "DB Browser". It has everything you need to create and manage your databases. The Firebird Database Engine is your second option, using the embedded mode for the database engine setup. Unlike SQLite, Firebird Embedded provides everything that a standard RDBMS would offer such as stored-procedures. The best database manager for Firebird is IBExpert's Personal Edition, which is freely available. The interface is not as intuitive as "DB Browser" but it provides all of the necessary tools to manage your Firebird databases. Firebird is also a tough database engine to work with as the documentation is not nearly as well defined as for other engines. Nonetheless, it is a very powerful engine and if you want to move your application into a multi-user environment, then you can simply use Firebird Server with literally no change in your desktop database structures.
Steve Naidamast Sr. Software Engineer Black Falcon Software, Inc. blackfalconsoftware@outlook.com
-
Have you considered rolling your own? It really depends on what your project needs are. If you are just storing information and not linking (making relational), then simple serialize/deserialize of JSON files can work very easily and would not have any outside dependencies. If you are needing relational, LocalDB (SQLExpress) would be a solid choice but does come with a size limit. I think its 10GB now. Used to be 4. I would not use Access.
Yep, I considered lighter file formats for storage such as XML and JSON. What I've read so far suggests that appending specific data chunks to these files become heavier as the data increases. And considering that this method needs pulling of entire file to memory, it might get slower over time as the DB size approaches several hundred MBs or even a GB? In contrast, Sqlite driver just performs CRUD operations on the file, so performance might be better?
-
I'm working on a winforms desktop side-project in c#, it's for my internal project management, storing milestones and tasks, client notes, mcq quiz, etc., the idea is to open source it later. I'm considering sqlite for database as it's the typical choice for standalone projects, isn't it? Another option is ms-access which works great but the caveat is that I don't have ms-office installed, I use LibreOffice instead. Is it possible to create and mange access databases (*.mdb) purely with ADO.NET code or do we need MSO installed? If not, what other database would you suggest for this kind of project?
-
I'm working on a winforms desktop side-project in c#, it's for my internal project management, storing milestones and tasks, client notes, mcq quiz, etc., the idea is to open source it later. I'm considering sqlite for database as it's the typical choice for standalone projects, isn't it? Another option is ms-access which works great but the caveat is that I don't have ms-office installed, I use LibreOffice instead. Is it possible to create and mange access databases (*.mdb) purely with ADO.NET code or do we need MSO installed? If not, what other database would you suggest for this kind of project?
-
Sqlite is installed with Windows ... I don't know what would be simpler than that. It's also in the public domain.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
"Sqlite is installed with Windows" You mean it is included in a normal Windows installation? Which Windows? I didn't have it installed on my computers, as far as I know, on any version of Windows (3.1..10). If you mean that it 'can' be installed in Windows, that's something else. What can't?! Just curious...
-
I'm working on a winforms desktop side-project in c#, it's for my internal project management, storing milestones and tasks, client notes, mcq quiz, etc., the idea is to open source it later. I'm considering sqlite for database as it's the typical choice for standalone projects, isn't it? Another option is ms-access which works great but the caveat is that I don't have ms-office installed, I use LibreOffice instead. Is it possible to create and mange access databases (*.mdb) purely with ADO.NET code or do we need MSO installed? If not, what other database would you suggest for this kind of project?
if you are looking for portability without having to install software on the host machine and do not need to share the DB with another process, go with SQLite. it works fantastic, most languages have support baked in, and you can still pass structured queries. last I read I think there is a 4TB limit. if the *.mdb gives you something more that SQLite doesn't have, choose that. to be honest I haven't touched an Access db from code in a couple decades, so I'm not all sure what it offers currently. if you are not worried about portability use SQL express, or a no-SQL DB for document storage. both are good, it all depends on what you plan to do with the data.