|
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?
|
|
|
|
|
Read the stuff at the top of the page: the Lounge is not for coding questions. Post it here instead: Ask a Question[^]
Ignoring the rules and annoying people you want free help from is not a good idea ...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Hmm, that didn't strike me as a programming question, rather more of a "what tool should I use" question.
|
|
|
|
|
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.
|
|
|
|
|
When 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. 
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
|
|
|
|
|
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)
|
|
|
|
|
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 ....
|
|
|
|
|
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.
|
|
|
|
|
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 use SQL Server Compact.
|
|
|
|
|
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.
|
|
|
|
|
If you want to keep things as simple as possible: LiteDB
modified 11-Nov-22 12:25pm.
|
|
|
|
|
LiteDB looks good. Serverless is not required for small projects, but it sure makes installation a lot easier.
|
|
|
|
|
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
|
|
|
|
|
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'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 - 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.
|
|
|
|
|
I second Firebird, works great local standalone. Fast, stored procedures, free. FlameRobin is a free admin tool.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
