1. Computing

How to access PostgreSQL from C#

By

PostgreSQL Logo

For a change from the usual MySQL, I thought I'd install PostgresSQL and see how easy or hard it was to use it in a C# application. In my case that meant setting up a Linux PostgreSQL database and accessing it from a Windows 7 application in C#.

Other scenarios are running both the database and app on the same box and that works irrespective of whether it's a Windows box or Linux.

PostgreSQL is an open source relational database much like MySQL though older, dating from 1989. It's always lived in the shadow of MySQL which had early success in the web hosting world, even though with PostgreSQL having more advanced features like views and triggers years before MySQL got them. With a degree of uncertainty still hanging over MySQL, it may be a good time to hedge bets by looking at PostgreSQL.

PostgreSQL is available for these platforms: FreeBSD, Linux, Mac OS X, Solaris and Windows which covers just about everything. I have a networked Linux box running the most recent version of Linux Mint so I installed the version (8.4) of PostgreSQL that comes from using the Synaptic package manager and used the free package Webmin to configure it.

If you use Linux and aren't great at administration (like me!), webmin is excellent. I've used it to configure Apache, MySql and Samba (the software that lets Windows access files directly on a Linux box).

  • Want to know more about Linux? Read Linux on About.com

By default PostgreSQL lets you login with user postgres with no password but only on the same computer (localhost access) and not from a networked computer. For accessing a networked server from a networked PC you must add the accessing host or * in the /etc/postgresql/8.4/main/postgresql.conf in the settings, That's for PostgreSQL 8.4 on Linux.

Listen_addresses = "*"

And when you create a database (in Webmin), you should also create a user with a password and in the webmin Allowed Hosts page set it up so that the database is only accessible by that user with an encrypted password. It's possible to setup access without a password but not a good idea!

I got a strange glitch where PostgreSQL complained about the word crypt in the pg_hba.conf file and refused to start. I had to edit that file from the Linux terminal (command line) with the line

sudo gedit pg_hba.conf

The sudo is needed because you need Administrator access to edit that file.

Look for this line.

host all [user] 0.0 0.0 0.0 0.0 crypt

where [user] is the user you setup to access PostgreSQL and change the word crypt to md5 then restart PostgreSQL.

Once you've done that you can download the excellent freeware EMS SQL Manager for PostgreSQL lite package (for Windows) which lets you create tables in the specified databases. Once you can connect and create tables then it's time to try accessing it from C#.

Accessing PostgreSQL from C#

The pgfoundry is the home of many PostgreSQL software packages but the one we're interested in is Npgsql; it's a .Net Data Provider for PostgreSQL that's written in C# and works easily with .NET database code. i.e. just create a connection string and use the built in objects.

After downloading Npgsql, unzip it and copy these three dlls into the same folder where your compiled exe will run from. Alternatively if you have it (from the Windows SDK) you could use gacutil, the .NET utility to add it to them General Assembly Cache (home of .NET assembly dlls).

  • Mono.Security.dll
  • Npgsql.dll
  • policy.2.0.Npgsql.dll

You can now add Npgsql as a reference to your C# project and gain access to various types such as NpgsqlCommand, NpgsqlConnection, NpgsqlDataReader and many more and use them in your application. The following code shows a row being added to table users in database one. A connection is made then a command runs some insert query to add a row with two columns.

using System;
using Npgsql;

namespace gvtest
{
    class Program
    {
        static void Main(string[] args)
        {
            NpgsqlConnection conn = new NpgsqlConnection ("Server=gandalf;Port=5432;User
Id=someuser;Password=somepassword;Database=one;") ;
            conn.Open() ;
    
            NpgsqlCommand command = new NpgsqlCommand("insert into users (iduser,email) values(1,'cplus@aboutguide.com')", conn) ;
    
            try
            {
              int rowsaffected = command.ExecuteNonQuery() ;
     
              Console.WriteLine("It added {0} lines in table table1", rowsaffected) ;
            }
    
            finally
            {
              conn.Close() ;
            }
          }
        }
}

Conclusion

This worked very well on my Windows/Linux setup and the next step will be trying it out on Linux. The Mono Migration Analyzer found no issues with my code so it should be straightforward.

©2014 About.com. All rights reserved.