C# postgres database connection

Question

I have already managed to connect to the database, but I used the following code to connect.

static void Main(string[] args)
{
    using (NpgsqlConnection conn= new NpgsqlConnection(
        "Host=xxx.xx.xx.xxx;Port=5432;User Id=Admin;Password=postgres.1;Database=Test1;")) 
    {
        conn.Open();

        NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM TABLE1", conn);

        try
        {
            NpgsqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    Console.Write("{0} \t", dr[i]);
                }
                Console.WriteLine();
            }
            dr.Close();
        }
        finally
        {
            conn.Close();
        }
    }

    Console.ReadLine();
}

Apparently I have to somehow generate classes for the tables in the database and use those to connect instead of using NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM TABLE1", conn);. Already tried using DbLinq's DbMetal, but I get the error message :

DbMetal : Server has closed connection.

I've been researching this, but I haven't found anything useful.

Please help if you can. It's kinda urgent.

Thanks in advance.

romain.blogreen.org/blog/2009/07/linq-postgresql-and-mono probably this could help — horgh
– horgh, Commented Aug 8, 2012 at 7:28
@horgh those are the instructions that I followed. Tried both with cmd and mono. It connects for a second and then loses connection — robertpas
– robertpas, Commented Aug 8, 2012 at 7:49

Steve · Accepted Answer · 2012-08-08 07:25:56Z

1

I think you need a NpgsqlDataAdapter and a DataSet

            DataSet ds = new DataSet();
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(); 
            da.SelectCommand = cmd;
            da.Fill(ds);
            return ds;

Now your DataSet contains a table (ds.Tables[0]) and that table all the rows selected

            foreach(DataRow r in ds.Tables[0].Rows)
                 Console.WriteLine(r["ColumnName"].ToString());

answered Aug 8, 2012 at 7:25

Steve

217k22 gold badges242 silver badges296 bronze badges

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

5 Comments

robertpas Over a year ago

I don't think it's what i'm looking for, but thanks for the answer. It works.

Steve Over a year ago

Sorry for that, but then I would like to understand better. Could you elaborate on 'generate classes for the tables'?

robertpas Over a year ago

So my supervisor says that i should not use any command(like SELECT * FROM) from C#, but instead have classes which describe the tables in the database. He says that I should use those. He recommended using dbLinq which has an application DbMetal, which if given a connection string would connect to the given database and return a class, example : "Table1.cs" which will then be copied into my program.

Steve Over a year ago

Thanks for the clarification, sorry, but can't help on dbLinq.

robertpas Over a year ago

No problem. It's pretty much a dead end for me though. Can't find anything. At least tomorrow is my last day of internship :)

Collectives™ on Stack Overflow

C# postgres database connection

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related