C# Npgsql tutorial

Hi,

I’ve just started using C# for the first time and decided to chuck up a little intro on how to connect with a Postgres database. This is a very stripped down tutorial, does the basics and doesn’t really handle exceptions, error states and the like. Firstly you need to get a few DLLs, Npgsql.dll, policy.2.0.Npgsql.dll and MonoSecurity.dll. You can get them from PgFoundry here.

I’ve put these in the same folder as the project. Go to Solution Explorer and locate the ‘References’ section under your project. Right click that and Add Reference. Click the ‘Browse’ tab and select the Npqsql dll only.
Firstly you need to load up the package/namespace (I’m not sure of the correct terminology here). Then you’re ready to connect to the database using the correct name, port, username and so on. This is simple.

using System;
...
using Npgsql;

....

NpgsqlConnection dbConnection = new NpgsqlConnection("Server=myServer;Port=xxxx;User Id=xyz;Password=xyz;Database=myDB;");

try
 {
        dbConnection.Open();
 }
catch
{
        //Handle error states here
}

That’s it! You’re now ready to execute queries and updates on the database.

I’ve included a very simple select statement below, it selects all from a hypothetical animal table and extracts the name and type column values from the results. The GetOrdinal is a useful helpful function to return you the integer that represents the index of the named column. It is more robust to changes in the database as indices may change but you need to check for NULLs as it won’t work (I’ve found out!)

NpgsqlCommand Command = new NpgsqlCommand("select name,type from animals", dbConnection);
NpgsqlDataReader result = Command.ExecuteReader();

while (result.Read())
{
      String animalName = result.GetString(result.GetOrdinal("name"));
      String animalType = result.GetString(result.GetOrdinal("type"));
      ... (Do something here)
}

Finally when you’re done with your database you need to close down the connection

dbConnection.Close();
dbConnection = null;

Thats it’s some very simple DB manipulations in very few lines of code, impressed with C# so far I must say!
Next step do things in transactions and handle exceptions!