fbpx
Welcome, Guest
Username: Password: Remember me

TOPIC: .Net simple database - what to use?

.Net simple database - what to use? 1 year 1 week ago #1

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

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #2

  • wriedmann
  • wriedmann's Avatar
  • Offline
  • Posts: 1404
  • Karma: 6
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
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #3

  • Phil Hepburn
  • Phil Hepburn's Avatar
  • Offline
  • Posts: 737
  • Karma: 7
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.

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #4

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

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #5

  • Phil Hepburn
  • Phil Hepburn's Avatar
  • Offline
  • Posts: 737
  • Karma: 7
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.

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #6

  • wriedmann
  • wriedmann's Avatar
  • Offline
  • Posts: 1404
  • Karma: 6
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
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #7

Wolfgang Riedmann wrote: ...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

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #8

  • wriedmann
  • wriedmann's Avatar
  • Offline
  • Posts: 1404
  • Karma: 6
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
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #9

Wolfgang Riedmann wrote: 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

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #10

  • wriedmann
  • wriedmann's Avatar
  • Offline
  • Posts: 1404
  • Karma: 6
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
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #11

I would like to mention Firebird SQL yet. I use it from many years in VO projects and now I plan use it in .Net projects. Firebird SQL is free and have everything what a database server should have: stored procedures, triggers, generators etc. It can be use as standalone server on Windows and Linux or as embedded in the application. You can find more information on www.firebirdsql.org .

Jacek Madej

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #12

Hello Nick,

I had the same problem, and put the data in an XML file.
The table is an ordinary read table with 2 fields.
I was pleasantly surprised how quickly the search function works in the XML.

Maybe it can help you.

regards,

Johan Kwaspen

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #13

  • Phil Hepburn
  • Phil Hepburn's Avatar
  • Offline
  • Posts: 737
  • Karma: 7
Hi guys - one and all,

Yes, this has been my quest for a number of years, since I started SQL in place of DBFs (10 years plus).

I have to say that using Microsoft's SQL engine has got much easier since those early days. Also they have made it much easier for standard "one-seat developer guys" to legally get and use free versions of their SQL Server.

If we can get a suitable Microsoft solution then we do have MANY extra benefits from their software tool integration - that is 'Management Studio' and now the built-in extra panes for SQL in Visual Studio. I have just attached to a SQL database in the clouds after many months absence and it was easy from VS.

I will keep things short for now by reminding all that even the full SQL Server approach is done using data files which can be attached, detached and moved around. (Hence the above with Azure.) Look at this image of some I have for general use :- Sorry attachments does not seem to be working (again) today ;-0((

When I get the attachments facility back for me here, I will send a couple of information images of what is possible even with MS technology - 'localDB runtime' is its name I believe.

And since I can post a link even with my attachment hands tied, here is something to checkout :-

www.infoq.com/news/2011/07/LocalDB

docs.microsoft.com/en-us/sql/database-en...2016-express-localdb

You may be interested to know that for the last three Cologne conferences I had to tackle this problem, and make solutions which would run on other guy's PC's / laptops, without me even seeing them. And provide these as conference session material.

Meanwhile, I will look up the way to set-up a 'silent' install of SQL express. Only a command line needs to be embedded in the app install, and bingo ! Now to find it !? I got it going following some good advice from Willie (M).

Best wishes,
Phil.
Wales, UK.
Attachments:

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #14

  • wriedmann
  • wriedmann's Avatar
  • Offline
  • Posts: 1404
  • Karma: 6
Hi Phil,

sometimes there are other problems too... like customers that have Linux servers (ok, should be no problem anymore).
And if you need more than 5 concurrent users, you have to pay not indifferent amounts (and it is really difficult to find a price for such an license).

Personally, I prefer to not need to use any SQLStudio or special tool to maintain databases - this should be done by my application itself (I have such code already for SQLite, Firebird and PostgreSQL).
The database managment tool I use (Navicat Premium Essentials) can manage all databases I need: Oracle, MS SQL, PostgreSQL, MySQL and SQLite.

Wolfgang
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #15

  • Phil Hepburn
  • Phil Hepburn's Avatar
  • Offline
  • Posts: 737
  • Karma: 7
Okay - have jumped over to Vivaldi and the attachments seems to work OK.

Here is how the database files used to look in MS (management studio) :-



And here is how the Server Explorer pane looks in my VS 2015 :-



And now to see what localdb looks like in VS :-



And with little effort (other than finding a password) here is my Olympics database in Azure :-



And here is some retrieved data - yes, its still in the Azure cloud after all this time ;-0)



PLEASE realise that I am not brow beating you guys about Microsoft etc., etc. - BUT - saying that if we can find a MS solution then we would be well advised to take and use it.

If you have not yet had the chance to use Entity Framework then you are missing out on a treat. EF6 and beyond make SQL life much easier - AND - we can do all our work as X# code and move away from T-SQL. Its a dream.

So I will look at trying to find a MS solution which is easy to install and use. We would also be well advised to try and make this solution multi user as well.

Hope this interests a few - AND - I have spent a HUGE effort tackling this problem over the years.

This does not make me an expert - BUT - very sympathetic to the cause !

Cheers,
Phil.

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #16

Hi,

PostgreSQL 10 rocks.

Support for streaming replication - awesome.
Support for logical replication - great.
Support for partitioning (big words but I have little use at this moment) - salivating.

FREE, LIBRE, NO COST.

Regards,

PS: Anyone have RDD replacement for PostgreSQL? We are now in the middle of re-writing our apps to PostgreSQL (MVC, cloud, browser-based) with X# and C# (and Xamarin for mobile devices) may take years but our VO apps on ADS still kicking like rock star.
--

Rene Pajaron

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #17

  • wriedmann
  • wriedmann's Avatar
  • Offline
  • Posts: 1404
  • Karma: 6
Hi Phil,

saying that if we can find a MS solution then we would be well advised to take and use it.


unfortunately I see it the way around. We depend on Microsoft for our client operating systems, we depend on Microsoft for our Office software.
Now we depend on Microsoft also for our development tool. That is more than enough, I think.
Therefore I try to take other solutions from other suppliers.
My wife has an iPhone, I use an Android device (Google Nexus), and for our cloud storage we use Amazon S3.

Wolfgang

P.S. I don't think Microsoft is bad or they should not earn money - IMHO they have done a very good job with Windows 10, and I continue to recommend Windows 10 and MS Office, so you cannot consider me a "Microsoft hater"
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #18

Rene,
all valid points - but dont forget to take a look at the ForeignDataWrapper (short FDW) concept. It allows you to "bind" almost any type of data "in" your PG instance - another PG server next door or anywhere on the world, MySQL, you name it, even CSV <g> (find a list at wiki.postgresql.org/wiki/Foreign_data_wrappers )and treat it as if it where all the same.
Plus the JSON(B)implementation, which let you add your unstructured data into the db, as if it where a NoSQL DB...
Sometimes is regret that being a smallish one man show, at max coding for own use, i don't have real incentive to dive into the possibilities this cool thing handles.:P

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #19

Hi Karl,

Been using PostgreSQL for awhile and still lots to learn and explore. Thanks for the tip, I will take a dive on this one. For now, I am really interested on anyone who have paid libs that mimic RDD for PostgreSQL to ease transition of our VO RDD apps to X# with Ado.NET. Still looking ways but re-write maybe an options and already doing in the periphery, but the core which is VO still growing is hard to decouple from ADS :(

Regards,

Rene

PS: I do love streaming replication, our clients in-premises PostgreSQL replicating to its cloud counterpart. But it requires a static IP, hence I am writing X# replication routines moving ADS data from in-prem to cloud, same way as in-prem ADS and PostgreSQL for client that do not have static IP. Simple, powerful. and FREE. Yes, json data types, unstructured making our CMS dev smooth.

Karl Faller wrote: Rene,
all valid points - but dont forget to take a look at the ForeignDataWrapper (short FDW) concept. It allows you to "bind" almost any type of data "in" your PG instance - another PG server next door or anywhere on the world, MySQL, you name it, even CSV <g> (find a list at wiki.postgresql.org/wiki/Foreign_data_wrappers )and treat it as if it where all the same.
Plus the JSON(B)implementation, which let you add your unstructured data into the db, as if it where a NoSQL DB...
Sometimes is regret that being a smallish one man show, at max coding for own use, i don't have real incentive to dive into the possibilities this cool thing handles.:P

--

Rene Pajaron

Please Log in or Create an account to join the conversation.

.Net simple database - what to use? 1 year 1 week ago #20

  • wriedmann
  • wriedmann's Avatar
  • Offline
  • Posts: 1404
  • Karma: 6
Hi Rene,

there was a guy from Australia, I think that some time ago wrote he had a RDD for PostgreSQL. I had asked more than one time, and of course I would have paid for such a tool, but haven't received any answer.

I know other people here in Italy is working on a VO RDD to MySQL.

Wolfgang
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

Last edit: by wriedmann.