Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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;";
                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
                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
                Jul 29, 2018 at 1:02
                You want to use the base DbConnection class rather than the SQL Server-specific SqlConnection
– Camilo Terevinto
                Jul 29, 2018 at 2:30
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.
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();
                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).
– Mitch
                Jul 29, 2018 at 1:54
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.