How to import the npgsql module?

Question

I need to connect to a PostgreSQL database from within PowerShell scripts. But how to install npgsql WITHOUT VisualStudio? There is no nuget!

So I tried to install the driver in the GAC using the newest MSI file (Npgsql-3.0.5.msi).

Using gacutil.exe shows, that it is installed:

Npgsql, Version=3.0.5.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7, processorArchitecture=MSIL

But PowerShell does not know anything about it! Get-Module -ListAvailable should show it but:

PS C:\WINDOWS\system32> (gmo -l n*).Name

NetAdapter  
NetConnection  
NetEventPacketCapture  
NetLbfo  
NetNat  
NetQos  
NetSecurity  
NetSwitchTeam  
NetTCPIP  
NetWNV  
NetworkConnectivityStatus  
NetworkLoadBalancingClusters  
NetworkTransition  
NFS  
PS C:\WINDOWS\system32> _

There is no module Npgsql!

I was searching for Npgsql.dll and it is there:

PS  C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Npgsql\v4.0_3.0.5.0__5d8b90d52f46fda7> dir

Verzeichnis: C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Npgsql\v4.0_3.0.5.0__5d8b90d52f46fda7


Mode                LastWriteTime     Length Name
----                -------------     ------ ----
-a---        08.01.2016     08:10     446464 Npgsql.dll


PS C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Npgsql\v4.0_3.0.5.0__5d8b90d52f46fda7> _

Because of the not recognised module npgsql my PowerShell code does not work:

function getDBConnection ($MyDBServer, $MyDBPort, $MyDatabase, $MyUid, $MyPwd) {
  $DBConnectionString = "Provider=PostgreSQL OLE DB Provider;Data Source=$MyDBServer;location=$MyDatabase;User ID=$MyUid;password=$MyPwd;timeout=1000;"
  $DBConn = New-Object System.Data.OleDb.OleDbConnection;
  $DBConn.ConnectionString = $DBConnectionString
  try {
    $DBConn.Open
  } catch {
    "Failed to connect! Error: "+ $_.Exception.Message
  }

  return $DBConn
}

function closeDBConnection ($DBConn) {
  $DBConn.Close
}

$query = "select * from test_table1"
$MyDBConnection = getDBConnection "dbserver" 5432 "databasename" "user" "pass"

try {
  $cmd = New-Object System.Data.OleDb.OleDbCommand($query, $MyDBConnection)
} catch {
  "Failed to create command object! Error: " + $_.Exception.Message
}
...

The $DBConn.Open doesn't fail, but the property ConnectionState remains Closed after the Open call.

The creation of an instance of OleDbCommand crashes with a german error message:

Failed to create command object! Error: Für "OleDbCommand" und die folgende Argumenteanzahl kann keine Überladung gefunden werden: "2".

It means that there are no overloaded methods with two parameters.

QUESTIONS:

• What do I need to install/configure so that Npgsql is visible in PowerShell?
• How do I load/import the Npgsql module in a PowerShell script or module?

Answer 1

When in doubt, read the documentation. Get-Module -ListAvailable only lists modules from the directories listed in $env:PSModulePath.

-ListAvailable

Gets all installed modules. Get-Module gets modules in paths listed in the PSModulePath environment variable. Without this parameter, Get-Module gets only the modules that are both listed in the PSModulePath environment variable, and that are loaded in the current session. ListAvailable does not return information about modules that are not found in the PSModulePath environment variable, even if those modules are loaded in the current session.

However, you can simply import any module with its full path in your script:

Import-Module 'C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Npgsql\v4.0_3.0.5.0__5d8b90d52f46fda7\Npgsql.dll'

Using wildcards in the path is allowed:

Import-Module 'C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Npgsql\*\Npgsql.dll'

If you want to be able to load the module by name create a subfolder Npgsql in one of the directories listed in $env:PSModulePath and put the DLL into that subfolder.