.Net simple database - what to use?

[ic2]

I would like the opinion of X#/C# developers. So far we have only used DBFCDX besides MySQL for some web projects as databases.

Now I want to add some data-functionality to an app (which could eventually become a W10 Universal app). DBFCDX is obviously not an option. What would you recommend to use? It should have a minimum impact on the users (like DBFCDX which only requires some DLL's,RDD's and DBF+FPT+CDX files to include in the installation). I mean: I don't want the user to have to install SQLExpress or Progress environments. PLus it should be fairly simple to implement in an app.

Dick

[wriedmann]

Hi Dick,

IMHO there is only one option: SQLite.

If there is some interest I can share a bit of code. I'm creating the database file if it does not exist, and adjust tables if needed.

Wolfgang

[Phil Hepburn]

Hi Dick,

Do you wish it to be accessed by more than one user at a time - concurrently?

Whatever your eventual choice do go for something which is driven by SQL code/script.

If my mind serves me well (some work ages back with Willie Moore) there is a silent install 'option' which allows SQL Express to be installed without user intervention easy.

I got 'SQLite' to install and run okay with both Metro apps and Windows phone, when there was no Microsoft alternative. Seemed ok.

Maybe we ought to research just what is on offer from Microsoft, as they did a simple light weight file based SQL alternative a while back. But I think this had the limit of a one PC / system access. All the query / statement code was T-SQL standard stuff.

I have been meaning to find out myself about this, so that I could add the relevant section to 'ClickStartXSharp'.

Let me know exactly what you want (as asked above) and I will see if things have changed on the MS side of things.

I intend to add a section on SQLite as well.

Cheers,
Phil.

[FFF]

Dick,
i see your concern re user action, but i'd seriously consider installing Postgres. Yes, there's an install, but it really is not harder than installing any other prog. There may be use for tweeking some config params, IF the data gets really bigger than one would expect on a "non dedicated server" app, but to replace usual CDX, let it run out of the box.

Just now I see i still have TWO installs running (V9.6 and V10.3)- simple didn't notice, that i forgot to uninstall the older version - so much to the "performance impact" on my machine .
And: No restrictions for user numbers, nor for size, tons of possibilities to access other data with the same interface (see "FDW"), no hidden costs and a very dedicated community.

Re SQLite - i use a banking app which holds it's data with this and i see on this very machine it has lousy performance with a data amount of about 17MB...

Karl

[Phil Hepburn]

Hi Karl, and all,

Yes, 'Postgres' is another option I intend to look more deeply into for X# use.

Isn't this what our friend in SA thinks so highly of ?

Performance is one important aspect I will look into.

If we are going to make a chance from DBF to SQL then the SQL solution we choose, MUST, be able to scale to something quite large and still perform.

One Mr Nell ;-0)

Must get some sleep,
Phil.

[wriedmann]

Hi Dick, Karl, Phil, all,

internally we have decided for PostgreSQL (yes, also Johan Nel recommended it). I'm using SQLite only for internal configuration data, as very simple storage.
My Door Configurator uses SQLite for both the configuration data and also for the configured doors, where every order with one or more dfferent doors is an own database file (that is zipped and uploaded to the company internet storage when saved).

SQLite needs no configuration. In fact the database is a simple file, and the entire engine is in a few dll's that are loaded from your program. Therefore it is not a multiuser database, but well suited as local single user simple database.

PostgreSQL (that we use also as backend for new PHP projects instead of MySQL) is a full SQL database that has many very interesting features, but it is not an installation-less database like file based ones (DBF, SQLite, MDB).

Syncfusion has published a free ebook "Postgres Succinctly": https://www.syncfusion.com/ebooks/postgres that gives a good oversight of some of the possibilities of this excellent database.

Wolfgang

[FFF]

...Therefore it is not a multiuser database, but well suited as local single user simple database.

Hi Wolfgang,
Having no experience with SQLite as developer: Does this really mean, ONE user, ONE access at a time? Not even multiple forms working on the same data-file?

Karl

[wriedmann]

Hi Karl,

no, you can access the same database file from more processes and more users.

Only during a write access the file will be locked shortly. So if you have more writes to the same database it could give some delays.

Initially, I had used the embedded version of Firebird for this, but apart the files were a lot bigger, it behaved not well with a multi-process access.

Wolfgang

[NickFriend]

My Door Configurator uses SQLite for both the configuration data and also for the configured doors, where every order with one or more dfferent doors is an own database file (that is zipped and uploaded to the company internet storage when saved).

Hi Wolfgang,

This is very interesting as I have to do something almost identical very soon with a quotation system that I'm redoing in .Net (C#) - the old VO version had the necessary DBFs, etc for each quote in a zipped and encrypted file to be able to send between users - I've been looking at how to get the same effect with the main DB now in MS SQL Server.

I'll probably come back and pester you for ideas and examples in a couple of months when I get to this part of the development

Nick

[wriedmann]

Hi Nick,

if you would like to treat database files like documents, SQLite may be the best solution.
A database can be very small as file, and when zipped, it decreases also.
In my Door Configurator, every order database contains only two tables, positions and variants, and is about 12kb in size. Zipping can reduce the size down to about 1kb.
To upload the single files I use a webservice that takes the files and registers some information about it in a local database (so I can reproduce when and who uploaded what order.

Wolfgang