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.
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.