Using PostgreSQL from Microsoft.NET

Introduction

PostgreSQL is one of the most important relational database systems in the open source world. It is released under a BSD style license. I've seen a few documents talking about the development of .NET application with PostgreSQL as the back-end server. That's why I'm writing this article to illustrate how you can access to the PostgreSQL database system using C#.

Getting started with the solution

For this solution, we're going to use the PostgreSQL database system which can be downloaded from http://www.postgresql.org/.  You can also find a good documentation in this site. For the creation of the database and its underlying schema, we're going to use pgAdmin III. In order to access to the PostgreSQL database system from Microsoft.NET, we're going to use NpgSQL2 driver for ADO.NET which can be downloaded from http://npgsql.projects.postgresql.org/.  When you unzip the npgsql archive, you can find the driver in the Npgsql.dll library inside the bin directory.

Now let's open the pgAdmin III management console and create a connection to the PostgreSQL database system (see Figure 1).



Figure 1

Next step is to create a test database (see Figure 2).



Figure 2

Then open the Query editor to execute the SQL statement for the creation of the database objects by clicking on the icon on the pgAdmin III toolbar.

Next step is to create a table to store information about the employee of the company (see Listing 1).

CREATE TABLE emp
(
empno integer NOT NULL,
empname varchar(50),
salary numeric(6,2),
CONSTRAINT emp_pkey PRIMARY KEY (empno)
)
WITH (OIDS=FALSE);
ALTER TABLE emp OWNER TO john;

Listing 1

Now let's add some data to the emp table (see Listing 2).

INSERT INTO emp (empno, empname, salary) VALUES (1, 'John', 3400.99);
INSERT INTO emp (empno, empname, salary) VALUES (2, 'Mary', 2400.55);
INSERT INTO emp (empno, empname, salary) VALUES (3, 'Peter', 1900.99);

Listing 2

Now let's open the Visual Studio.NET 2008, and create a Windows Forms Application project (see Figure 3).



Figure 3

The first step of the Windows Forms application is to add a reference to the Npgsql.dll assembly as shown in the Figure 4.



Figure 4

Let's add a dataset to define the schema of the business entities holding the data related to the employees (see Figure 5).



Figure 5

Then right-click on the strongly typed Data Set and add a data table (see Figure 6).



Figure 6

Then define the fields of the Employee data table matching the columns of the employee table in PostgreSQL (see Figure 7).



Figure 7

Now let's bind the Employee business entity and its fields to the main windows form by opening the Data Sources windows and dragging and dropping the Employee node onto the form in Details mode.

Some binding artifacts are automatically created such as an instance of the strongly typed Data Set named dsHumanResources, an instance of the BindingSource class and another instance of the BindingNavigator class.

The last part of the application is the code to access to the back-end database server and to display the data on the form.
The data load code is implemented in the Form_Load event handler of the windows form (see Listing 3).

private void Form1_Load(object sender, EventArgs e)
{
    string strConnString = "Server=remote_server;Port=5432;User Id=john;Password=john;Database=test";
    try
    {
        NpgsqlConnection objConn = new NpgsqlConnection(strConnString);
        objConn.Open();
        string strSelectCmd = "select empno, empname, salary from emp";
        NpgsqlDataAdapter objDataAdapter = new NpgsqlDataAdapter(strSelectCmd, objConn);
        objDataAdapter.Fill(this.dSHumanResources.Employee);
        objConn.Close();
    }
    catch (Exception ex)
    {
        System.Windows.Forms.MessageBox.Show(ex.Message,"Error message",MessageBoxButtons.OK, MessageBoxIcon.Error);
    }

}

Listing 3

Now let's implement the logic to commit the changes on the client data set by coding the event handler of the click event on a new button on the form (see Listing 4).

private void button1_Click(object sender, EventArgs e)
{
    this.employeeBindingSource.EndEdit();
 
    string strConnString = "Server=remote_server;Port=5432;User Id=john;Password=john;Database=test";
    try
    {
        NpgsqlConnection objConn = new NpgsqlConnection(strConnString);
        objConn.Open();

        string strUpdateCmd = "update emp set empname=:empname, salary=:salary where empno=:empno";
        NpgsqlParameter objParameter = null;
        NpgsqlCommand objUpdateCmd = new NpgsqlCommand(strUpdateCmd, objConn);
        objParameter = objUpdateCmd.Parameters.Add(":empno", NpgsqlTypes.NpgsqlDbType.Integer);
        objParameter.SourceColumn = "empno";
        objParameter.SourceVersion = DataRowVersion.Original;
        objParameter = objUpdateCmd.Parameters.Add(":empname", NpgsqlTypes.NpgsqlDbType.Varchar, 50);
        objParameter.SourceColumn = "empname";
        objParameter.SourceVersion = DataRowVersion.Current;
        objParameter = objUpdateCmd.Parameters.Add(":salary", NpgsqlTypes.NpgsqlDbType.Numeric);
        objParameter.SourceColumn = "salary";
        objParameter.SourceVersion = DataRowVersion.Current;

        NpgsqlDataAdapter objDataAdapter = new NpgsqlDataAdapter();
        objDataAdapter.UpdateCommand = objUpdateCmd;
        objDataAdapter.Update(this.dSHumanResources.Employee);
        objConn.Close();
    }
    catch (Exception ex)
    {
        System.Windows.Forms.MessageBox.Show(ex.Message, "Error message", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Listing 4

Conclusion

In this article, I've illustrated how to access a PostgreSQL database using C# and Microsoft.NET framework.