Best option for a local/standalone database in a desktop 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?
had this issue a couple years ago. Just needed a decent DB that ran locally. I ended up using MariaDB. It just works. The SQL statements can sometimes be abit different but otherwise everything just worked. I ended up using DBeaver as my IDE for the SQL and it was pretty darn good. and because I am like you a person who likes LibreOffice it all seemed to just go together and worked fine on windows I might add.
To err is human to really elephant it up you need a computer
-
AFAIK the .MDB format is deprecated - .ACCDB is the new format. I get your point about a single manageable file (although for any reasonably sized data set you are going to have to split the Access DB into multiple files to overcome the 2GB limit). Personally I would just use LocalDB (SQL Server Express)
CHill60 wrote:
AFAIK the .MDB format is deprecated - .ACCDB is the new format.
You are correct -- "MDB" is long deprecated. I have the bad habit of typing "MDB" when I should by using "ACCDB". It may be that I'm just too lazy to type 2 extra characters ....
-
For small projects, requiring a separate application be installed to manage data is a problem with moving from PC to PC. Even if you plan to use it only on 1 PC, eventually you'll need to migrate to a new machine. Having the data in 1 or more discrete files makes it easier. Since you're planning to open-source it, making the program and its data portable is critical. I use MS Access for several small projects, as it fits the above criteria. Once you have a MDB built, all MDB interactions can be handled in code. Sadly, there are version problems between MS Office and the MS Access libraries, so getting that ironed out has been a PITA for me. But since you don't have MS Office, it shouldn't be a problem for you, but it could be for recipients. NOTE: If you don't have MS Access, you'll need to write a short program that constructs an MDB. SQL Lite and SQL Express need to have tools installed, so those have to be installed and configured (probably no worse than MS Access). I don't know if the DB can be handled as a file, and included in an installer. I lack the knowledge to know if this is a problem. LibreOffice? I assume LO Base has libraries that can be included in an installer, so this is a good option, as you can build the DBs in LO Base and can edit them as needed. XML is also a good choice. The files are totally portable and learning to use it is not hard, although you'll probably want a separate file to represent each table. I'm currently reconsidering my use of MS Access in a couple of personal projects, so answering your question has given me food for thought.
BryanFazekas wrote:
SQL Lite and SQL Express need to have tools installed, so those have to be installed and configured (probably no worse than MS Access). I don't know if the DB can be handled as a file, and included in an installer. I lack the knowledge to know if this is a problem.
I don't know about sql express but used sqlite extensively. In terms of "tools", a single command line program called sqlite3.exe is typically used though "proper" IDEs like DBeaver come with their own libraries for creating and managing sqlite files. For .NET and C# specifically, System.data.sqlite is the standard way I believe, they provide both downloadable .NET DLLs and nuget packages. Through this library, you can create and manage sqlite data files through code. Regarding working with mdb without MS-Access, I'm thinking DAO which I believe is an ancient library but still ships with most recent windows systems?
-
If you want to keep things as simple as possible: LiteDB
LiteDB looks good. Serverless is not required for small projects, but it sure makes installation a lot easier.
-
BryanFazekas wrote:
SQL Lite and SQL Express need to have tools installed, so those have to be installed and configured (probably no worse than MS Access). I don't know if the DB can be handled as a file, and included in an installer. I lack the knowledge to know if this is a problem.
I don't know about sql express but used sqlite extensively. In terms of "tools", a single command line program called sqlite3.exe is typically used though "proper" IDEs like DBeaver come with their own libraries for creating and managing sqlite files. For .NET and C# specifically, System.data.sqlite is the standard way I believe, they provide both downloadable .NET DLLs and nuget packages. Through this library, you can create and manage sqlite data files through code. Regarding working with mdb without MS-Access, I'm thinking DAO which I believe is an ancient library but still ships with most recent windows systems?
Prahlad Yeri wrote:
I don't know about sql express but used sqlite extensively. In terms of "tools", a single command line program called sqlite3.exe is typically used though "proper" IDEs like DBeaver come with their own libraries for creating and managing sqlite files.
Thanks for the info. I will consider that in the future. I suspect the problem with VS/Access interactions is the MS Access team and the VS team don't consider working with each other, as MS pushes SQL Server (in all its varieties) as the DB solution. MS Access appears to be considered a stand-alone product that is self-contained.
Prahlad Yeri wrote:
Regarding working with mdb without MS-Access, I'm thinking DAO which I believe is an ancient library but still ships with most recent windows systems?
Internally MS Access uses AccessBasic, which hasn't changed substantially in 20+ years. That is probably DAO. In C# I use System.Data, specifically the OleDB provider. This replaced the earlier DAO and ADO (ODBC) libraries, AFAIK.
-
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?
As mentioned above the latest MS-Access db is .accdb. I think it's licensed as an Office product now and not be used only as a db. At my last job we had a product that used Access as the db. MS let us slide on the licensing because we also did a few reports off the db but warned us we were tiptoeing on the edge. The application was a testing, scoring, grading application and not very Office-y. When I left they were in the throes of moving it to SQL Server. I'm not entirely sure of the license restrictions (this was ~5 years ago) but it may be worth looking into if you decide to go the Access route.
-
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'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?
Whatever your choice, consider a separate Data Access Layer (DAL). That way when you open-source it, users can just point it to whatever their database of choice is. They might to tweak some bits in the DAL but that just improves the product overall and widens its appeal.
Telegraph marker posts ... nothing to do with IT Phasmid email discussion group ... also nothing to do with IT Beekeeping and honey site ... still nothing to do with IT
-
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 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.
-
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?
do you need relational database, or scale of a database engine? if gonna be a few hounded entries, a JSON file, read at runtime, and parsed into plain old objects, might be just as affective. All your linq/queries still work. If not need encrypting of data, then can easily edit said file in a text editor, and transport, backup of file same as any file. but then im the weird type that made a comics list in web page/javascript
-
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 have this agenda application with the same characteristics. I am using SQL Server CE which has the same behaviour as SQLite has but being a Microsoft monolithic database, works very close to SQL Server (Thew big Brother) and it is very easy to migrate your data to the last without much effort.
-
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?
If you end up needing a fuller set of SQL features, it is now possible to run MariaDB (MySql equivalent) in a local portable mode, with a session initiated/terminated with a discretionary port, from a shellexecute or simillar. MariaDB.education - Install portable[^]
Paul
-
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?
First thing: don’t use Access for anything more than prototyping and local, personal projects. Second, if you do use Access, check their licensing terms. Lastly, if you expect to have multiple users using one central database, don’t use Access. Don’t misundertake me: Access is a great tool for what it is, but it’s definitely not suited for some things.
Time is the differentiation of eternity devised by man to measure the passage of human events. - Manly P. Hall Mark Just another cog in the wheel
-
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?
