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.
- Want to know What is a Relational Database?
- Want to know more about SQL? Read What is SQL?
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.
