Sending VFP data to .NET Blazor project 2

[Joey.MM]

Greetings!

I was wondering if someone could provide some assistance/clarity for a project that I'm doing.

I'm currently working on a Blazor project, for those of you that are unaware, Blazor is web framework for building interactive web applications using C# and HTML on .NET. I want to use Blazor as a Progressive Web Application so that users can access their data on the go. The thing here is that the data is stored in a VFP database and Blazor can't seem to access such data. Here's what I did step by step with some of the code :

- On Visual Studio 2019, I established an ODBC data connection to my VFP project database (.DBC) locally. I can retrieve and visualize the data on my tables just fine!

- After this, I created a Class Library with:

​

1. A class to get my ODBC connection string

​

2. A class that uses SQL syntax to pull specific fields from a .DBF table

​

3. And a data Model (get:, set to pass along said .dbf fields

- In my Blazor project I created a new web page to display such data using C# and HTML on a list.

- When I run the Web Application and I try to go into my Sales web page to show the data... I get the following error :

PlatformNotSupportedException: System.Data.ODBC is not supported on this platform.

After many hours of research I come to find out that .NET 5 does not support ODBC connections anymore :

https://github.com/dotnet/runtime/issues/898

Could there be any workaround for this issue? Should I migrate my VFP database to another one (mySQL, SQL Server, etc.)? I was thinking of creating an API and use it as an intermediary between my VFP data and Blazor. What do you guys think? Any help or insight would be appreciated.

 

[Chris Miller]

From the VFP perspective the only direct data connectivity options you have are ODBC and OleDB, the MS ODBC driver (discontinued since VFP7, so last ODBC driver only supports VFP6 DBF/DBC features and language) or the MS OleDB provider (replacing the ODBC driver since VFP7, available in versions up to VFP9 SP2 or in short the latest VFP, and by the way including all backward compatibility).

From the discussion you link to I get the same essence. The "platform" does not support ODBC anymore.

With exceptions, as it seems. Some say it does, but there you get into the trouble what is actually meant with "platform". It's such a overused term. It's obviously all on the Windows platform, no problem on that level. Then .NET has different versions and is divided into standard and core, and there's also the CPU bitness x86 vs x64 or "Any".

I'm not even sure after reading the discussion what it is, that doesn't support ODBC anymore. My best guess is that it's actually a difference between Core and Standard .NET libraries more than an issue with versions 4,5 or 6. Core is, well, the core of the .NET framework, only and is a limited subset. As far as I googled a bit about Blazor there seem to be embedded in ASP.NET Core, so the Core framework family is your "plattform". But in itself .NET Core is able to make use of System.Data.ODBC and System.Data.OleDB

I'd look for help in the .NET community about how to configure a Blazor project to enable it using OdbcConnection or OleDbConnection, then the question for alternatives becomes obsolte.

Otherwise you'd need to go other routes, and that's questionable, as it's simpler solved if you can migrate data to better database platforms. You could develop a REST service with VFP or a COM component consumable in .NET. But that then will require VFP code. You could perhaps rather develop a C# component that does the data access and can be reached by HTTP requests.

The other aspect I see after googling a bit about Blazor is, that it offers to run purely client side in the form of C# ASP.NET Core code translated to WebAssembly. Well, and in that case the limitations you face are not .NET limitations but browser limitations, I think. Browsers are sandboxed about file access on their host system. You can mainly only access cookies and cached files, not any local files. Which also is the root of not being able to use client side ODBC driver DLLs available, sinply as they are also outside of the browser sandbox. The browser itself is, of course, a desktop app, which also enables it to act with the local file system in itself, storing Downloads on harddrive, for example. But anything running within a browser is sandboxed. So anything needing VFP data would need to communicate with something else able to make the data connectivity. A Blazor Server component, for example. I haven't heard anything about ODBC in Javasccript, Typescript or Webassembly. Anything needing data in a web app will get it from a server, the most direct way is via REST API, which means it is not direct data access anyway.

I'm not knowing your situation, but if your database is local you would need to go as far as running IIS and hosting the Blazor Server component on the same system as the Blazor Webapp component running in the browser. A typical setup for web developers running a webserver on his development workstation. Then your client machine is the server, too. But that doesn't make this mobile.

Or, perhaps as the finishing thought, if your data is local, you can have the finest technology to allow a mobile application, that doesn't make your data mobile anyway, it always will need some server side component the data can be served from and clients with internet connectivity as minimum mobile basis.

Chriss

PS:

I should perhaps add that even with ODBC available, the VFP ODBC connectivity differs from most other ODBC drivers in not allowing to access data remotely stored, you can at best access data that's in the same LAN as the client using the ODBC driver. Same for the OleDb Provider. In comparison, other database servers are reachable at some socket on a service specific port. By default you can also only reach say an MSSQL on premise server in the LAN of a company, but in general you can make it available on the internet. Mainly, as there is a serverside service that handles incoming requests.

In VFP the only server component is is a file server, the ODBC driver or OleDB provider is not forwarding ODBC commands to a service running somewhere, it includes the VFP runtime and SQL engine to execute queries on DBFs itself, it already is "the server" And it can only reach local files or files on a share in a LAN or WAN by mapped drives or UNC paths \\servername\sharename\..., the only data source types you can specify are a dbc filename or a folder of dbfs. The whole communication is via file system protocols anyway.

So even if you can make use of ODBC your range of data access is limited to local or LAN access of files. Your best bet is constructing a REST service that does access DBFs and use it as a REST service within the Blazor app. And as already concluded, that requires internet connected clients at least and is perhaps feasible in contrasst to data migration, if you're having a hard time migrating all other software using that data to use a new backend. It could be done in C#, too, or in any language that could use the VFP9 OleDb Provider, ideally, or the ODBC driver and it then should run on the same server as the DBFs are located. Well, just like you seldom separate a database server engine from the actual databases, the actual SQL engine should have fast access to data, so it should be local to it.

 

[mplaza]

Depending on your needs a solution is a vfp microservice over http;

Check this sample I shared last year on foxite:

https://www.foxite.com/archives/rest-api-demo-0000490335.htm

and here you can see how to put your service behind IIS configuring it as reverse proxy:

https://www.foxite.com/archives/rest-api-with-ssl-certificate-with-vfp-0000491875.htm

Marco Plaza

https://www.github.com/nfoxdev

 

[Joey.MM]

For Chris :

I would like to thank you for taking the time to reply and your insight.

First of all, I agree 100% with you. It is indeed somewhat a tedious topic when it comes to the term "platform" being overused nowadays. I think we can both agree that there shouldn't have to be any way of verifying platform compatibility when it comes to these kind of projects considering the fact that A) I'm working on a Windows machine and B) I'm not implementing any Cross-Platform libraries or components on these projects (other than the default ASP.NET Core multiplatform operability settings). The word "Platform" appears to have replaced what was then known as operating system and taken over the word "Framework".

As you've mentioned, .NET Core should be able to make use of System.Data.ODBC or System.Data.OleDb so I created a simple REST API to pull the data from my ODBC connection and the API layer is running on ISS Express. Here's what I did step by step :

I referenced the ODBC VFP driver on my appsettings.json :

I created a class to catch the ODBC connection string and send the SQL query :

The controller to set the GET request parameters :

Once I run the API and call the GET records I get the following error :

The same error I got when using Blazor. I tried to check the System.Data.ODBC package and I realized that I was running the latest version which is 7.0.0 :

Here's where it gets interesting...If I try to downgrade to another version I now get another error:
"System.Data.Odbc.OdbcException: 'ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

I went to my ODBC Data Source Administrator just to verify and everything checks out. I have the VFP drivers installed, otherwise I wouldn't have been able to establish a connection to Visual Studio in the first place.

The idea of making a REST service that is able to pull data from a local VFP database file and sending it to my Blazor App is great but unfortunately it seems like it might not work. What's funny is that back in 2020 I was actually able to pull VFP data into a REST API using a ODBC connection. I went back to that project and it no longer works as it did back then. Why? I honestly can't find a concrete answer.

Seems like migrating databases might be the best bet? What do you think?

I apologize for the long reply, but I wanted to be as explicit as possible.

Thank you for your attention once again, Chris

 

[Joey.MM]

For Marco :

Nice! I see you are working directly with sockets in the examples you linked. This could be an alternative, I'll make sure to check the examples and run some tests. I'll get back to you in case I get stuck or have any issues.

Thanks!

 

[Chris Miller]

Hi Joey,

I see you specified a connectionstring, I don't see you using it/passing it into the constructor of VFPDataAccess, though. If you'd do, the error would not be "no default driver specified", you specify usage of the "Microsoft Visual FoxPro Driver". The passage "Data source name not found" of the error message also points into the direction that the connection string used would be interprreted as a DSN name. I think this would happen, if the empty string was used.

Well, even if you get this working, needing to downgrade the .NET framework version to get ODBC support is making this a "legacy" situtation within .NET from the getgo. It may not be relevant, as using DBFs is legacy in itself, and you try to support this legacy situation, there is no need to upgrade. But then you're stuck on old versions in both worlds.

As I see (atlopes) Marco Plaza gave you a way to establish a VFP microservice, that encapsulates the legacy aspect fully on the VFP side and I'd go that route rather than trying to get it going with older .NET framework versions. It makes me wonder why MS decided to discontinue ODBC support. It's still a modern data connectivity standard, if not the most modern. And even Linq, EF etc. use this layer anyway, AFAIK.

Chriss

 

[Chris Miller]

One more hint: In further discussions I also found support missing for ODBC due to project types not being WinForms but UWP. I guess as Blazor is a type of web app this also is about the general UI decisions that exclude parts of the .NET framework, even if System.Data.ODBC is surely not related to the UI choice.

I also found 2 mentions of Microsoft.Windows.Compatibility Pack, which you can get via the NuGet package manager. You could give that one last try to get OdbcConnection working within your project.

Chriss

 

[Joey.MM]

Hey, Chris

I added the code in the VFPDataAccess constructor to pass on the connection string explicitly and the line "services.AddSingleton<VfpDataAccess>(new VfpDataAccess(Configuration.GetConnectionString("VfpConnection")));" in the Startup.cs file. I downloaded the Microsoft Compatibility pack and I get the same error.

Indeed, downgrading to older .NET versions seems unreliable and it is something I'd rather not do. I think the solution is establishing a VFP microservice the way Marco suggested. This seems like the best option for now.

I also wonder why MS would do such a thing, ODBC is pretty flexible. But oh well, one can only guess.

Appreciate your time and attention on this topic, Chris

Have a nice day!
Joey

 

[Chris Miller]

You're welcome, I got interested in this.

The only further idea that's off track of usual data connectivity would be COM interop. It would still mean programming in VFP to return JSON or XML from methods of an olepublic class built as a COM automation server.

I just mention it for sake of completeness. I guess the IIS webserver involvement already offers the advantage of security and scalability of parallel http requests, though it doesn't sound like you need massive parallelism anyway.

Chriss

 

[Rajesh Karunakaran]

Dear Joey.MM,

Why don't you use OLEDB. I think, you can try as below:

Add following.
using System.Data.OleDb;
(You will have to add a reference to this in your project)

and use OleDbConnection, OleDbCommand, OleDbDataReader etc as per your need.

Also, you will have to change 'Platform Target' from Any CPU to x86.
When you build, select 'x86' in place of 'Any CPU' (the combo boxes in your IDE at top where you see 'Debug'/'Release' and 'Any CPU' etc)

Then, it should work I hope.

Rajesh