Welcome to the Npgsql
.Net Data Provider for Postgresql Project Home Page

Example Programs

On this section, you will find some example programs done in Windows Forms, ASP.Net and others showing Npgsql usage and help you to start coding with Npgsql.

Example 1

This is a Windows Forms program. Note that in order to run this example you have to create a table as follow:

CREATE TABLE npdata (key TEXT PRIMARY KEY, ndata INT8);

//
// NpGrid.cs
//
// Date: 2006.11.09
// author: Hiroshi Saito(z-saito@guitar.ocn.ne.jp)
//
// Description: This is tried in PostgreSQL 8.1.
// Please do the following table definition beforehand you start testing.
// CREATE TABLE npdata (key TEXT PRIMARY KEY, ndata INT8);
//
// Copyright (C) 2002-2006 The Npgsql Development Team.
//

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using Npgsql;

public class NpGridcs : Form
{
    private NpgsqlConnection conn;
    private NpgsqlDataAdapter NpAdapter;
    private NpgsqlParameter NpParam = null;
    private System.Data.DataSet dset = null;
    private System.Data.DataTable dtsource = null;
    private System.Windows.Forms.Button ok, exit;
    private System.Windows.Forms.ToolTip tipok, tipexit;
    private System.Windows.Forms.DataGrid npgridfrm;

    string query = "SELECT * FROM npdata";


    static void Main()
    {
        Application.EnableVisualStyles();
        Application.Run(new NpGridcs());
    }

    public NpGridcs()
    {
        InitializeComponent();
    }

    // This method creates the user interface components.

    public void InitializeComponent()
    {
        this.ClientSize = new System.Drawing.Size(640, 400);
        npgridfrm = new DataGrid();
        npgridfrm.Location = new Point(10, 10);
        npgridfrm.Size = new Size(620, 350);
        this.Text = "Simple example of data grid by Npgsql";
        this.Controls.Add(npgridfrm);

        ok = new Button();
        ok.Location = new Point(10, 365);
        ok.Size = new Size(70, 25);
        ok.TabIndex = 1;
        ok.Text = "&Ok";
        this.Controls.Add(ok);
        ok.Click += new System.EventHandler(button_Click);
        tipok = new ToolTip();
        tipok.SetToolTip(ok, "Updated refreshing"); 

        exit = new Button();
        exit.Location = new Point(95, 365);
        exit.Size = new Size(70, 25);
        exit.TabIndex = 1;
        exit.Text = "&Exit";
        this.Controls.Add(exit);
        exit.Click += new System.EventHandler(button_Click);
        tipexit = new ToolTip();
        tipexit.SetToolTip(exit, "End of this program"); 

        ConnectToData();

        // Here we connect Npgsql datasource "dtsource" created in previous method with our form.
        try
        {
            npgridfrm.DataSource = dtsource;
            npgridfrm.SetDataBinding(dset, "npdata");
            dtsource.RowChanged += new DataRowChangeEventHandler(Row_Changed);
        }
        catch (Exception ex) { }

    }

    // This method is responsible to get the data from server and 
    // setup delete, update and insert commands for table.

    public void ConnectToData()
    {
        string DSN;
        Npgsql.Design.ConnectionStringEditorForm Ndesign = new Npgsql.Design.ConnectionStringEditorForm();
        Ndesign.ShowDialog();
        DSN = Ndesign.ConnectionString.ToString();
        if (DSN == "")
            return;

        conn = new NpgsqlConnection(DSN);
        dset = new DataSet("npdata");
        NpAdapter = new NpgsqlDataAdapter();
        NpAdapter.SelectCommand = new NpgsqlCommand(query, conn);
        NpAdapter.Fill(dset, "npdata");
        dtsource = dset.Tables["npdata"];

        deleteCmd();
        updateCmd();
        insertCmd();
    }

    // Setups the delete command.
    public void deleteCmd()
    {
        string query = "DELETE FROM npdata WHERE key = @key";

        NpAdapter.DeleteCommand = new NpgsqlCommand(query, conn);

        NpParam = NpAdapter.DeleteCommand.Parameters.Add("@key", NpgsqlTypes.NpgsqlDbType.Text);
        NpParam.SourceColumn = "key";
        NpParam.SourceVersion = DataRowVersion.Original;

    }
    
    // Setups the update command.
    public void updateCmd()
    {
        string query = "UPDATE npdata SET key = @key, ndata = @ndata WHERE key = @key";

        NpAdapter.UpdateCommand = new NpgsqlCommand(query, conn);
        
        NpParam = NpAdapter.UpdateCommand.Parameters.Add("@key", NpgsqlTypes.NpgsqlDbType.Text);
        NpParam.SourceColumn = "key";
        NpParam.SourceVersion = DataRowVersion.Original;

        NpParam = NpAdapter.UpdateCommand.Parameters.Add("@ndata", NpgsqlTypes.NpgsqlDbType.Bigint);
        NpParam.SourceVersion = DataRowVersion.Current;
        NpParam.SourceColumn = "ndata";

    }

    // Setups the insert command.
    public void insertCmd()
    {
        string insertQuery = "INSERT INTO npdata VALUES (@key, @ndata)";
        NpAdapter.InsertCommand = new NpgsqlCommand(insertQuery, conn);

        NpParam = NpAdapter.InsertCommand.Parameters.Add("@key", NpgsqlTypes.NpgsqlDbType.Text);
        NpParam.SourceColumn = "key";
        NpParam.SourceVersion = DataRowVersion.Current;

        NpParam = NpAdapter.InsertCommand.Parameters.Add("@ndata", NpgsqlTypes.NpgsqlDbType.Bigint);
        NpParam.SourceVersion = DataRowVersion.Current;
        NpParam.SourceColumn = "ndata";

    }

    public void button_Click(object sendobj, EventArgs evarg)
    {
        if (sendobj == ok)
            UpdateObj();
        else
        if (sendobj == exit)
            this.Dispose();
    }
    

    // Process event generated by form when a row changed.
    private void Row_Changed(object chgobject, DataRowChangeEventArgs evarg)
    {
        UpdateObj();
    }

    // This method updates the dataset with modified data from form. It uses NpgsqlDataAdapter which verifies what rows has been modified, 
deleted and inserted and send the proper commands to database server.
    public void UpdateObj()
    {
        try
        {
            NpAdapter.Update(dset, "npdata");
        }
        catch (Exception ex) 
        {
            MessageBox.Show(ex.ToString());
        }
    }

}

Example 2

This is a ASP.Net sample which allows you to send queries to database.

<!--
//
// Npgsqlw.aspx
// Date: 2006.11.16
// author: Hiroshi Saito(z-saito at guitar dot ocn dot ne dot jp)
// Description: This is tried in PostgreSQL 8.1.
// Attention: Please note it enough for the reasons of not preferable on security in use.
//
-->

<%@ Page Language="C#"%>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="Npgsql" %>

<script language="C#" runat="server">
    
    String url = "localhost";
    String dbn = "template1";
    String usr = "postgres";
    
    NpgsqlConnection cnDB;
    
    protected void Page_Load(object sender, EventArgs e)
    {
        String constr = "DATABASE=" + dbn + ";SERVER=" + url + ";UID=" + usr + ";Encoding=UNICODE;";
        cnDB = new NpgsqlConnection(constr);
        try
        {
            cnDB.Open();
        }
        catch (NpgsqlException ex)
        {
            Query.Text = constr;
            Result.Text = ex.ToString();
            return;
        }
        
        Result.Text = "Ready.";
    }

    protected void Query_TextChanged(object sender, EventArgs e)
    {
        NpgsqlCommand command = new NpgsqlCommand();
        NpgsqlDataReader dr;

        Result.Text = "";
        
        if (cnDB == null)
        {
            Result.Text = "Sorry, The connection has been broken off.";
            return;
        }
        
        command.Connection = cnDB;
        command.CommandText = Query.Text;

        try
        {
            dr = command.ExecuteReader();
        }
        catch(NpgsqlException ex)
        {
            Result.Text = ex.ToString();
            return;
        }
        
        //  this is empty result check.
        if (!dr.HasRows)
                return;

        do
        {
            Int32 j, i;
            j = dr.FieldCount;
            DataTable dt = dr.GetSchemaTable();
            DataRowCollection schemarows = dt.Rows;
            
            for (i = 0; i < j; i++)
            {
                Result.Text += schemarows[i][0] + "\t";
            }
            
            Result.Text += "\r";
            
            while(dr.Read())
            {
                for (i = 0; i < j; i++)
                {
                    Result.Text +=  dr[i] + "\t";
          }
                
                Result.Text += "\r";
            }
        } while (dr.NextResult());
      
    }  
</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Npgsql (.NET Data Provider)</title>
</head>
<body style="font-family: Times New Roman">
    <form id="form1" runat="server">
    <div style="text-align: left">
        <span style="color: #3399ff"><span style="font-size: 24pt"><strong>
        Npgsql (.NET Data Provider)</strong> </span></span>
        <br />
        <asp:TextBox ID="Query" runat="server" Height="24px" Width="663px" OnTextChanged="Query_TextChanged" Font-Size="14pt"></asp:TextBox><br />
        <br />
        <asp:TextBox ID="Result" runat="server" BorderStyle="Solid" Height="359px" TextMode="MultiLine"
            Width="662px" Font-Size="9pt" BackColor="#FFE0C0"></asp:TextBox><br />
        <br />
        <span style="font-size: 24pt"><strong>
        Powered by PostgreSQL</strong></span></div>
    </form>
</body>
</html>