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?
I use SQL Server Compact.
-
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?
SQL Express is what I use. I used to use Access, then switched to SQL Server, for multi-user applications. for single user desktop, I would recommend SQL Express. Not only is it easy to use, with SSMS, but it helps you to learn SQL, database design, T-SQL, and Stored Procedures, all of which you need if you ever have to use full blown SQL Server.
-
Sqlite is great, though personally I actually prefer the heavyweight champ SQL Express -- I have enough side projects in C# that it warrants using SQL Express with multiple DB's, and I don't have to worry about any nuances with Sqlite, though frankly the frameworks and libraries (Linq2Sql, EF, Dapper, FluentMigrator, etc) pretty much handle the nuances for me.
Latest Article:
Create a Digital Ocean Droplet for .NET Core Web API with a real SSL Certificate on a DomainWhen I was using SQLite in a UWP app a few years ago, the main pain point was just that the style of database scheme project we normally used wasn't supported at all; and the closest match was apparently a non-idiomatic way to use the database and buggy as a result. MS did fix the bug I found eventually (Achievement Unlocked), but not until a few years after the project had ended. :doh:
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
-
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 want to keep things as simple as possible: LiteDB
-
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.
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)
-
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
