C#: Setting up a connection with PostgreSQL in VisualStudio

In this small tutorial I gonna show you how to setup a connection to your local PostgreSQL instance and write/read data to/from a table (in the following I will call this table my_test_table).

You will need:

• Visual Studio Community www.visualstudio.com/
• PostgreSQL for Windows: www.enterprisedb.com  (be sure to select pgAdmin3 during the installation, it will make your life easier)
• The Npgsql package: www.npgsql.org 

Here is the definition of my_table (I used pgAdmin to create it)

CREATE TABLE public.my_table
(
  id integer NOT NULL,
  extref integer NOT NULL,
  val numeric NOT NULL,
  CONSTRAINT my_table_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.my_table
  OWNER TO postgres;


Install Npgsql from the Package Manager Console in VisualStudio:  

Tools -> NuGet Package Manager -> Package Manager Console 

In the command prompt type:

Install-Package Npgsql -Version 3.2.5   (or whatever new version is available here)



In VisualStudio create a simple console application and use this code:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;

namespace TestDB
{
    class Program
    {

        static public void TestDB()
        {

            // Adapt for your configuration (port, Username, password etc)
            var connString = "Host=localhost;Port=5432;Username=postgres;Password=postgres;Database=postgres";

            using (var conn = new NpgsqlConnection(connString))
            {
                conn.Open();

                // Insert some data

                using (var cmd = new NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "INSERT INTO my_table (id, extref, val) VALUES (@id, @extref, @val)";
                    cmd.Parameters.AddWithValue("id", 10);
                    cmd.Parameters.AddWithValue("extref", 10);
                    cmd.Parameters.AddWithValue("val", 150);
                    cmd.ExecuteNonQuery();
                }

                // Retrieve all rows
                Console.WriteLine("Reading from the DB table...");
                using (var cmd = new NpgsqlCommand("SELECT * FROM my_test_table", conn))
                using (var reader = cmd.ExecuteReader())
                    while (reader.Read())
                    {
                        string readLine = string.Format("id={0}, extref={1}, value={2}", reader.GetString(0), reader.GetString(1), reader.GetString(2));
                        Console.WriteLine(readLine);
                    }
            }

        }

        static void Main(string[] args)
        {
            TestDB();

            Console.ReadKey();

        }
    }
}

NOTE

If you try to run this program twice, you will get an exception because you will be trying to insert the same id.. twice and this is not possible because ID is a unique key.
So remember to delete the table's content each time you runt this application.


The list of available parameters for the connection string  is available here


References:
http://www.npgsql.org/doc/index.html