SQL Database choices

This forum is meant for questions and discussions about the X# language and tools
User avatar
OhioJoe
Posts: 131
Joined: Wed Nov 22, 2017 12:51 pm
Location: United States

SQL Database choices

Post by OhioJoe »

Karl, I will give it a try. The instances I know of where there are 10 or more users all have managed servers, so a managed database server isn't too much extra work.
However, that still leaves demonstration software and single-user PCs. Like I said, I think the best solution is to write an app that works with all SQL varieties. Will let everyone know how it works. Project is in progress.
Joe Curran
Ohio USA
User avatar
wriedmann
Posts: 3774
Joined: Mon Nov 02, 2015 5:07 pm
Location: Italy

SQL Database choices

Post by wriedmann »

Hi Joe,
writing an application that works on all SQL varieties is doable, but a lot of work.
I have a set of libraries that work on the top of ADO.NET and gives me a single interface to work with all SQL dialects I use.
But the main difference is in the SQL language dialects itself: in some of them "like" works case insentive, in some not, so you need to use "ilike" (PostgreSQL). Then there is a difference how to limit the select to a subset of data (always a good idea!): some dialects use "select top xxx" (MS Sql), some "limit xxx" (MySQL).
These are only the IMHO most important one, but there are much, much more.
Wolfgang
Wolfgang Riedmann
Meran, South Tyrol, Italy
wolfgang@riedmann.it
https://www.riedmann.it - https://docs.xsharp.it
User avatar
OhioJoe
Posts: 131
Joined: Wed Nov 22, 2017 12:51 pm
Location: United States

SQL Database choices

Post by OhioJoe »

Sounds brilliant, Wolfgang. Thank you.

I once worked on something that was designed to work with both SQLite (ODBC) and MS SQL (SQL Server Express), which the app detected on the basis of the connection string (or something like that; I don't remember.) So I'll start there.

Of course my next question will be how to implement the SQLite DLLs so the user doesn't need to install the ODBC driver. But I'll try first before asking to be bailed out by you and others.

Thanks everyone. The general idea here is to use my experience as a real-world demonstration of how to (relatively easily) switch a large application from DBF to SQL.

From what I can tell, it's been 24 years since the DBF file format has been updated. Time to switch.
Joe Curran
Ohio USA
User avatar
wriedmann
Posts: 3774
Joined: Mon Nov 02, 2015 5:07 pm
Location: Italy

SQL Database choices

Post by wriedmann »

Hi Joe,
one of the beauties of the ADO.NET is that you do not need to install or configure anything - it is enough to copy the needed DLLs for the database.
For SQLite is is:
System.Data.SQLite.dll
x86SQLite.Interop.dll (for 32 bit systems)
x64SQLite.Interop.dll (for 64 bit systems)
For PosgreSQL it depends on the version of the client DLL, in my case
Npgsql.dll
System.Threading.Tasks.Extensions.dll
Newer versions have more dependencies.
For the MS SQL Server do you don't need to install anything because the client DLL is part of the .NET Framework.
The only database where I know a driver has to be installed even for ADO.NET is Oracle.
Wolfgang
Wolfgang Riedmann
Meran, South Tyrol, Italy
wolfgang@riedmann.it
https://www.riedmann.it - https://docs.xsharp.it
User avatar
TimothyS
Posts: 62
Joined: Thu Dec 15, 2016 3:39 pm
Location: Australia

SQL Database choices

Post by TimothyS »

Firebird. It is free, bulletproof, easy to install, got stored procedures etc etc. A 2MB app called Flamerobin will give full database admin. We have 100s of sites running it without problems.
User avatar
OhioJoe
Posts: 131
Joined: Wed Nov 22, 2017 12:51 pm
Location: United States

SQL Database choices

Post by OhioJoe »

Thank you, Timothy. Just looked up Firebird. Has a ADO.NET provider.
Joe Curran
Ohio USA
Post Reply