How To : using Postgresql C# Client Npgsql to access postgresql database

After How to : install and test PostgreSql on Centos, we need to change several things to enable C# Client Access to the remote DB.

• Create a User (login) with a password
• Change the pg_hba.conf to turn on the user access from the giving IP range.  change postgres.conf  to change the listening ip address to * instead of just localhost by default.
• install Npgsql .net assembly on the client machine and write C# code.

To Create a user demo with password demo

\h Create User # \h will list the help tips for every command Create User demo superuser login password ’demo’; after that, you can query pg_shdow system table to list all users and their hashed password

PG_HBA.conf is located in the pgdata directory, here it will /usr/local/pgsql/data
add one line in the top , to enable demo user from host 192.168.209.1 to access all db with the md5 credential.

host    all     demo    192.168.209.1/32        md5

for postgresql.conf, change the listen address to * include all ips

#listen_addresses = 'localhost'   listen_addresses = '*'

the restart the service. “ pg_ctl -D /usr/local/pgsql/data/ restart”

Download the NPGsql .net library, create a C# Console application and reference the download Assembly Npgsql.dll

all the NPGXXX class are simply the implantation of ado.net standard interface.

Here is 10 lines of code to connect to remote postgresql db hellodb and do a basic query.

string connString = "Server=192.168.209.130;port=5432;user id=demo;password=demo;database=hellodb"; using (NpgsqlConnection conn = new NpgsqlConnection(connString)) { conn.Open(); NpgsqlCommand cmd = new NpgsqlCommand("SELECT name, countrycode from City limit 10", conn); NpgsqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) {     Console.WriteLine("City " + reader.GetString(0) + "Code " + reader.GetString(1)); } }

hit f5 to run

 
to my suprice, it use its own data protocol.