Npgsql: Unable to connect Visual Studio 2015 to PostgreSQL

Question

I'm new to PostgreSQL and trying to change my MVC 5 app from MSSQ to PostgreSQL. I've successfully installed PostgreSQL, created a database, schema and a test table. I also successfully installed NuGet packages EntityFramework6.Npgsql (v3.1.1) and Npgsql (v3.1.7). I verified my web.config and machine.config files and Npgsql references were already created. I'm using dapper to manage the data.

web.config:

<add name="PgSQL_Conn" providerName="Npgsql" connectionString="server=127.0.0.1;port=5432;userid=user;password=password;database=DB_Name" />

<providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, EntityFramework6.Npgsql" />
    </providers>

<system.data>
    <DbProviderFactories>
    <remove invariant="Npgsql" />
    <add name="Npgsql Data Provider" invariant="Npgsql" description="Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql" />
    <add name="dotConnect for PostgreSQL" invariant="Devart.Data.PostgreSql" description="Devart dotConnect for PostgreSQL" type="Devart.Data.PostgreSql.PgSqlProviderFactory, Devart.Data.PostgreSql, Version= 7.6.714.0, Culture=neutral, PublicKeyToken=09af7300eec23701" /></DbProviderFactories>
  </system.data>

machine.config:

    <system.data>
            <DbProviderFactories><add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/><add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/><add name="Npgsql Data Provider" invariant="Npgsql" description=".NET Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql, Version=3.1.6.0, Culture=neutral, PublicKeyToken=5D8B90D52F46FDA7"/></DbProviderFactories>
</system.data>

I'm using the following code to test the connection:

string text, query;

query = @"SELECT ""MOSB"".""GetText""() AS ""Text"";";

IDbConnection db = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings["PgSQL_Conn"].ConnectionString);
db.Open();

var result = db.Query(query);

text = result.First().Text;

When I debug the application, the following error shows up when the breakpoint hits db.Open();

An exception of type 'Npgsql.PostgresException' occurred in Npgsql.dll but was not handled in user code Additional information: External component has thrown an exception.

BTW, if I try to add a new Data Connection or Server from the Server Explorer tab, I don't see any PostgreSQL Provider in the list. If I run odbcad32.exe, two drivers appear.

I really don't know what else to do.

what line give you the error? and why so many double quotes "in that query? — Juan Carlos Oropeza
btw I just did the npgsql yesterday just following the tutorial no problem, but i only use EF — Juan Carlos Oropeza
Ben - I also used EF to be able to test the connection using VS Data Connections just know how to configure it. Juan - The line that gave me the error was when trying to open the connection and also have to use double quotes even in pgAdminIII query window. So I selected table's SELECT script from scripts option and that's how it was created. — Jose_Fig
Double quotes was used because PostgreSQL is case sensitive and I used uppercase letters in the schema and tables names. — Jose_Fig

Jose_Fig Jose_Fig · Accepted Answer · 2016-08-15T15:06:12

Problem solved! The issue was that my Windows user account wasn't able to edit machine.config file. After privileges were granted, file was successfully modified and now I can connect with Dapper and EF.

Npgsql: Unable to connect Visual Studio 2015 to PostgreSQL

1 Answers