Click here to Skip to main content
15,860,972 members
Articles / Programming Languages / SQL
Article

Using PostgreSQL in your C# (.NET) application (An introduction)

Rate me:
Please Sign up or sign in to vote.
4.92/5 (80 votes)
15 Nov 2008BSD4 min read 766.7K   15.9K   112   55
In this article, I would like to show you step by step how to use this fantastic database in your C# application.

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.

  1. Creating a new user (unless you really don't mind playing with a super-user account :-)):
  2. How to create new role on the server

  3. Creating a new database (fairly obvious, but nevertheless here it goes :-)):
  4. How to create new database in PgAdmin III

  5. Creating a table:
  6. How to create new table in PostgreSQL

  7. Adding stuff to tables (there are, of course, other ways, but I will let you explore this):
  8. How to add more tables

  9. 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.
  10. 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.

SQL
CREATE TABLE simple_table
(
  id integer NOT NULL, -- SERIAL if like identity-like functionality
  tekst character varying(50),
  CONSTRAINT simple_table_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE simple_table OWNER TO adrian; -- my user

-- i drop Polish letters so i don't have to worry about codepage
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:

Adding Npgsql provider

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

C#
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
            {
                // PostgeSQL-style connection string
                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 );
                // Making connection with Npgsql provider
                NpgsqlConnection conn = new NpgsqlConnection(connstring);
                conn.Open();
                // quite complex sql statement
                string sql = "SELECT * FROM simple_table";
                // data adapter making request from our connection
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                // i always reset DataSet before i do
                // something with it.... i don't know why :-)
                ds.Reset();
                // filling DataSet with result from NpgsqlDataAdapter
                da.Fill(ds);
                // since it C# DataSet can handle multiple tables, we will select first
                dt = ds.Tables[0];
                // connect grid to DataTable
                dataGridView1.DataSource = dt;
                // since we only showing the result we don't need connection anymore
                conn.Close();
            }
            catch (Exception msg)
            {
                // something went wrong, and you wanna know why
                MessageBox.Show(msg.ToString());
                throw;
            }
        }
    }
}

And the effect:

Working example

And, that's it! :D. Practice PostgreSQL and enjoy!

Final words

This article is for those who have used a database for a couple of months (or even years) and would like to taste something different. If you are looking for a solid, stable, secure, and relatively fast database - PostgreSQL is for you. It costs 0.00 $, it has some cool features (look into the documentation and you will find them), and it is free.

I hope that you enjoyed my article :-)

Really final words.... :-)

The project has two dependencies. The first is Npgsql.dll (provider) and the second is MultiCtrls (which is a DLL that was built in my other article). If something is wrong with the sources, please make references by hand (MultiCtrls is bundled with the ZIP file).

License

This article, along with any associated source code and files, is licensed under The BSD License


Written By
Software Developer Agilion Consulting
Poland Poland
I specialize at C#, developing Enterprise solutions. I have some knowledge of ASP.NET MVC - looking forward to use it together with Typescript.

Comments and Discussions

 
Questionnpgsql is missing Pin
Member 1348403714-May-20 21:48
Member 1348403714-May-20 21:48 
Suggestionadd update version Pin
Member 1170495616-Mar-20 17:18
Member 1170495616-Mar-20 17:18 
QuestionI cant insert unicode character e.g. "नमस्कार" in postgresql using .net Pin
hiremath swapnil7-Feb-18 0:07
hiremath swapnil7-Feb-18 0:07 
AnswerRe: I cant insert unicode character e.g. "नमस्कार" in postgresql using .net Pin
RickZeeland12-Feb-18 9:19
mveRickZeeland12-Feb-18 9:19 
QuestionError in NpgsqlConnection Pin
Member 98293293-May-16 2:10
Member 98293293-May-16 2:10 
AnswerRe: Error in NpgsqlConnection Pin
Member 98293293-May-16 2:52
Member 98293293-May-16 2:52 
Questionno using Pin
Dmitry Shevyakov29-Mar-16 1:21
Dmitry Shevyakov29-Mar-16 1:21 
GeneralMy vote of 5 Pin
Member 1108217718-Feb-16 17:25
Member 1108217718-Feb-16 17:25 
Questionok + conn string in webconfig Pin
zpaulo_carraca18-Sep-15 5:08
zpaulo_carraca18-Sep-15 5:08 
GeneralThank you Pin
Member 1041853828-Dec-14 17:12
Member 1041853828-Dec-14 17:12 
QuestionHow to select data from view in asp.net Pin
VinayGandhi17-Dec-14 18:51
professionalVinayGandhi17-Dec-14 18:51 
QuestionGood one Pin
ubbott_b2-Dec-14 21:00
ubbott_b2-Dec-14 21:00 
GeneralMy vote of 3 Pin
ZZKot24-Nov-14 23:01
ZZKot24-Nov-14 23:01 
Questionnot working with me Pin
Member 1103964026-Aug-14 23:22
Member 1103964026-Aug-14 23:22 
Questionchange the following query in postgres Pin
Member 1089159517-Aug-14 19:23
Member 1089159517-Aug-14 19:23 
QuestionExeption when I try to connect Pin
romasimeiz15-Jul-14 22:18
romasimeiz15-Jul-14 22:18 
AnswerRe: Exeption when I try to connect Pin
Adrian Pasik16-Jul-14 0:06
Adrian Pasik16-Jul-14 0:06 
AnswerRe: Exeption when I try to connect Pin
hixohe2-Oct-14 19:57
hixohe2-Oct-14 19:57 
Question64bit? Pin
Swab.Jat23-Jan-14 21:03
Swab.Jat23-Jan-14 21:03 
AnswerRe: 64bit? Pin
Adrian Pasik24-Jan-14 13:43
Adrian Pasik24-Jan-14 13:43 
QuestionIn Listview Pin
rojaldearintok10-Sep-13 0:29
rojaldearintok10-Sep-13 0:29 
QuestionSending List to the postgres db Pin
Member 810061424-Apr-13 7:17
Member 810061424-Apr-13 7:17 
QuestionYour code doesn't work. please help me. Pin
Jesus Christ's servant14-Apr-13 23:44
Jesus Christ's servant14-Apr-13 23:44 
AnswerRe: Your code doesn't work. please help me. Pin
Paweł Granat13-May-14 12:50
Paweł Granat13-May-14 12:50 
GeneralMy vote of 5 Pin
Member 876091425-Feb-13 0:18
Member 876091425-Feb-13 0:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.