Hi,

We initially had a database-first project with SQL Server as the only database. We're now adding support for PostgreSQL and have added PostgreSql as another database under "Relational Model Data", and are treating that one as Model First. All the Entities are correct, the designer creates a Create script for Postgres correctly. Everything related to tables works well.

However, I'm now trying to work out what to do with the two stored procedures we have in SQL Server. In the left tree, if I Edit one of the Stored Procedure Calls to open its editor and select the Parameter Mappings tab I'm not sure how to set the name of the stored procedure for "PostgreSql Driver (Npgsql)". I clicked Create Mapping which enabled the magnifying glass icon. But there are no stored procedures in the Catalog Explorer under the PostgreSql (Npgsql) (obviously??), and I can't seem to type into the Target field as it's a dropdown. If I click the New Target button it offers to create a Table, not a Stored Procedure.

I'm not expecting the designer to actually create the code for the stored procedure in Postgres, I just want to add knowledge of it to the project so that the C# it generates can call the SP that I specify.

I'm using version 5.8.3.

Cheers, Paul

Stored procedures aren't created by the designer indeed. To have postgresql's functions (use functions, please, not stored procedures in postgresql) in your model, make sure the signature's are the same as the SQL Server's and in the Sync tab in the designer, set your schema for postgresql to 'Mixed'. This will tell the designer to pull all metadata other than tables from the target database. It'll produce 2 tasks now: one for reverse engineering metadata from postgresql (which will be all other elements except tables) and model first (for tables).

Thanks, and noted on the use of functions.

I did manage to get the procedure into the project by hand-editing the project file ;-) Doing it the correct way adds more complexity:

• I had to create a PostgreSql schema database on a shared server where so far we haven't needed one, only for SQL Server;
• I had to install Npgsql4.0.12 from an MSI to get the designer to be able to find the driver.

So at this point I think I might stick with my hack until we really need a Postgres schema database or make more use of functions.

pdonovan wrote:

Thanks, and noted on the use of functions.

I did manage to get the procedure into the project by hand-editing the project file ;-) Doing it the correct way adds more complexity:

• I had to create a PostgreSql schema database on a shared server where so far we haven't needed one, only for SQL Server;

The postgresql server can be on another machine tho, they don't need to be on the same server.

• I had to install Npgsql4.0.12 from an MSI to get the designer to be able to find the driver.

the designer ships with the npgsql driver that's needed for it. (It's in the pg driver folder). You had to install the older npgsql provider manually ?

Yes, I had to run the installer. Before that I got an exception at the point it tried to scan the Postgres database. I forget exactly, but it was something along the lines of a “not found” exception. I’ll try to reproduce it tomorrow.

Does the llblgen installer do anything to register the driver as being in a particular location? I copy the installed files into git, so I’m not running the designer from the original install location.

pdonovan wrote:

Yes, I had to run the installer. Before that I got an exception at the point it tried to scan the Postgres database. I forget exactly, but it was something along the lines of a “not found” exception. I’ll try to reproduce it tomorrow.

No, it's my fault, I assumed we added the npgsql provider to the driver in 5.8, but that was in 5.9. So in 5.8 you still have to install the MSI one. In v5.9 we reference a nuget package and use it directly so you don't need to install the msi one.

Does the llblgen installer do anything to register the driver as being in a particular location? I copy the installed files into git, so I’m not running the designer from the original install location.

In 5.8, it pulls the npgsql provider from the DbProviderFactories. This is a central construct where DbProviderFactory instances are registered (this is done in the machine.config file of netfx in C:\Windows\Microsoft.NET\Framework64\v4.0.30319\config.) Since a certain v5.x version of npgsql they introduced a breaking change so it didn't run anymore on netfx and they stopped releasing the msi installer because of it. The msi installer registers the npgsql dll in the machine.config file of .netfx so DbProviderFactories knows where it's located and any app requesting the factory can load it.

The dll is in the GAC, so it's pulled from there.

Otis wrote:

No, it's my fault, I assumed we added the npgsql provider to the driver in 5.8, but that was in 5.9. So in 5.8 you still have to install the MSI one. In v5.9 we reference a nuget package and use it directly so you don't need to install the msi one.

No problem. If I can get finance to approve a renewal at this time of year (my license expired in June) I'll upgrade to solve that particular issue.

Thanks for all your help.