CODEWITHSUNDEEP
c#postgresql
wandermonk
wandermonk

Reputation: 7346

Querying postgres from c# using npgsql

I have installed postgres in my windows machine and started working on a POC. I am able to connect to the database from the windows command line. But, I am unable to connect from the C# application.

It seems i have some issues in the connection string. I have gone through multiple tutorials but every tutorial has their own way of providing the connection string parameters.Is there any standard way of giving the hostname,port,username,password and database.

I am trying to query using a rest api. Am, I doing the right way.

// GET api/values
[HttpGet]
public IActionResult Get()
{
   Test test = new Test();
   return Ok(test.Table2Json());
}

using Npgsql;
using System;

namespace Zeiss.MCCNeo.DataMigration.Utilities
{
  public class Test
  {
     private readonly NpgsqlConnection conn;
     public Test()
     {

     conn = new NpgsqlConnection("Server=127.0.0.1;User Id=postgres;" +
                                    "Password=postgres;Database=postgres;");
     //also tried using this
     conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User                                       
                                   Id=postgres;" +                              
                                   "Password=postgres;Database=postgres;");
     }

     public string Table2Json()
     {
         string value = null;
         NpgsqlCommand command = new NpgsqlCommand("select * from test", 
                                        conn);
         NpgsqlDataReader dr = command.ExecuteReader();
         while (dr.Read())
         {
         value = dr[0].ToString();
         }
         return value;
         }
    }
 }

Exception:

-       $exception  {System.InvalidOperationException: Connection is not open
   at Npgsql.NpgsqlConnection.CheckReadyAndGetConnector()
   at Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__92.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteReader()
   at Zeiss.MCCNeo.DataMigration.Utilities.Test.Table2Json() in c:\users\inpyadav\documents\visual studio 2017\Projects\DataMigration\Zeiss.MCCNeo.DataMigration.Utilities\Test.cs:line 19
   at DataMigration.Controllers.ValuesController.Get() in c:\users\inpyadav\documents\visual studio 2017\Projects\DataMigration\DataMigration\Controllers\ValuesController.cs:line 18
   at lambda_method(Closure , Object , Object[] )
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__27.MoveNext()}  System.InvalidOperationException

Upvotes: 0

Views: 1740

Answers (1)

Admir
Admir

Reputation: 155

Why don't you open connection as it says in error message?

conn.Open();

Upvotes: 1

Related Questions