CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
I don’t hide the fact the PostgreSQL is my favourite database, and where possible I try to use it in as many places as possible (and on as many projects as possible).
There are a lot of reasons why I do this, and that’s simply because of some of the clever things that you can do with PG.
In this post, I’m going to introduce you to a couple of them, and show you how to get direct access to PG by using its native ADO.NET connector.
There is a chance that you can make some of these tricks work by using raw queries in Entity Framework, NHibernate, and other ORMs, but for this post I’ll be ignoring those and concentrating simply on doing this in raw C#.
To try the examples, you will need to install a current version of the PostgreSQL database and its admin tool, “PgAdmin3”. Going through that process in this post would take too long, but in most cases it’s simply a case of running a standard Windows installer that can be downloaded through this link.
Once you have Postgres up and running, fire up Visual Studio, create yourself a simple command line program, and use NuGet to search for and install “NpgSql”
Native Database Arrays
The first thing we’re going to look at is native arrays.
Postgres fully supports arrays as column types. This means that, when you create a table in the database, you can use an array-like syntax (the same as in .NET to define an array type column).
If you’ve installed Postgres correctly, you should have an application called “Pgadmin”. Find and run the tool, and then either find or create a database to experiment in (if you need a new one, you can right-click your server and choose ‘New Database’).
In my case, I’ve created a database called ‘playground’:
Figure 1: The Postgres test database
I’m not going to go into the process of creating/setting up a database because there’s not enough space in this post to do so. However, once you have a database to use, you then need to execute the following SQL in it, to create a test table for the array example:
CREATE TABLE blogarray ( pkid serial NOT NULL, intarray integer[], textarray text[], CONSTRAINT blogarray_pkey PRIMARY KEY (pkid) ) WITH ( OIDS=FALSE );
This will create a table in your database that has an auto increment primary key, and two array columns: one that holds an array of integers and one that holds an array of strings.
If you now add the following code to your Program.cs file in your Visual Studio console app project:
using System;
using System.Linq;
using Npgsql;
using NpgsqlTypes;
namespace npgsql
{
class Program
{
private const string SERVER = "MyServer";
private const string DATABASE = "MyDatabase";
private const string USER = "myUser";
private const string PASSWORD = "myPassword";
static string GetConnectionString()
{
var csb = new NpgsqlConnectionStringBuilder
{
Host = SERVER,
Database = DATABASE,
UserName = USER,
Password = PASSWORD
};
return csb.ConnectionString;
}
static string GenerateRandomString()
{
var chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
var random = new Random();
var result = new string(
Enumerable.Repeat(chars, 8)
.Select(s => s[random.Next(s.Length)])
.ToArray());
return result;
}
static int[] GetRandomInts()
{
Random random = new Random();
int[] results = new int[10];
for (int count = 0; count < 10; count++)
{
results[count] = random.Next(0, 1000);
}
return results;
}
static string[] GetRandomStrings()
{
string[] results = new string[10];
for (int count = 0; count < 10; count++)
{
results[count] = GenerateRandomString();
}
return results;
}
static void ArrayTest()
{
using(NpgsqlConnection connection =
new NpgsqlConnection(GetConnectionString()))
{
connection.Open();
string sql = "insert into blogarray(intarray, textarray)
values(:intparam , :textparam)";
using(NpgsqlCommand command =
new NpgsqlCommand(sql, connection))
{
command.Parameters.Add(new NpgsqlParameter("intparam",
NpgsqlDbType.Array | NpgsqlDbType.Integer));
command.Parameters.Add(new NpgsqlParameter("textparam",
NpgsqlDbType.Array | NpgsqlDbType.Text));
command.Parameters[0].Value = GetRandomInts();
command.Parameters[1].Value = GetRandomStrings();
command.ExecuteNonQuery();
}
connection.Close();
}
}
static void Main()
{
ArrayTest();
}
}
}
and then compile and run it, you should see that your database is now populated with some array bound data:
Figure 2: Our data table showing array bound data
If you add some further code to read the data back:
static void ArrayReadTest()
{
using (NpgsqlConnection connection =
new NpgsqlConnection(GetConnectionString()))
{
connection.Open();
string sql = "select * from blogarray";
using (NpgsqlCommand command =
new NpgsqlCommand(sql, connection))
{
using(NpgsqlDataReader reader =
command.ExecuteReader())
{
while(reader.Read())
{
int[] intArray = (int[])reader[1];
string[] textArray = (string[])reader[2];
}
}
}
connection.Close();
}
}
and then run that, and stop on a break point, you’ll see that you can read the array straight out of the database, and back into a normal .NET array without having to write any custom conversion code.
Figure 3: A breakpoint on the read code shows our array data read back from Postgres
In a similar way, you can use the new Json and Hstore types to handle document DB style data in a relational database. However, you have to handle both of these as regular single strings where the ADO.NET provider is concerned, and then do a quick conversion using something like JSON.NET.
Database Notifications
Another interesting trick that Postgres can do is to use its built-in Notification system.
For many years now, Postgres has had a very simple, but very effective pub/sub service built into it.
Switch to PG Admin (just as you did previously to create the table), in an SQL Window type:
listen mynotification
and execute it (press F5 if you’re using PG Admin). At this point, you won’t see anything other than a message that tells you the statement executed successfully. However, if you now enter the following:
notify mynotification
you should now see that Postgres signals into your result window that it received a notification on the channel you asked it to listen on.
Figure 4: PG Admin showing a notification
If you want to add some data to the notification, simply enclose it in single quotes after a comma:
notify mynotification, 'Hello Postgres'
and the notification also will send that:
Figure 5: PG Admin showing a notification, with data
You can send anything you like in the payload string, but only one single string can be sent. If you want to send multiple values, you need to encode the string as JSON or an array or something else you can deserialize.
You can use the pub/sub service inside .NET using NpgSql just by wrapping the preceding commands in a normal NonQuery SQL Command. There are, however, a couple of gotchas to be aware of.
First, Notifications come in two types: Asynchronous and Synchronous, The Async version has been available in NpgSql since before version 1, and relies on your .NET program calling Postgres functions at regular intervals before it will receive a notification request.
Because you need to make an API call before a request is passed to your application, you could un-intentionally introduce delays into your application’s notification system without being aware. To counter this you, would frequently have to ‘Poll’ your NpgSql connection for notification events to be fired in a timely manner.
The Sync version works the same way, except that the notifications are delivered to your app automatically as soon as they are raised by Postgres. This is more reliable, and leads to fewer delays but, because of the way it works, you CANNOT call any other Postgres operation from within the notification handler, without first opening a new connection for that operation. Depending on what you’re attempting to do, this may not be an issue, but it’s highly recommended that, if you’re using Sync notifications, you use the notification event handler only to set a flag and raise an event elsewhere in your application in reaction to that flag, if you wish to perform other Postgres related activities.
You set the notification mode you want to use in your connection string by setting ‘SyncNotification’ to false for Async mode and true for sync mode, if you’re using the NpgSqlConnectionStringBuilder to build your connection string (as I did in the array example), you simply need to set ‘SyncNotification’ to either true or false, as required.
Making use of notifications in your .NET code is trivially easy. Add the following functions to your Program.cs alongside the code you created for the array test.
static void StartListening()
{
_notificationConnection =
new NpgsqlConnection(GetConnectionString());
_notificationConnection.Open();
string sql = "listen mynotification";
using (NpgsqlCommand command = new NpgsqlCommand(sql,
_notificationConnection))
{
command.ExecuteNonQuery();
}
_notificationConnection.Notification +=
PostgresNotification;
}
static void StopListening()
{
string sql = "unlisten mynotification";
_notificationConnection.Notification -=
PostgresNotification;
using (NpgsqlCommand command = new NpgsqlCommand(sql,
_notificationConnection))
{
command.ExecuteNonQuery();
}
_notificationConnection.Close();
}
static void PostgresNotification(object sender,
NpgsqlNotificationEventArgs e)
{
Console.WriteLine("I are Notification -->");
Console.WriteLine(" DATA {0}",
e.AdditionalInformation);
Console.WriteLine(" CHANNEL {0}", e.Condition);
Console.WriteLine(" PID {0}", e.PID);
}
static void TriggerNotification()
{
string sql = "notify mynotification,
'This is some extra data'";
using (NpgsqlCommand command = new NpgsqlCommand(sql,
_notificationConnection))
{
command.ExecuteNonQuery();
}
}
Change your ‘main’ method to look like this
static void Main()
{
StartListening();
Console.ReadLine();
TriggerNotification();
Console.ReadLine();
TriggerNotification();
Console.ReadLine();
StopListening();
}
and, finally, update your ‘GetConnectionString’ method so it looks like the following:
static string GetConnectionString()
{
var csb = new NpgsqlConnectionStringBuilder
{
Host = SERVER,
Database = DATABASE,
UserName = USER,
Password = PASSWORD,
SyncNotification = true
};
return csb.ConnectionString;
}
If you now run your application, and press Return three times, you should get an output similar to what’s shown in Figure 6:
Figure 6: Output produced by the Postgres notification test
As you can see, the first line is the notification payload, the second the notification name, and the third is the process ID of the responding background process.
Remember, too, you don’t have to trigger these notifications from the same .NET program. You could, for example, trigger them from within a long-running stored procedure, that sends a percentage value back to the app. The app then use could that to update a progress indicator.
You can trigger multiple notifications, on different channels, and then be able to tell which channel triggered the notification by examining the channel name.
Program Originated Data
The final trick I’m going to show you (but by no means the last) is, as the header says, ‘Program originated data.’
I can already hear the confusion at that statement, so I guess I better explain it before we go any further.
Postgres has the ability to execute an application of any description that’s runnable by the server on which it’s installed, and then feed any output produced by the program into a database table.
For example, if you had a command line program that generated 10 random integers for a given number of lines, you then could feed those 10 integers into 10 integer columns in a table in your database.
As it turns out, this can come in quite handy. In the past, for example, I’ve used this feature by writing a .NET command line program that consumes some strange data feed, and then print that feed to the console as a standard CSV, while using the program data facility to place the CSV data into a correctly formatted table schema.
Add the following method to your Program.cs file:
static void GenerateProgramData()
{
Random random = new Random();
for (int linecount = 0; linecount < 10;
linecount++)
{
for (int numbercount = 0; numbercount < 10;
numbercount++)
{
Console.Write("{0},", random.Next(0,1000));
}
Console.WriteLine("LINE{0}", linecount);
}
}
followed by updating your main method to look like this:
static void Main() { GenerateProgramData(); }
If you run your program at this point, you should see something like the output in Figure 7:
Figure 7: Output from program data method
As you can see, all it simply does is generate 10 lines each containing 10 numbers and an item of text.
Using Pgadmin3, create a new table in your test database, using the following SQL:
CREATE TABLE public.programdata ( intone integer, inttwo integer, intthree integer, intfour integer, intfive integer, intsix integer, intseven integer, inteight integer, intnine integer, intten integer, linetext text ) WITH ( OIDS = FALSE );
Now, compile and build your console app; then, copy the exe to a folder your Postgres database is able to access. Because my server is on a different machine, a copied the console app across the network to a known location on the same machine that my copy of Postgres could see. For you, this might just be as simple as ‘C:\’ or even a temp folder. As long as your Postgres server can access that folder via its full folder path, everything will work fine.
Once you’ve copied the app, in your PgAdmin SQL window, execute the following statement:
copy programdata (intone, inttwo, intthree, intfour, intfive, intsix, intseven, inteight, intnine, intten, linetext) from program 'g:\npgsql.exe' csv
Remember to change the ‘g:\npgsql.exe’ part to point to the location you copied your application to.
If you look in the table you created using Pgadmin, you should see something that looks similar to the data in Figure 8:
Figure 8: The data from our program, added to our Postgres table
As you can see, the output from the program has been directly inserted into the table you created.
There is one thing you must be aware of, though. Using the copy from program command, you MUST be logged into your Postgres database as ‘Postgres’ (the Postgres account is the equivalent of ‘sa’ on a MS-SQL), if you’re not, you will receive a security error telling you the operation is not permitted.
By using program data, for instance, you could easily write a small stub program that reads the server process table and inserts it into your database, which then can be easily queried, or, as previously mentioned, use it to pre-transform public data, or as we did above. Just use it for generating test data; the list is endless.
Postgres can do much more than the three things I’ve outlined here, and the NpgSql driver can support many of them directly. In particular, there are a lot of custom data types, designed to handle IP Addresses, Binary data, time and date ranges, and many others that are not available using things like Entity Framework and other ORMs.
Got a strange .NET problem you need to solve? Or a weird assembly you’ve discovered that you don’t know what it does? Come and find me on the interwebs (I’m usually on twitter as @shawty_ds), and I’ll see if I can do a post on it.
Until then, happy Postgressing!
