none
How to connect WinForms DataGridView to PostgreSQL DB over network RRS feed

  • Question

  • I'm trying to set up a connection to a PostgreSQL database as a datasource for a WinForms DataGridView.

    I can't figure out how to set up the connection.

    I'm using DBeaver for my DBMS and have set up a test table with some simple data. Right now, I'm just trying to test the connection by sending the version to the console. The server is on a network and the host is an IP address.

    In Form1_Load I've tried this:

        string strConnString = "Server=<ipaddress>;Port=5432;Username=<username>;Password=<password>;Database=test.cars";
        NpgsqlConnection objConn = new NpgsqlConnection(strConnString);
        
        try
        {
            objConn.Open();
            string strSelectCmd = "select version()";
            var cmd = new NpgsqlCommand(strSelectCmd, objConn);
            var version = cmd.ExecuteScalar().ToString();
            Console.WriteLine($"PostgreSQL version: {version}");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error message", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

    But I get an exception at the `objConn.Open();` line.

    So, am I not setting up the connection string correctly? Once I get that connected, is the remainder of the code correct?

    Then there's the question of how to connect the db to a DataGridView (the only way I know how to do that is via the wizard and I've only done that with a SQL Server db - but first things first...).

    Thursday, March 5, 2020 2:57 PM

All replies

  • Hello,

    Please specify the exact exception message.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, March 5, 2020 3:46 PM
  • Yes, I suppose an error message would help!

    When I run it, the message box says, "Exception has been thrown by the target of an invocation"

    Below is what I get in the Output window. Below that I've pasted my entire code.

    'PostgreSQL Test.exe' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_32\mscorlib\v4.0_4.0.0.0__b77a5c561934e089\mscorlib.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'PostgreSQL Test.exe' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\Users\marky\Documents\My VS Projects\PostgreSQL Test\bin\Debug\PostgreSQL Test.exe'. Symbols loaded.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Windows.Forms\v4.0_4.0.0.0__b77a5c561934e089\System.Windows.Forms.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System\v4.0_4.0.0.0__b77a5c561934e089\System.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Drawing\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Configuration\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Core\v4.0_4.0.0.0__b77a5c561934e089\System.Core.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Xml\v4.0_4.0.0.0__b77a5c561934e089\System.Xml.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Users\marky\Documents\My VS Projects\PostgreSQL Test\bin\Debug\Npgsql.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_32\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_32\System.Transactions\v4.0_4.0.0.0__b77a5c561934e089\System.Transactions.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Users\marky\Documents\My VS Projects\PostgreSQL Test\bin\Debug\System.ValueTuple.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\PrivateAssemblies\Runtime\Microsoft.VisualStudio.Debugger.Runtime.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Users\marky\Documents\My VS Projects\PostgreSQL Test\bin\Debug\System.Threading.Tasks.Extensions.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\netstandard\v4.0_2.0.0.0__cc7b13ffcd2ddd51\netstandard.dll'. Module was built without symbols.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_32\System.EnterpriseServices\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_32\System.EnterpriseServices\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.Wrapper.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Users\marky\Documents\My VS Projects\PostgreSQL Test\bin\Debug\System.Runtime.CompilerServices.Unsafe.dll'. Module was built without symbols.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Runtime\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Runtime.dll'. Module was built without symbols.
    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Users\marky\Documents\My VS Projects\PostgreSQL Test\bin\Debug\System.Memory.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    Exception thrown: 'System.Reflection.TargetInvocationException' in mscorlib.dll

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using Npgsql;
    using System.Data.SqlClient;
    
    namespace PostgreSQL_Test
    {
    	public partial class Form1 : Form
    	{
    		public Form1()
    		{
    			InitializeComponent();
    		}
    		
    		private void Form1_Load(object sender, EventArgs e)
    		{
    			string strConnString = "Server=10.14.50.105;Port=5432;Username=timetrex;Password=cpsiispc;Database=test.cars";
    			NpgsqlConnection objConn = new NpgsqlConnection(strConnString);
    
    			try
    			{
    				objConn.Open();
    			}
    			catch (Exception ex)
    			{
    				MessageBox.Show(ex.Message, "Error message", MessageBoxButtons.OK, MessageBoxIcon.Error);
    			}
    		}
    	}
    }

    Thursday, March 5, 2020 4:31 PM
  • Hello,

    Kind of feels like a versioning issue with the data provider, did you add a reference via NuGet package manager and what version is being used?

    Lastly, you might consider asking on stack overflow and tag your question wit npgsql tag


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, March 5, 2020 5:29 PM
  • Sorry - it's been forever since I've done this stuff, I've only ever used SQL Server data connections, and I'm only doing it for our SSRS reporting team because I'm the lucky one with the only experience building any WinForms! :)

    I downloaded "baseconnectionlibrary.1.0.3.nupkg" and in VS went to Add Reference, but it's not seeing that extension, so I'm not sure how to add it to my solution.

    I did post a question on SO yesterday but hand't gotten any responses. I added the npgsql tag per your recommendation.

    Thursday, March 5, 2020 6:42 PM
  • Hi Mark Yorkovich, 
    Based on your description, you can add the reference by following the steps firstly.
    1.Right click the project> Add> Reference> Browse> Select your reference> Add.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 6, 2020 1:34 AM
  • Thanks, Daniel, but I tried that and what I downloaded (baseconnectionlibrary.1.0.3.nupkg) isn't being recognized as a valid reference. When I switch the browse file extension to All I get the error message, "A reference to (filepath/name) could not be added. Please make sure that the file is accessible, and that it is a valid assembly or COM component."

    Also, after I close the window to browse to a reference, this is in the Output pane: "[Failure] Could not find file 'C:\Users\marky\AppData\Local\Temp\.NETFramework,Version=v4.7.2.AssemblyAttributes.cs'."

    So perhaps I'm not downloading the correct file?

    Friday, March 6, 2020 1:06 PM
  • Hi Mark Yorkovich,
    You can try the following steps to install it.
    Right-click the project> Manage NuGet Packages> Browse> Enter the baseconnectionlibrary> Instll it.
    And about the error, I suggest that you check for an inner exception. If there isn't one, check your logs for the exception that occurred immediately prior to this one.
    Here are more causes in this link and you can refer to it.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 10, 2020 7:52 AM
  • Thanks, Daniel

    I was able to install the NuGet library.

    WARNING! A lot of information coming in! :)

    The initial reading that I did when looking into logging exceptions (I'm new at that) said to just do a Console.WrietLine(ex.ToString(). I did that and in addition to the above Output text, I got the below in the Output window (After "Output Window text" below). Does that shed any light on what's going on? Link(s) to where I can find answer(s) to that would be helpful!

    Also, I checked All in the Exception Settings for Common Language Runtime Exceptions. And when I run the solution it stops at the .Open() method and a window opens:

    

    The call stack reads in full: System.Security.Cryptography.MD5CryptoServiceProvider.MD5CryptoServiceProvider()

    I Googled "Visual Studio C# This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms" and that pointed me to an article about disabling FIPS mode in Windows Control Panel - am I going down the right path regarding that?

    Output Window text:

    'PostgreSQL Test.exe' (CLR v4.0.30319: PostgreSQL Test.exe): Loaded 'C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\PrivateAssemblies\Runtime\Microsoft.VisualStudio.Debugger.Runtime.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms.
       at System.Security.Cryptography.MD5CryptoServiceProvider..ctor()
       --- End of inner exception stack trace ---
       at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
       at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       at System.Security.Cryptography.CryptoConfig.CreateFromName(String name, Object[] args)
       at System.Security.Cryptography.MD5.Create()
       at Npgsql.NpgsqlConnector.<AuthenticateMD5>d__3.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.Auth.cs:line 177
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at Npgsql.NpgsqlConnector.<Authenticate>d__0.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.Auth.cs:line 34
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at Npgsql.NpgsqlConnector.<Open>d__148.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 423
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at Npgsql.ConnectorPool.<AllocateLong>d__28.MoveNext() in C:\projects\npgsql\src\Npgsql\ConnectorPool.cs:line 249
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at System.Threading.Tasks.ValueTask`1.get_Result()
       at Npgsql.NpgsqlConnection.<>c__DisplayClass32_0.<<Open>g__OpenLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnection.cs:line 297
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at Npgsql.NpgsqlConnection.Open() in C:\projects\npgsql\src\Npgsql\NpgsqlConnection.cs:line 119
       at PostgreSQL_Test.Form1.Form1_Load(Object sender, EventArgs e) in C:\Users\marky\Documents\My VS Projects\PostgreSQL Test\Form1.cs:line 35


    Tuesday, March 10, 2020 12:22 PM
  • Hi Mark Yorkovich,
    Here is a document about connecting PostgreSql to windows forms and you can refer to it.
    Hope it could be heplful for you.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 13, 2020 9:50 AM