Configuring ASP.NET Development Server to connect to a postgres database

Question

I have an existing ASP.NET app which is configured to connect to a MS SQL Server database and I am trying to modify it to use a postgres database instead.

I would like to avoid modifying the C# code, if possible.

I expected that the following changes would be sufficient:

1. Add a reference to a suitable postgres driver, in this case NauckIT.PostgreSQLProvider.dll
2. Add a provider tag to specify the driver to use, in this case:

   <connectionStrings>
     <add name="default" connectionString="Server=127.0.0.1;Port=5432;User Id=finnw;Password=XXX;Database=default;Pooling=false"
       providerName="Npgsql" />

   </connectionStrings>

But the providerName tag seems to have no effect (although it does not in itself generate an error.) Instead I still see this error message in the browser:

[ArgumentException: Keyword not supported: 'port'.]
   System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) +4907604
   System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules) +98
   System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +55

It looks as though it is still trying to use the MS SQL Server driver.

How do I persuade it not to?

I am using ASP.NET Development Server, not IIS. Initially I thought this might be the problem but this forum post suggests otherwise.

Answer 1

The rather wonderful http://www.connectionstrings.com/ gives examples of all kinds of connection strings, and yours looks to be OK. Unfortunately your code is using SqlClient which is the SqlServer only database access in .Net. You're going to have to hack some code ...

Answer 2

You need to change from using the classes in System.Data.SqlClient to using their analogs in the System.Data.Odbc namespace. You'll also need to get an ODBC driver, install it, and set up an ODBC connection on the server. Then you can use this named connection to access your database.

Answer 3

• You need to download and configure your PostgreSQL ADO.Net connection driver, there are a few one to do this, the most used is Npgsql (http://pgfoundry.org/projects/npgsql/), download the driver first. (Download Npgsql2 that's the new one!)

• Make sure you are using the correct DbProvider as Npgsql2

• Make sure you have the Npgsql2 provider in your references (anyway you can add this to your web.config):

<system.web> <compilation> <assemblies> <add assembly="Npgsql2, Version=1.0.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"/> </assemblies> </compilation> </system.web>

• Make sure you have the Npgsql2 data provider installed as expected: (you can add this to your web.config file):

<system.data> <DbProviderFactories> <add name="Npgsql2 Data Provider" invariant="Npgsql2" description=".Net Framework Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql2, Version=1.0.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" /> </DbProviderFactories> </system.data>

• Add your connection string as usual:

<connectionStrings> <add name="Blog" connectionString="SERVER=localhost;Database=blogstarterkit;User name=test;Password=test" providerName="Npgsql2"/> <!-- provider invariant name, configured above --> </connectionStrings>

BTW, StackOverflow editor is really bad to format XML code... :S