PostgreSQL and C# - Npgsql .NET Data Provider - Getting Started

Npgsql is an open source .NET Data Provider for PostgreSQL 9.x and 8.x. It allows you to access a PostgreSQL database from any .NET application.

PostgreSQL 9.x and 8.x
Microsoft .NET 4.0 and 3.5		Mono (open source .NET)
C# and Visual Basic.NET		ASP.NET and Web Services

Download the data provider at http://pgfoundry.org/projects/npgsql (BSD License).

Among other files, there is Npgsql.dll assembly that will be used in a .NET application and must be available for .NET Runtime.

Let's assume that you have created a C# .NET project. Then copy files Npgsql.dll and Mono.Security.dll into the application folder.

Rigth-click Solution Explorer → References in your project, select Add Reference… → Browse, and select Npgsql.dll and Mono.Security.dll files.

Now you are ready to use the Npgsql assembly, see examples below.

Global Assembly Cache is the central repository for .NET assemblies shared between all applications.

Microsoft provides a gacutil.exe tool to register a shared assembly in the GAC that you can use to register Npgsql.dll and Mono.Security.dll assemblies:

On Windows 7, gacutil.exe is located in Program Files\Microsoft SDKs\Windows\v7.0A\bin (.NET 3.5)

After you have added a reference to Npgsql assembly, you can access a PostgreSQL database from a C# application.

Minimal sample to retrieve rows from a table (without error handling):

   using System;
   using Npgsql;         // Npgsql .NET Data Provider for PostgreSQL
 
   class Sample
   {
     static void Main(string[] args)
     {
        // Specify connection options and open an connection
        NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=postgres;" + 
                                "Password=pwd;Database=postgres;");
        conn.Open();
 
        // Define a query
        NpgsqlCommand cmd = new NpgsqlCommand("select city from cities", conn);
 
        // Execute a query
        NpgsqlDataReader dr = cmd.ExecuteReader();
 
        // Read all rows and output the first column in each row
        while (dr.Read())
          Console.Write("{0}\n", dr[0]);
 
       // Close connection
       conn.Close();
     }
   }

• PostgreSQL Articles and Reference
• PostgreSQL and C# - Working with Result Sets
• Npgsql - User's Manual