C# SqlConnection Exception: Keyword not Supported 'Port'

Question

I have looked at a lot of posts with the same topic. But neither helped me. I want to use Postgresql with a CrossPlatform-App. Later I will use a server in the cloud but for trying I think it's easier to use a local database. Therefore I have installed database itself and the "npgsql" Package with "NuGet", like recommended from the Postgres guys. After getting always the same error while connecting, I reduced the code to a console-App with really ten lines of code.

namespace SqlTester_ConsolApp
{
    class Program
    {
        static void Main(string[] args)
        {
            string MyConnection = "Server=127.0.0.1;Port=5432;Database=sample;UserId=Postgresql;Password=zzzzzzzzz;";

            try
            {
                SqlConnection Connection = new SqlConnection(MyConnection);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("---------------------------------------------------------------------");
                System.Diagnostics.Debug.WriteLine(ex.ToString());
            }
            System.Diagnostics.Debug.WriteLine("Ready");
        }
    }
}

The new SqlConnection always terminates with and System.ArgumentException telling Keyword not supported 'port'. The cause is clear, the .Net Framework tries to use SQL Server instead of passing my String to the npgsql driver. Therefore it does not make sense to cut the "port" from the string.

My question is how can I change the string parsing to the Progresql Provider? Actively I did nothing for that wrong selection, it's obviously part of the default behavior from the .Net Framework and it's either an error or intention to support only MS-Products.

I tried somethings to change the behavior. Update for clarification: Because the usage of "NpgsqlConnection" is not acceptable for me. My code should be provider-independent.

As I found in the Postgresql-Docu to npgsql and to another question with the same problem I added the following lines to the App.Configfile of the Console-App.

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

It did not help. The other thing is, if it would help with the App.Config how can I transfer that to my Cross-Platform-App. There is no App.Config.

My Problem is the environment not the programming itself. I start programming again after years in other jobs. So all the environment is absolutely new to me. Until that point Visual Studio handled anything automatically. So I have not to learn many details. I tried to find some information about the .Net-internals for DbProviderFactories and configuring of apps. But I did not find useful information, mostly because of not knowing how to search. So any help is useful to me. Thanks in advance.

If you use SqlConnection, then it's going to try and connect to SQL Server. Use NpgsqlConnection instead. — DavidG
– DavidG, Commented Jul 29, 2018 at 0:38
"My Problem is the environment not the programming itself." - well no, you are clearly in error with using a SqlConnection to talk to Postgres — user585968
– user585968, Commented Jul 29, 2018 at 1:02
You want to use the base DbConnection class rather than the SQL Server-specific SqlConnection — Camilo Terevinto
– Camilo Terevinto, Commented Jul 29, 2018 at 2:30

Richardissimo · Accepted Answer · 2018-07-29 06:13:52Z

SqlConnection is specific to SQL Server. You would need to use NpgsqlConnection to use a Postgres connection, which means you will need the references to the Postgres assemblies.

But you are intending to move to a different provider in future, so to minimise the impact of that move, try to use the generic base classes in your code. For example:

var connString = "Host=myserver;Username=mylogin;Password=mypass;Database=mydatabase";
using (DbConnection conn = new NpgsqlConnection(connString))
{
    conn.Open()
    using (DbCommand command = conn.CreateCommand())
    {
        // etc
    }
}

This way, when you swap over, all you need to do is replace NpgsqlConnection across your solution with SqlConnection. If you want to use DbProviderFactory (there's a good example in there) then you can, but basically you are only saving yourself this single search/replace, removing the references, and releasing a new version of the code.

I'd suggest putting the connection string in the config file rather than code (as shown in the question) to avoid having it in multiple places, and make it easier to change without a rebuild.

And of course you may have to fix any implementation-specific details in the SQL itself.

komluk · Accepted Answer · 2018-07-29 00:49:27Z

0

Here's a basic code to get you started. But the best way for beginners i to take a tour through tutorial http://www.npgsql.org/doc/index.html

var connString = "Host=myserver;Username=mylogin;Password=mypass;Database=mydatabase";
using (var conn = new NpgsqlConnection(connString))
{
    conn.Open();
}

answered Jul 29, 2018 at 0:49

komluk

6296 silver badges22 bronze badges

1 Comment

Mitch Over a year ago

Thank you, it works, but like that all my code has dependencies to Npgsql and any later change of the database will be horrible. I missed to say that it was my intention to avoid this dependency. It's important to be independent from the Db-Provider. Therefore the change of the Config-File, which came exactly from the same tutorial (link).

Collectives™ on Stack Overflow

C# SqlConnection Exception: Keyword not Supported 'Port'

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related