C# connecting to a postgres database

Question

This is my first time connecting to a database, but i'm having some problems

using Npgsql;

namespace DBPrj
 {
class Program
{
    static void Main(string[] args)
    {
        bool boolfound=false;
        NpgsqlConnection conn = new NpgsqlConnection("Server=<ip>; Port=5432; User Id=Admin; Password=postgres.1; Database=Test1"); //<ip> is an actual ip address
        conn.Open();

        NpgsqlCommand cmd = new NpgsqlCommand();
        NpgsqlDataReader dr= cmd.ExecuteReader(); //I get InvalidOperationException : The connection is not open.
        if (dr.Read())
        {
            boolfound=true;
            Console.WriteLine("connection established");
        }
        if(boolfound==false)
        {
            Console.WriteLine("Data does not exist");
        }
        dr.Close();
        conn.Close();



    }
}

}

What could be the problem? Is the NpgsqlConnection string written correctly? Could the database be protected from remote access?

How could I fix this problem?

Thanks in advance!

Niklas Rosencrantz · Accepted Answer · 2017-07-14 07:42:37Z

7

You never assign your NpgsqlConnection to your NpgsqlCommand and you don't supply a query to execute for your NpgsqlDataReader, fixing that should solve the immediate problems.

Also, wrapping at least your NpgsqlConnection in a using()-statement is a good idea to make sure that the connection is always closed, even if there is an exception.

using Npgsql;

namespace DBPrj
{
    class Program
    {
        static void Main(string[] args)
        {
            bool boolfound=false;
            using(NpgsqlConnection conn = new NpgsqlConnection("Server=<ip>; Port=5432; User Id=Admin; Password=postgres.1; Database=Test1"))
            {
                conn.Open();

                NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM Table1", conn);
                NpgsqlDataReader dr= cmd.ExecuteReader();
                if (dr.Read())
                {
                    boolfound=true;
                    Console.WriteLine("connection established");
                }
                if(boolfound==false)
                {
                    Console.WriteLine("Data does not exist");
                }
                dr.Close();
            }
        }
    }
}

edited Jul 14, 2017 at 7:42

Niklas Rosencrantz

26.7k78 gold badges247 silver badges447 bronze badges

answered Aug 7, 2012 at 10:31

Karl-Johan Sjögren

17.8k7 gold badges66 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kestami Over a year ago

my suspicions were correct, about the overloads on the command.

Kestami · Accepted Answer · 2012-08-07 10:29:54Z

1

In your connection string you may be missing a semi-colon at the end of database.

Database=Test1"

may need to be;

Database=Test1;"

also - it may be worth wrapping your conn.open() in a try catch statement for user-friendliness and ease of catching errors.

Edit 1:

Just did a little reading. Does NpgsqlCommand need parameters to be passed to it? just in pseudo code, something like;

NpgsqlCommand cmd = new NpgsqlCommand(query, conn);

edited Aug 7, 2012 at 10:29

answered Aug 7, 2012 at 10:14

Kestami

2,0834 gold badges32 silver badges49 bronze badges

Collectives™ on Stack Overflow

C# connecting to a postgres database

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related