Easily integrate PostgreSQL with .NET

The PostgreSQL database server provides a reliable, robust database solution at no cost. Tony Patton walks you through the steps of using PostgreSQL as the backend for .NET applications.

The open source software community has had a huge impact on the software industry. The quality and type of applications available via open source licenses are impressive.

While MySQL has garnered most of the attention with respect to open source database solutions, there are plenty of other options, such as PostgreSQL. This week I take a look at PostgreSQL and explain how you can utilize it as the backend for .NET applications.

PostgreSQL

I was exposed to PostgreSQL on a recent project where the client moved from SQL Server 7.0 to PostgreSQL. While they preferred an open source database server (running on Red Hat Linux), the front-end development continued to utilize the .NET Framework. I was a bit anxious about learning a new database system, but PostgreSQL is impressive.

PostgreSQL has been around (in one form or another) for more than 15 years. It runs on all of the major operating systems, including the recent release for the Windows platform. While MySQL only recently added support for stored procedures, PostgreSQL includes full support for foreign keys, joins, views, triggers, and stored procedures. Its SQL implementation (called PL/pgSQL) strongly conforms to the ANSI-SQL 92/1999 standards. It is similar to Oracle's PL/SQL.

It amazes me that such a powerful database system is freely available according to the BSD license. This license gives you the freedom to use, modify, and distribute PostgreSQL in any form you like whether it's open or closed source.

The transition from SQL Server 7.0 to PostgreSQL was much smoother than anticipated, but there are differences to consider if you're moving from SQL Server's T-SQL language to PostgreSQL's PL/pgSQL.

A few noteworthy items include the fact that PL/pgSQL does not support brackets around object names, and object owners are not used (like dbo or a username). PL/pgSQL lines/commands are terminated with semicolons, and double quotes are used to enclose object names. PL/sgSQL adheres to the SQL standard, while Microsoft's T-SQL does not; as you can see, there are even more differences than I can cover in this article. Let's take a closer look at utilizing the PostgreSQL platform from .NET code.

.NET connectivity

Npgsql is a freely available .NET data provider for PostgreSQL. It is an installation option for the most recent version of PostgreSQL (8.1.4). It is also available in a separate download with the following options:

  • Npgsql/bin/docs—Documentation
  • Npgsql/bin/docs/apidocs—API Documentation
  • Npgsql/bin/ms1.1—Npgsql compiled for Microsoft .NET 1.1
  • Npgsql/bin/mono—Npgsql compiled for Mono
Weekly .NET tips in your inbox
TechRepublic's free .NET newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET.
Automatically sign up today!

The great thing about .NET data providers is familiarity with one simplifies using other providers since the approach is the same. Here is a breakdown of the most common Npgsql classes:

  • NpgsqlConnection—Used to establish connection to the PostgreSQL database.
  • NpgsqlCommand—Send commands to the PostgreSQL database.
  • NpgsqlDataAdapter—PostgreSQL version of the data adapter class.
  • NpgsqlDataReader—PostgreSQL version of the data reader class.
  • NpgsqlParameter—Allows you to work with stored procedure parameters.
  • NpgsqlException—Exceptions specific to the PostgreSQL environment.

The Npgsql data provider is used in your code by adding the following line in your C# code:

using Npgsql;

Or, adding this line in your VB.NET code:

Imports Npgsql

The Imports page directive is used with ASP.NET pages (without codebehind files); it is demonstrated in the upcoming examples. You can also put the Npgsql assembly in the Global Assembly Cache (GAC). Npgsql is strongly signed, so gacutil can be used to install it via the following command:

gacutil -i Npgsql.dll

The approach to interfacing with a PostgreSQL database server is the same as working with another database platform like SQL Server. The key difference is the syntax of the commands sent to the database server, since you must utilize PL/pgSQL syntax. The following example establishes a connection to a PostgreSQL server and reads data from a table called Customers. The Customers table was created with the PL/pgSQL in Listing A.

A couple of notes on this PL/pgSQL listing:

  • Double-quotes are used for object names (table, columns, etc.).
  • The CustomerID field is the primary key, thus it is unique (constraints) and uses a sequence to assign values (like the autonumber feature in SQL Server).

Listing B contains the code to read all values from the Customers table. Listing C contains the VB.NET equivalent.

The code establishes a connection to the database server, issues a command, and displays the data in a DataGrid component. A key aspect of the code is the connection string used to hook up with the PostgreSQL server. In this case, I'm using a local test server on the same machine (127.0.0.1 loopback address). Every database connection string seems to be different, so make sure it is correct to properly interact with the database. Another nuance with PostgreSQL is the case-sensitive nature of its PL/pgSQL language when dealing with database objects, so the following two statements are not equal:

SELECT * FROM "Customers";
SELECT * FROM "customers";

The table was created as Customers, so it must be capitalized to access it. This caused some confusion (on my part) when first working with the database environment.

The code should be relatively straightforward if you have done much database programming with .NET. To further illustrate the point, the VB.NET in Listing D loops through all the data in the Customers table and displays in a simple HTML table. Listing E offers the equivalent C# code.

Another option

Microsoft pushes SQL Server as the database solution to solve every business need, but its cost can be prohibitive for smaller organizations. Open source solutions like PostgreSQL provide a robust database platform with no associated licensing costs. Thankfully, the open source community has developed a .NET data provider that allows you to easily utilize a PostgreSQL server in .NET applications.

Have you taken advantage of open source databases like PostgreSQL or MySQL in your .NET applications? If so, share your experiences in the article discussion forum.

Miss a column?

Check out the .NET Archive, and catch up on the most recent editions of Tony Patton's column.

Tony Patton began his professional career as an application developer earning Java, VB, Lotus, and XML certifications to bolster his knowledge.