CSharp PostgreSQL

This article is about developing database applications in C# using PostgreSQL. I developed such an application to transfer the consumption values of my smart meter from a comma seperated values (CSV) file to a PostgreSQL database.

Packages

• pgadmin3 - this is for convenient working with PostgreSQL
• libmono-npgsql4.0-cil (Mono Npgsql library (for CLI 4.0) - installs the bindings needed for developing a C# application that talks to PostgreSQL)

MonoDevelop

In a C# solution, "Npgsql.dll" - usually found in /usr/lib/mono/4.0/Npgsql.dll - must be added.

C# examples

Opening connection

var connString = @"server=localhost;userid=smartmeter;password=SECRET;database=smartmeter;";
using (var conn = new NpgsqlConnection(connString))
{
  conn.Open();
  // Server version can be found in conn.ServerVersion
  // Do something with connection
}

Insert data to table with variables

It is best practice to use variables (also called bind variables). Do not consider concatenating a string and build a query string yourself! This makes your application vulnerable to security problems, namely SQL injection. Ask your favorite search engine about it, there are millions of articles addressing that.

int ra = -1;
using (var insertCommand = conn.CreateCommand()) {
  insertCommand.Connection = conn;
  insertCommand.CommandText = "insert into meteringvalues " + "(devicenr, tariff, source, sourcetype, \"timestamp\", value) values " + "(:devicenr, :tariff, :source, :sourcetype, :timestamp, :value)";
  insertCommand.Parameters.Add("devicenr", NpgsqlTypes.NpgsqlDbType.Varchar, 20).Value = DeviceNr;
  insertCommand.Parameters.Add("tariff", NpgsqlTypes.NpgsqlDbType.Bigint).Value = Tariff;
  insertCommand.Parameters.Add("source", NpgsqlTypes.NpgsqlDbType.Bigint).Value = Source;
  insertCommand.Parameters.Add("sourcetype", NpgsqlTypes.NpgsqlDbType.Bigint).Value = SourceType;
  insertCommand.Parameters.Add("timestamp", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = Timestamp;
  insertCommand.Parameters.Add("value", NpgsqlTypes.NpgsqlDbType.Double).Value = value;
  insertCommand.Prepare ();
  int ra = insertCommand.ExecuteNonQuery ();
}

However, there is one caveat of that solution: If you change your database schema, you also have to change your application's code. It is better to write a stored procedure that finally inserts the data to the destination table. This also enables you to check values, transform them, dispatch them to different tables and so on in the database (hence don't having the need to change the application which requires deploying it again).

To complete that example, here is the definition of the table:

CREATE TABLE meteringvalues
(
  devicenr character(20),
  tariff bigint,
  source bigint,
  sourcetype bigint,
  "timestamp" timestamp without time zone,
  value double precision
)

Help

These web pages were helpful:

• PostgreSQL in Ubuntu Help
• PostgreSQL and C#
• npgsql User Manual