Best way to use Microsoft SQL database in X#

[ic2]

Interesting posts.In order to make a product as widely maintainable as possible in the future I can imagine that one should invest time in replacing VO Gui stuff (like Windows) in Winforms (or WPF). This also means, correct me if I am wrong that:

1 bBrowser can no longer be used for browsers
2 XS2Ado can perhaps no longer be used for accessing SQL databases (not tested).

What I would like to know is:

1 Does anyone have some sample code of how to access a Microsoft SQL and PostgreSQL data from X#, to compare with how it looks with Vo2Ado?
2 What is an XGUI class?

Dick

[wriedmann]

Hi Dick,
Robert plans to create a XS2ADO version for the XGUI classes, and code to access a SQL Server database with ADO.NET is easy (I'm using my own interface libraries that expose both a GetDataTable() and an ExecuteStatement() method.
This is my method to get a DataTable (with exception handling removed):

Code: Select all

method GetDataTable( cSelect as string ) as DataTable
local oAdapter as SqlDataAdapter
local oDataSet as DataSet
local oDataTable as DataTable

oAdapter := SqlDataAdapter{ cSelect, self:Connection }
oDataSet := Dataset{}
oAdapter:Fill( oDataSet )
oDataTable := oDataSet:Tables[0]
oDataSet := null_object

return oDataTable

The XGUI classes are the new GUI classes that are compatible to the VO GUI classes (so a drop in should be possible), but are based on Windows Forms.

Wolfgang

[ic2]

Hello Wolfgang,

That looks easy enough indeed. If I do a quick search there seem to be commercial ado.net versions but from https://learn.microsoft.com/en-us/dotne ... ta/adonet/ I get the impression that it's part of .Net. What do you use? Can anyone tell how/where Vo2Ado (XS2Ado) differs from (an) Ado.net implementation?

Also I would be interested to learn more about the XGUI classes. Is there any additional information? E.g. does that mean that bBrowser and/or XS2Ado would work on a Winforms window? Or that you could mix VO Windows with Winforms windows and gradually replace Vo Style windows with Winforms windows (as converted from XPorter), for example?

Dick

[wriedmann]

Hi Dick,
most databases have their own ADO.NET driver like PostgreSQL or SQLite, but the ADO.NET driver for MS SQL Server is part of the .NET Framework.
XS2ADO has a different approach than ADO.NET, more like VO with server classes, whereas ADO.NET has datatables and requires manual updates. Or you can use some ORM tool to map your objects to a SQL database.
bBrowser currently is for the VO GUI classes only, but hopefully we will see it also for the XGUI classes too - but I don't think we will ever see it for Windows Forms. That would implicate a complete redesign, and I doubt the sales numbers will ever cover the development costs.

As I wrote: the XGUI classes are part of the subscriber version of X#, and are basically a drop in replacement for the VO GUI classes. They are stable beginning with X# 2.8, and the more people use them, the better they will become.
Immagine having a Windows Forms based application with all the things you know from the VO GUI classes: pictures, databinding, name based binding for buttons and methods.

Our company will use them for new developments where we need to be fast in development because you have the same productivity with them as with the VO GUI classes (Windows Forms development and WPF development is much slower as you know).

Wolfgang

[stecosta66]

Robert,
thanks for clarifying about Xs2Ado.
At this moment I'm in no rush, I'll start converting my largest VO application at the beginning of 2024, and I don't want relying on old VO Gui classes.
So looking forward at this new XGui classes.

Stefano

Stefano,
To make it use with the XGUI classes I would have to create an extra DLL that is compatible with these classes.
Since these classes are not officially ready yet, I have not done that.
But that makes sense.

Robert

[ic2]

Hello Wolfgang,

Your explanations are extremely useful although it raises some new questions every time, I hope I don't take too much of your time.

1 Do I interpret it correctly when I say that XGUI can be seen as standard Winforms with a 'layer' on top which allows the apply some VO style 'goodies' as the ones you mention?

2 About SQL: As I understand it now, you must program something different for all types of SQL servers and all types of interfaces to them:
- VO with OLE
- X# with ADS (we use SQL in our X# version of the e-mail program)
- Microsoft SQL with the standard . Net Ado.net
- PostgreSQL or SQLite with their own Ado.net version
- XS2Ado (it is also not clear to me whether this then fits every SQL database).

Basically I wonder how much should be changed in a program if for example we would start with XS2Ado, converting a VO2Ado driven VO program (using Microsoft SQL Server) when we would later decide to (e.g.) use PostgreSQL instead.

If there's a sample program somewhere for both situation, that would help to understand. I know Johan Nel has been working with PostgreSQL and I think he created samples years ago (Maybe https://www.xsharp.eu/itm-downloads?fol ... DataDriven but not sure if that is the right way to start as it's >5 yeas back and for Vulcan and also I am not sure if the samples use PostgreSQL .

Dick

[wriedmann]

Hi Dick,
first: the XGUI classes are GUI classes that have all the methods you know from the VO GUI classes, with all the callbacks, and the same parameter list.
That means you can take a simple VO GUI application and change the GUI library, and it works. That is what I have done for one of my samples in the Memmingen conference (but I have to admit that I knew what was not working, and had built my VO application using only supported classes like ListViews and "normal" control classes). I had tried to build a compatibility layer on top of Windows Forms myself several years ago, but have seen very early that this was a very complex thing to do. I then have looked at the sources of the XGUI classes, and have seen that Robert has uses a proxy class approach: he has built classes with the same API as the VO GUI classes, but instead of passing the calls to the Windows API, they are passed to Windows Forms controls and forms.

second: the way ADO.NET works is that every provider of ADO.NET has similar classes with a similar API so if you know one, it is relatively easy to understand them all. But since I have to support several SQL dialects in my applications (because many customers have some different databases I have to support), I have created DLLs that have exactly the same external interface to my application and are hiding the implementation details inside. Currently, I have such DLLs for PostgreSQL, MS SQL Server, MySQL, Oracle, SQLite and ADS.
I had a session about this last year at the Memmingen conference, but unfortunately I took a Corona infection two days before and had to hold my session through Zoom (I can say that I hope for the entire session to be able to stay in before the screen).
You can find the samples for this session here:
https://riedmann.it/download/SQL_Summit.zip
and the presentation (in German language) here:
https://riedmann.it/documents/SQL.pptx
But ADO.NET has a completely different approach as VO. XS2Ado instead has the VO approach using DataServers. I may be wrong, but I think that moving from XS2ADO to ADO.NET needs a different database layer, or at least to use your own layer. One approach would be to build an array server that takes the data from a ADO.NET datatable, and write eventual updates using the SQL Statement. (I have done a similar thing in VO using a http interface to a PHP layer on a MS SQL database).
Another possibility would the to use the XSQL classes. They are the VO SQL Classes, but instead of using ODBC, they are using ADO.NET as underlying database layer. Unfortunately I had not the time and the need to make some tests with them.
Wolfgang

[OhioJoe]

Another possibility would the to use the XSQL classes. They are the VO SQL Classes, but instead of using ODBC, they are using ADO.NET as underlying database layer.

XSQL classes? That's new to me.

Do you mean the SQL classes in XSharp ? If you look up SQLConnection{} in the help file it says ODBC.
Obviously I'm confused (as usual). If you're telling me that I can connect to and use an ADO.NET source using the XSharp classes, that would be a game-changer for me. I still have several DBF systems to convert.
Thank you, Wolfgang, if you can enlighten me.

[wriedmann]

Hi Joe,
if you have a FoX version of X# installed, then you have the following assemblies in your Redist folder:

other than these

The VO*.dll are the ported versions of the VO libraries, and the ones you are using if migrating an application from VO, or developing a new VO GUI application in X# (as we also do). Since these libraries rely on heavy Windows API calls, they are limited to x86 mode and AFAIK they don't handle Unicode.

The more interesting libraries are the XSharp.VO*.dll libraries: they are newly developed by the X# development team to offer a strongly typed and AnyCPU capable version of the API calls based version.
In my WPF applications I use the XSharp.VORDDClasses.dll instead of the VORDDClasses.dll to have also support for 64 bit applications - and I do that for some years now. Since it is very confusing to keep them distinct, I would call the XSharp.RDDClasses XRDDClasses.
Similarly, there are newly developed GUI Classes: XSharp.VOGUIClasses.dll instead of VOGUIClasses.dll. In my session about GUI choices for VO developers I called them XGUI classes. They are based not more on the Windows API, but on Windows Forms. Therefore they not only support AnyCPU mode, but also Unicode.
And the companion the these classes is XSharp.VOSQLClasses.dll, opposed to the VOSQLClasses.dll - called XSQL Classes to make it easier to understand. Instead of being based on ODBC like the VOSQLClasses.dll they are based on ADO.NET - and therefore they can be used in AnyCPU applications and have Unicode support like the other libraries of this series. But since I'm not using the VOSQLClasses.dll in my VO applications (I'm using VO2Ado or self written X# COM modules to access SQL data), I have not looked yet at these classes so I cannot say how they work.
Wolfgang

[OhioJoe]

Wolfgang,

Yesterday Chris Pyrgas talked me into leaving VO and I decided to take his advice. So if you grow weary of logging onto this forum every morning only to see another dumb question from me, blame it on our friend Chris.



I tested the following libraries in a VO-exported application:

XSharp.VOGUIClasses.dll
XSharp.VORDDClasses.dll
XSharp.VOSystemClasses.dll

I removed:

VOGUIClasses.dll
VORDDClasses.dll
VOSystemClasses.dll

The VOGUI Classes replacement left me with a mountain of compiler errors. I'll obviously need to start working those out.

The VORDD Classes left me with some compiler errors in DBServer subclasses, which I suppose I can resolve with help from this forum. But since I'm moving from DBF to SQL this will probably be a waste of time.

bBrowser threw an error that says it needs VOSystemClasses.dll

The next step is trying out XSharp.VOSQLClasses.dll

I'm sure you'll be hearing from me

As always I thank you for your help.