Introduction
In this article, I would like to show you the basics of using a PostgreSQL database in your .NET application. The reason why I'm doing this is the lack of PostgreSQL articles on CodeProject despite the fact that it is a very good RDBMS. I have used PostgreSQL back in the days when PHP was my main programming language, and I thought.... well, why not use it in my C# application.
Background
I assume you have a basic knowledge of databases, SQL commands, and so on.
Why PostgreSQL
First and foremost, it's
free*, in BSD-style freedom (both libre and gratis), which means you will not have license problems in your commercial applications. Next, you have a very good procedural language called
PL/pgSQL (there are also the Java, Perl, Python, Tcl (and other) flavors). Lots of communities support it (large ones like
Planet PostgreSQL, or blogs, for instance,
"Depesz" blog), and finally, lot's of fun exploring this pearl of open source databases.
Installing PostgreSQL and the .NET data provider
First of all, go to the
PostgreSQL website. PostgreSQL is known for having a
very good
documentation system. For the purposes of this article, I used PostgreSQL v.
8.3.4 and PgAdmin III v. 1.8.4 (that comes bundled with PostgreSQL). Remember to install the database
and PgAdmin III, but
NOT Npgsql, since it comes with version 1.x and we're going to use v. 2.0. from a different package. Now, if you have a PostgreSQL server up and running (which is a
really easy task), let's download some drivers. For this article, I have used
NpgSQL2 built
directly for MS .NET. When you download it, in the zip package (
\bin directory), you will find
Npgsql.dll, and that's the library we're looking for.
Playing with PgAdmin III
If you have ever worked with any DB-Management software like
Aqua Data Studio,
SQL Manager (I use the Lite version every day), or the MS Management Studio Express, you will find PgAdmin III really intuitive. It may not have that many gizmos as others, but it will do the job nicely. Here are a couple of daily tasks that you probably want to know how to do.
- Creating a new user (unless you really don't mind playing with a super-user account :-)):
- Creating a new database (fairly obvious, but nevertheless here it goes :-)):
- Creating a table:
- Adding stuff to tables (there are, of course, other ways, but I will let you explore this):
- If you are looking for an MS-SQL Identity, just make your column type serial, or look for sequence in the documentation. OK, to be completely honest, PgAdmin III still needs work... numbers may not appear if you add a row through the grid, but believe me, they are there :-). You will see them the next time you reload a window with the grid.
- If you looking for more hit documentation :-)
Making a database
Here is a SQL-script so we can make our test database. Remember to create a database and a user. My user for this article will be adrian.
Hide Copy Code
CREATE TABLE simple_table
(
id integer NOT NULL,
tekst character varying(50),
CONSTRAINT simple_table_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE simple_table OWNER TO adrian;
INSERT INTO simple_table (id, tekst) VALUES (1, 'jaki tekst');
INSERT INTO simple_table (id, tekst) VALUES (2, 'jaki inny tekst');
INSERT INTO simple_table (id, tekst) VALUES (3, 'jeszcze inny tekst');
Using PostgreSQL in C#
Just make an empty Windows Forms application, and add a reference to Npgsql.dll, like shown in the picture:
Baking a connection string PostgreSQL-style
A connection string in PostgreSQL is made by making keyword=value; pairs (Important: even the last element must end with a ';'). The most basic ones are:
- Server - specifies the server location
- User Id - the database user
- Port - default is 5432
- Password - the password for the database user
- Database - the database name
Simple test application
Hide Shrink 
Copy Code
using Npgsql;
namespace PostgreSQLTEst
{
public partial class Form1 : Form
{
private DataSet ds = new DataSet();
private DataTable dt = new DataTable();
public Form1()
{
InitializeComponent();
}
private void llOpenConnAndSelect_LinkClicked(object sender,
LinkLabelLinkClickedEventArgs e)
{
try
{
string connstring = String.Format("Server={0};Port={1};" +
"User Id={2};Password={3};Database={4};",
tbHost.Text, tbPort.Text, tbUser.Text,
tbPass.Text, tbDataBaseName.Text );
NpgsqlConnection conn = new NpgsqlConnection(connstring);
conn.Open();
string sql = "SELECT * FROM simple_table";
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
ds.Reset();
da.Fill(ds);
dt = ds.Tables[0];
dataGridView1.DataSource = dt;
conn.Close();
}
catch (Exception msg)
{
MessageBox.Show(msg.ToString());
throw;
}
}
}
}
And the effect:
And, that's it! :D. Practice PostgreSQL and enjoy!
