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 licences 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 utilise 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 utilise 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 licence. This licence 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 utilising 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

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 utilise 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.

Listing A
CREATE TABLE "Customers"
(
"LName" varchar(200),
"FName" varchar(200)[],
"CustomerID" int8 NOT NULL DEFAULT nextval('"CustomerID"'::regclass),
"Address" varchar(500),
"City" varchar(100)[],
"State" varchar(2)[],
"ZipCode" int8,
"EmailAddress" varchar(100)[],
CONSTRAINT "PK_Customers" PRIMARY KEY ("CustomerID"),
CONSTRAINT "U_Customers" UNIQUE ("CustomerID")
)

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.

Listing B
<%@ Page language="c#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="Npgsql" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<html><head><title>PostgreSQL Example</title>
<script language="C#" runat="Server">
private void Page_Load(object sender, System.EventArgs e) {
NpgsqlConnectionconn = null;
NpgsqlCommandcomm = null;
NpgsqlDataAdaptersda = null;
DataSetds = new DataSet();
try {
conn = new
NpgsqlConnection("Server=127.0.0.1;Port=5432;Userid=Test;password=test;Protocol=3;SSL=false;
Pooling=true;MinPoolSize=1;MaxPoolSize=20;Encoding=UNICODE;Timeout=15;SslMode=Disable");
comm = new NpgsqlCommand();
comm.Connection = conn;
conn.Open();
comm.CommandType = CommandType.Text;
comm.CommandText = "SELECT * FROM \"Customers\";";
sda = new NpgsqlDataAdapter(comm);
sda.Fill(ds);
dgCustomers.DataSource = ds;
dgCustomers.DataBind();
} catch (Npgsql.NpgsqlException ex) {
Response.Write("Postgre Exception: " + ex.ToString());
} catch (Exception ex) {
Response.Write("Exception: " + ex.ToString());
} finally {
if (conn.State == System.Data.ConnectionState.Open) {
conn.Close();
}
sda.Dispose()
comm.Dispose()
conn.Dispose()
} }
</script></head>
<body>
<form id="frmPostgreSQLExample" method="post" runat="server">
<asp:DataGrid id="dgCustomers" runat="server" />
</form></body></html>

Listing C
<%@ Page language="vb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="Npgsql" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<html><head><title>PostgreSQL Example</title>
<script language="vb" runat="Server">
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles MyBase.Load
Dim conn As NpgsqlConnection
Dim comm As NpgsqlCommand
Dim sda As NpgsqlDataAdapter
Dim ds As DataSet
ds = New DataSet
Try
conn = New NpgsqlConnection("Server=127.0.0.1;Port=5432;Userid=Test;password=test;Protocol=3;SSL=false;Pooling=true;
MinPoolSize=1;MaxPoolSize=20;Encoding=UNICODE;Timeout=15;SslMode=Disable")
comm = New NpgsqlCommand
comm.Connection = conn
conn.Open()
comm.CommandType = CommandType.Text
comm.CommandText = "SELECT * FROM ""Customers"";"
sda = New NpgsqlDataAdapter(comm)
sda.Fill(ds)
dgCustomers.DataSource = ds
dgCustomers.DataBind()
comm.Close()
Catch ex As Npgsql.NpgsqlException
Response.Write("Postgre Exception: " + ex.ToString())
Catch ex As Exception
Response.Write("Exception: " + ex.ToString())
Finally
If (conn.State = System.Data.ConnectionState.Open) Then
conn.Close()
End If
sda.Dispose()
comm.Dispose()
conn.Dispose()
End Try
End Sub
</script></head>
<body>
<form id="frmPostgreSQLExample" method="post" runat="server">
<asp:DataGrid id="dgCustomers" runat="server" />
</form></body></html>

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 capitalised 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 D
<%@ Page language="vb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="Npgsql" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<html><head><title>PostgreSQL Example 2</title>
<script language="vb" runat="Server">
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles MyBase.Load
Dim conn As NpgsqlConnection
Dim comm As NpgsqlCommand
Dim sdr As NpgsqlDataReader
Try
conn = New NpgsqlConnection("Server=127.0.0.1;Port=5432;Userid=Test;password=test;Protocol=3;SSL=false;Pooling=true;MinPoolSize=1;
MaxPoolSize=20;Encoding=UNICODE;Timeout=15;SslMode=Disable")
comm = New NpgsqlCommand
comm.Connection = conn
conn.Open()
comm.CommandType = CommandType.Text
comm.CommandText = "SELECT ""LName"", ""FName"" FROM ""Customers"";"
sdr = comm.ExecuteReader()
Response.Write("<table border=""1"">")
Response.Write("<tr><th>Last Name</th><th>First Name</th></tr>")
While (sdr.Read())
Response.Write("<tr>")
Response.Write("<td>")
Response.Write(sdr(0))
Response.Write("</td>")
Response.Write("<td>")
Response.Write(sdr(1))
Response.Write("</td>")
Response.Write("</tr>")
End While
Response.Write("</table>")
sdr.Close()
Catch ex As Npgsql.NpgsqlException
Response.Write("Postgre Exception: " + ex.ToString())
Catch ex As Exception
Response.Write("Exception: " + ex.ToString())
Finally
If (conn.State = System.Data.ConnectionState.Open) Then
conn.Close()
End If
conn.Dispose()
comm.Dispose()
sdr.Dispose()
End Try
End Sub
</script></head>
<body>
<form id="frmPostgreSQLExample2" method="post" runat="server">
</form></body></html>

Listing E offers the equivalent C# code.

Listing E
<%@ Page language="c#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="Npgsql" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<html><head><title>PostgreSQL Example 2</title>
<script language="C#" runat="Server">
private void Page_Load(object sender, System.EventArgs e) {
NpgsqlConnectionconn = null;
NpgsqlCommandcomm = null;
NpgsqlDataReadersdr = null;
DataSetds = new DataSet();
try {
conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;Userid=Test;password=test;Protocol=3;SSL=false;Pooling=true;
MinPoolSize=1;MaxPoolSize=20;Encoding=UNICODE;Timeout=15;SslMode=Disable");
comm = new NpgsqlCommand();
comm.Connection = conn;
conn.Open();
comm.CommandType = CommandType.Text;
comm.CommandText = "SELECT * FROM \"Customers\";";
sdr = comm.ExecuteReader();
Response.Write("<table border=\"1\">");
Response.Write("<tr><th>Last Name</th><th>First Name</th></tr>");
while (sdr.Read()) {
Response.Write("<tr>");
Response.Write("<td>");
Response.Write(sdr[0]);
Response.Write("</td>");
Response.Write("<td>");
Response.Write(sdr[1]);
Response.Write("</td>");
Response.Write("</tr>");
}
Response.Write("</table>");
sdr.Close();
} catch (Npgsql.NpgsqlException ex){
Response.Write("Postgre Exception: " + ex.ToString());
} catch (Exception ex) {
Response.Write("Exception: " + ex.ToString());
} finally {
if (conn.State == System.Data.ConnectionState.Open) {
conn.Close();
}
conn.Dispose();
comm.Dispose();
sdr.Dispose();
} }
</script> </head><body>
<form id="frmPostgreSQLExample2" method="post" runat="server">
<asp:DataGrid id="dgCustomers" runat="server" />
</form></body></html>

Another option
Microsoft pushes SQL Server as the database solution to solve every business need, but its cost can be prohibitive for smaller organisations. 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 utilise 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.

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