Resolved postgreSQL

[mauede]

Dear Visual Studio Experts,

I am trying to access PostGreSQL from Visual Studio. I need to implement a C# script that allows users to access and modify some information I recorded in a PostGre database.
Since Visual Studio is a MicroSoft product, it sees all the MS databases in this local network but it cannot see PostgreSQL, even if it is installed locally on the workstation I am using. PostGre is running as I can access it through the low level terminal window commands ("psql").
In the Visual Studio form that is used to connect to a database I tried all possible names for the local PostGre server.
I tried "localhost", the IP-address, the workstation name. I emphasize that the workstation is networked as I can connect to it from home.
An IT gentleman came to my rescue. He said Visual Studio need a driver to access PostGre. He downloaded and installed the driver but when we tested the connection we just got a different error message.

I would greatly appreciate any suggestions. guidelines to access PostGre from Visual Studio on a Windows workstation.

Thank you in advance.

 

[Skydiver]

What did the "IT Gentleman" install on your machine?

What is the new error message that you got?

As an aside, in the C# world, code is called "code", not "scripts" (unless you are writing C# code for Unity).

 

[Program4Food]

in the C# world, code is called "code", not "scripts"

Thank you for acknowledging.

 

[mauede]

What did the "IT Gentleman" install on your machine?

What is the new error message that you got?

As an aside, in the C# world, code is called "code", not "scripts" (unless you are writing C# code for Unity).

Thank you for your reply.

I do not know the driver name that the IT gentlemen installed. It was downloaded from the internet.
PostGre is not a MicroSoft product. Visual Studio (VS), which is an MS product, sees all the MS databases but does not see Postgre, although it is installed and working through "psql" commands.

On Visual Studio I click on View --> Server Explorer
I can see the name of the workstation I am using "RTTQA-990-009"
I click on the icon "Connect to Database" . a window pops up that shows the databases VS sees:
"Microsoft Access Database File"
"Microsoft ODBC Data Source"
"Microsoft SQL Server"
"Microsoft SQL Server Database File"
"Oracle Database"
<other>

Clearly, VS cannot detect PostgreSQL.
VS expects me to fill out the field "Data provider" whose dropdown menu contains only the item
".NET Framework Data Provider for OLE DB" --> Continue
On the next window, I see "Microsoft Access Database File (OLE DB)"
I fill out the field "Database file name" with the name of the database I created through "psql"
that is TrialsDB. To log on to the database my
username is "Postgres"
My password is " Rttqa123"
The above logon details are accepted when I use "psql" terminal window.
When I click on "Test Connection the following error message pops up:
"The Microsoft.Jet.OLEDB4.0 provider cannot be used in Visual Studio because it is 32-bit only.
For more information please see Work with data in Visual Studio - Visual Studio (Windows)".
That was the only choice!

Any suggestions and/ advice to help me out of my misery is very welcome
Thank you in advance
Maura

 

[Skydiver]

Part of me feels that you were duped. What did you use to see before this "IT gentleman" installed something on your system?

Anyway, if you have VS2022, it is all 64-bit, and the built in JET OLEDB driver that you are trying to use is 32-bit only as the error says. You could try installing the newer Microsoft Access Database Engine 2016 that will give you 64-bit OLEDB drivers, but that still a crutch. You should have used:

Npgsql PostgreSQL Integration - Visual Studio Marketplace

Extension for Visual Studio - Allows you to connect to PostgreSQL from within Visual Studio via Server Explorer, create EF6 model from existing database, etc.

marketplace.visualstudio.com

Or

Connect PostgreSQL with Visual Studio 2019

ozanecare.com

 

[mauede]

Part of me feels that you were duped. What did you use to see before this "IT gentleman" installed something on your system?

Anyway, if you have VS2022, it is all 64-bit, and the built in JET OLEDB driver that you are trying to use is 32-bit only as the error says. You could try installing the newer Microsoft Access Database Engine 2016 that will give you 64-bit OLEDB drivers, but that still a crutch. You should have used:

Npgsql PostgreSQL Integration - Visual Studio Marketplace

Extension for Visual Studio - Allows you to connect to PostgreSQL from within Visual Studio via Server Explorer, create EF6 model from existing database, etc.

marketplace.visualstudio.com

Or

Connect PostgreSQL with Visual Studio 2019

ozanecare.com

Thank you. Yesterday we installed the following driver PostgreSQL ODBC Driver (32/64 bit) - Visual Studio Marketplace
It started to work.
I am trying to follow Dr Rex Cardan's video
that covers how to access Aria database through Visual Studio Aria is an Oncology Information System produced by Varian Medical Systems. It is a MicroSoft database. I found out that while the ADO.NET package works to interface VS to Aria database, it does not work to interface VS with PostgreSQL.
I will try with the package you suggested.
Thanks again
Regards

 

[Skydiver]

If you installed an ODBC driver for Postgre, then you should have selected "Microsoft ODBC Data Source" (not the OLEDB one that you described in post #4".

 

[mauede]

If you installed an ODBC driver for Postgre, then you should have selected "Microsoft ODBC Data Source" (not the OLEDB one that you described in post #4".

Thank you.
I followed your tip and it worked. Instead the ADO.NET package does not work with Postgre. It works with Aria database.
I started to use Npgsql. There seems to be no other way to access Postgre from Visual Studio with C#.
I feel somewhat uncomfortable because I could not find any good example of Npgsql usage.
Actually I found one that the IT gentleman found to be wrong. It was not working.
I wonder if someone knows of where to get some programming example for Npgsql.
It would be a great help.
Thanks a lot.

 

[JohnH]

Getting Started | Npgsql Documentation

www.npgsql.org

Npgsql aims to be fully ADO.NET-compatible, its API should feel almost identical to other .NET database drivers