Monday, March 7, 2011

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

image

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

image

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

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

image 
to my suprice, it use its own data protocol.

image

1 comment:

Job site review said...

hello,
can u suggest a way to set search_path for connection session, because running "set search_path='schema'" before query is a too much for me. I was looking for a solution of it and got stuck here.


And nice blog... continue this, it will help a lot of people who are looking for good start with Postgresql.

 
Locations of visitors to this page