<title>Npgsql: User's Manual</title>

<meta http-equiv="content-Type" content="text/html; charset=utf-8">

<meta http-equiv="content-Style-Type" content="text/css">

<meta http-equiv="expires" content="">

<meta name="category" content="IT/Database/PostgreSQL/Npgsql/Docs/Internal">

<meta name="filename" content="UserManual.html">

<meta name="date" content="2002-06-29">

<meta name="author" content="Francisco Jr.">

<meta name="robots" content="nofollow">

<meta name="keywords" content="IT; Database; PostgreSQL; Npgsql; Communication; State Machine; Documentation; .Net; C#; ADO.Net; Mono;">

<meta name="description" content="This document describes the User's Manual of Npgsql.">

<link rel="stylesheet" href="global.css">

<script language="JavaScript" src="global.js"></script>

table.std {

border-bottom: 2 solid silver;

border-right: 2 solid silver;

line-height: 140%;

}

tr.tableheader {

background-color: PowderBlue;

color: darkblue;

text-align: center;

font-weight: bold;

font-size: larger;

height: 40;

}

tr.stateheader {

background-color: AliceBlue;

color: darkblue;

text-align: center;

font-weight: bold;

padding-top: 8;

padding-bottom: 8;

height: 40;

}

tr.columnheader {

text-align: center;

font-weight: normal;

height-

}

tr.std {

text-align: left;

vertical-align: top;

}

td.std {

border-top: 2 solid silver;

border-left: 2 solid silver;

padding-right: 5;

padding-left: 5;

padding-top: 5;

padding-bottom: 5;

}

p.headnotes {

font-size:smaller;

margin-top: 0;

margin-bottom: 0;

}

<h1 align="center">Npgsql: User's Manual</h1>

<center> Copyright &copy; <a href="http://gborg.postgresql.org/project/npgsql/cvs/co.php/Npgsql/docs/thenpgsqldevelopmentteam.htm">The Npgsql Development Team </a></center>

<hr />

<p />

<p class="headnotes">Last update: $Date$ by $Author$</p>

<p class="headnotes">Category: External documentation</p>

<p class="headnotes">Intended Audience: Npgsql Users</p>

<h2> 1. What is Npgsql? </h2>

<p> <a href="http://pgfoundry.org/projects/npgsql/">Npgsql </a> is a .Net Data Provider for the <a href="http://www.postgresql.org">PostgreSQL Database Server</a>. </p>

<p> It allows a .Net client application (Console, WinForms, ASP.NET, Web Services...) to send and receive data with a PostgreSQL server. It is actively developed based on the

guidelines specified in the .Net documentation.</p>

<h2> 2. How to get and compile Npgsql </h2>

<h3> 2.1 Binary package </h3>

<p> You can download Npgsql compiled for MS.Net and Mono in the <a href="http://pgfoundry.org/frs/?group_id=1000140">Files section </a> of the project.</p>

<p> Inside this package, you will find the following directory layout: </p>

<p> Npgsql/bin/docs - Documentation </p>

<p> Npgsql/bin/docs/apidocs - API Documentation </p>

<p> Npgsql/bin/ms1.1 - Npgsql compiled for MS.Net 1.1 </p>

<p> Npgsql/bin/mono - Npgsql compiled for Mono </p>

<p>As soon as Npgsql is released on other platforms/versions, they will be added accordingly to this layout.<p>

<h3> 2.2 Installing binary package </h3>

<p> In order for the .Net Runtime to locate the Npgsql.dll library, this file must be placed in your application directory- unless you specify another

directory as a path to private components through a configuration file (using the probing element). Please see the .Net docs for information

on how the runtime probes (locates) assemblies to be loaded. Specifically, see the section called "Path to Private Components" </p>

<p> In ASP.NET and Web Services .Net Applications, there must be a directory called "bin" below the ASP.NET root application directory.

For example, if the application directory is called "ASPNETApplication", then Npgsql.dll and Mono.Security.dll must be placed in the "ASPNETApplication\bin" directory.

If these files are not in the right directory, you can expect to see the compiler generate errors on code that uses Npgsql classes.</p>

<p> Alternatively, you can put the Npgsql assembly in the Global Assembly Cache (GAC). Since version 0.4, Npgsql is strongly signed- meaning that you can

use "gacutil" to install it. Simply issue the following command:</p>

<pre class="CodeBox">

gacutil -i Npgsql.dll

<p> Please refer to "Installing an Assembly in the Global Cache Assembly" section of MSDN docs for more information. Use of the GAC has implications

that you should fully understand before going down this path.

<p> Note that placing Npgsql in the GAC is required for Npgsql design time support in Visual Studio .Net.

<p> (Npgsql compiled for Mono doesn't require Mono.Security.dll as it's already integrated in the Mono Runtime.)</p>

<p> Once you copy or set up the assemblies, you're ready to try the examples- jump to <a href="#section3">section 3</a>. </p>

<h3> 2.3 Getting Npgsql from CVS </h3>

<p> To get Npgsql from CVS, use the following info in your CVS client: </p>

Server: cvs.pgfoundry.org

<br />

Repository: /cvsroot/npgsql

<br />

Module name: Npgsql2

<br />

User: anonymous

<br />

Password:

<p> If you are using CVS from a command line, use this command: </p>

<pre class="CodeBox">

cvs -d :pserver:anonymous@cvs.pgfoundry.org:/cvsroot/npgsql login

<br />

Hit the Enter key when prompted for a password (none required):

<br />

<pre class="CodeBox">

cvs -d :pserver:anonymous@cvs.pgfoundry.org:/cvsroot/npgsql checkout Npgsql2

<p> The code will begin transferring: </p>

<pre class="CodeBox">

$ cvs -d :pserver:anonymous@cvs.pgfoundry.org:/cvsroot/npgsql login

Logging in to :pserver:anonymous@cvs.pgfoundry.org:2401/cvsroot/npgsql

CVS password:

$ cvs -d :pserver:anonymous@cvs.pgfoundry.org:/cvsroot/npgsql co Npgsql2

cvs checkout: Updating Npgsql2

cvs checkout: Updating Npgsql2/admin

U Npgsql2/admin/release.pl

cvs checkout: Updating Npgsql/docs

U Npgsql2/docs/Npgsql.zargo

U Npgsql2/docs/NpgsqlConnectionStateMachine.png

U Npgsql2/docs/SuggestedReadings.htm

...

<h3> 2.4 Compiling Npgsql</h3>

<p>The officially supported method for compiling Npgsql is <a href="http://nant.sf.net">NAnt</a> version 0.86 and above. Version 0.86 is needed because it has support for compiling sattelite resources assemblies.</p>

<p>Simply run <i>nant</i> from the Npgsql/src/Npgsql folder and you're done. It will create a folder named "build"; see the "build/ms" folder for the resulting assemblies.</p>

<p>We also have project files for MonoDevelop, Visual Studio.Net and Visual Studio.Net 2008.</p>

<h3> 2.5 Running Npgsql Unit tests</h3>

<p> In order to be able to run nunit tests you must first setup your tests database.</p>

<p> First, create a database called npgsql_tests:</p>

<pre class="CodeBox">

createdb npgsql_tests

<p> Later, create an user called npgsql_tests with password npgsql_tests:</p>

<pre class="CodeBox">

createuser -NP npgsql_tests

<p> Now, run the scripts to add tables, functions, data etc. These scripts are located in testsuite/noninteractive folder.</p>

<p>To run nUnit tests, simply run:

<pre class="CodeBox">

nant tests

</pre>

<h2> <a name="section3"></a>3. Npgsql Usage </h2>

<p> This section explains Npgsql usage in a .Net application (Windows or ASP.NET). If you have experience developing data access applications using the

Sql Server, OleDB or ODBC.NET providers, you will find that Npgsql is very similar, in most respects equally or more robust, and backed by an active community.</p>

<p> In order to use Npgsql, the PostgreSQL server must be listening to TCP/IP connections. TCP connections are enabled by default on 8.0 + servers. Previous versions should have postmaster started with the "-i" option. Check PostgreSQL Documentation for details: <a href="http://www.postgresql.org/docs/7.4/static/postmaster-start.html">http://www.postgresql.org/docs/7.4/static/postmaster-start.html</a> </p>

<p> Note: Npgsql is still under development. Only features currently supported will be demonstrated. As Npgsql matures, more functionality will become available. </p>

<h3> Adding required namespaces to your source file </h3>

<p> First, in order to access Npgsql objects more easily (i.e. Intellisense in Visual Studio .Net), you need to instruct the compiler

to use the Npgsql namespace. As you manipulate data retrieved by Npgsql, classes in System.Data will also be required.

In C#, add this directive to the appropriate page or class: </p>

<pre class="CodeBox">

using System.Data;

using Npgsql;

<p> If you are using ASP.NET without code-behind files, you may need to add the following lines in top of your ASPX pages:

<pre class="CodeBox">

<%@ Assembly name="System.Data" %>

<%@ Assembly name="Npgsql" %>

You can read more details here <a href="http://www.mono-project.com/FAQ:_ASP.NET">FAQ Mono Page about ASP.NET</a>

<h3> Establishing a connection </h3>

<p>To establish a connection to a server located at IP 127.0.0.1, port 5432,

as user "joe", with password "secret", on database "joedata", open NpgsqlConnection with the following connection string:</p>

<pre class="CodeBox">

using System;

using System.Data;

using Npgsql;

public class NpgsqlUserManual

{

public static void Main(String[] args)

{

NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=joe;Password=secret;Database=joedata;");

conn.Open();

conn.Close();

}

}

<h3> Connection String parameters </h3>

<p> When establishing a connection, NpgsqlConnection accepts many parameters which modify its behavior. Here is the list of current parameters you can tweak: (From NpgsqlConnection source) </p>

<div class="CodeBox">

<p>Gets or sets the string used to connect to a PostgreSQL database.</p>

<p>Valid values are:</p>

<dl>

<dt><code>Server</code></dt><dd>Address/Name of PostgreSQL Server</dd>

<dt><code>Port</code></dt><dd>Port to connect to</dd>

<dt><code>Protocol</code></dt><dd>Protocol version to use, instead of automatic; Integer 2 or 3</dd>

<dt><code>Database</code></dt><dd>Database name. Defaults to user name if not specified</dd>

<dt><code>User Id</code></dt><dd>User name</dd>

<dt><code>Integrated Security</code></dt><dd>Set to use windows integrated security. Default=false</dd>

<dt><code>Password</code></dt><dd>Password for clear text authentication</dd>

<dt><code>SSL</code></dt><dd>True or False. Controls whether to attempt a secure connection. Default = False</dd>

<dt><code>Pooling</code></dt><dd>True or False. Controls whether connection pooling is used. Default = True</dd>

<dt><code>MinPoolSize</code></dt><dd>Min size of connection pool. Min pool size, when specified, will make NpgsqlConnection pre-allocate the specified number of connections with the server. Default: 1</dd>

<dt><code>MaxPoolSize</code></dt><dd>Max size of connection pool. Pooled connections will be disposed of when returned to the pool if the pool contains more than this number of connections. Default: 20</dd>

<dt><code>Encoding</code></dt><dd>Obsolete. Always returns the string "Unicode", and silently ignores attempts to set it.</dd>

<dt><code>Timeout</code></dt><dd>Time to wait for connection open in seconds. Default is 15.</dd>

<dt><code>CommandTimeout</code></dt><dd>Time to wait for command to finish execution before throw an exception. In seconds. Default is 20.</dd>

<dt><code>Sslmode</code></dt>

<dd>Mode for ssl connection control. Can be one of the following:

<dl>

<dt><code>Prefer</code></dt><dd>If it is possible to connect via SLL, SSL will be used.</dd>

<dt><code>Require</code></dt><dd>If an SSL connection cannot be established, an exception is thrown.</dd>

<dt><code>Allow</code></dt><dd>Not supported yet; connects without SSL.</dd>

<dt><code>Disable</code></dt><dd>No SSL connection is attempted.</dd>

</dl>

The default value is "Disable".

</dd>

<dt><code>ConnectionLifeTime</code></dt><dd>Time to wait before closing unused connections in the pool, in seconds. Default is 15.</dd>

<dt><code>SyncNotification</code></dt><dd>Specifies if Npgsql should use synchronous notifications</dd>

<dt><code>SearchPath</code></dt><dd>Changes search path to specified and public schemas.</dd>

<dt><code>Preload Reader</code></dt>

<dd>

<p>

If set to "true" (the default is "false") this causes datareaders to be loaded in their entirety before ExecuteReader

returns.

</p>

<p>

This results in less performance (especially in the case of very large recordsets, in which case the level of performance could

be intolerable), but is left as an option to cover a particular potential backwards-compatibility issue with previous versions of

Npgsql.

</p>

<p>

According to the ADO.NET documentation, while an IDataReader is open the IDbConnection used to obtain it is "busy" and cannot

be used for any other operations (with a few documented exceptions to this rule). Npgsql enforces this rule and hence while an

NpgsqlDataReader is open most other operations on the NpgsqlConnection used to obtain it will result in an

InvalidOperationException (Npgsql relaxes the rule in allowing you to use a connection if an NpgsqlDataReader has been

read to the end of it's resultset(s) even if it hasn't been closed, since at this point it is no longer using any resources

from the connection).

</p>

<p>

Previously however, Npgsql allowed users to completely ignore this rule. This was entirely a side-effect of internal

implementation issues, and strictly speaking has never been supported (since it always violates the ADO.NET

specification) but that will be little comfort should you suddenly find previously working code is broken. Hence if

you find a problem with this change you can use this connection-string option to move back to the previous behaviour.

</p>

<p>

If you do use it however, you should do so as a stop-gap before fixing the code in question for two reasons:

</p>

<ol>

<li>Performance and, particularly, <a href="preloadScalabilty.html">scalability</a> is much better without this option.</li>

<li>Such code will be likely to fail, should you at any point want to extend to support a different data provider.</li>

</ol>

</dd>

<dt><code>Use Extended Types</code></dt>

<dd>

<p>

This option affects whether DataAdaptors expect to use the .NET System.DateTime type or the Npgsql date and time

types like NpgsqlTimeStamp which has functionality and ranges beyond that of System.DateTime. Either option allows

both the Npgsql and System types to be used, but if set to "true" DataAdaptors will expect to be passed the specific

Npgsql type for the field in question, whereas if set to "false" they will expect System.DateTime.

</p>

<p style="color: red;">

This option is experimental and will hopefully its impact will be reduced or removed in later releases.

</p>

<p>

The default is "false".

</p>

</dd>

<dt><code>Compatibility</code></dt>

<dd>

<p>

This version is intended as a simpler method of dealing with breaking changes to adding yet more and more connection

string options. It takes a single value in the form of a version number (a.b[.c[.d]]). Changes that could break

existing code will, when possible, copy the behaviour prior of version number. The first such version is

2.0.2.1, so a value of "2.0.2" would not have its new behaviour.

<table>

<thead><tr><th scope="col">Version</th><th scope="col">Behaviour</th></tr>

<tbody>

<tr>

<th scope="row">2.0.2</th>

<td>

<ol>

<li>GetOrdinal will return -1 if the field name is not found.</li>

<li>GetOrdinal is kana-width sensitive.</li>

</ol>

</td>

</tr>

<tr>

<th scope="row">2.0.2.1</th>

<td>

<ol>

<li>GetOrdinal will throw IndexOutOfRangeException if the field name is not found.</li>

<li>GetOrdinal is kana-width insensitive.</li>

</ol>

</td>

</tr>

</tbody>

</table>

</p>

</dd>

<h3> Using NpgsqlCommand to add a row in a table </h3>

<p>The previous example doesn't do anything useful. It merely connects to the database and disconnects. If there is an error, a NpgsqlException is thrown.

Now, suppose you have a table called "table1" with two fields, "fielda" and "fieldb", both of type int. If you want to insert tuple (1, 1) in this table you can send the

insert statement:</p>

<pre class="CodeBox">

using System;

using System.Data;

using Npgsql;

public static class NpgsqlUserManual

{

public static void Main(String[] args)

{

NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=joe;Password=secret;Database=joedata;");

conn.Open();

NpgsqlCommand command = new NpgsqlCommand("insert into table1 values(1, 1)", conn);

Int32 rowsaffected;

try

{

rowsaffected = command.ExecuteNonQuery();

Console.WriteLine("It was added {0} lines in table table1", rowsaffected);

}

finally

{

conn.Close();

}

}

}

ExecuteNonQuery() is ideally suited for insert and update queries because it returns an integer indicating the number of rows affected

by the last operation.

<h3> Getting a single result value using the NpgsqlCommand.ExecuteScalar() method </h3>

<p> In some scenarios, you only need to retrieve a single value (scalar) from a function. Use the ExecuteScalar()

method on a Command object :</p>

<pre class="CodeBox">

using System;

using System.Data;

using Npgsql;

public static class NpgsqlUserManual

{

public static void Main(String[] args)

{

NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=joe;Password=secret;Database=joedata;");

conn.Open();

NpgsqlCommand command = new NpgsqlCommand("select version()", conn);

String serverversion;

try

{

serverversion = (String)command.ExecuteScalar();

Console.WriteLine("PostgreSQL server version: {0}", serverversion);

}

finally

{

conn.Close();

}

}

}

You may also use ExecuteScalar against queries that return a recordset, such as "select count(*) from table1".

However, when calling a function that returns a set of one or more records, only the first column of the first row

is returned (DataSet.Tables[0].Rows[0][0]).

In general, any query that returns a single value should be called with Command.ExecuteScalar.

<h3> Getting a full result set with NpgsqlCommand.ExecuteReader() method and NpgsqlDataReader </h3>

<p> There are several ways to return recordsets with Npgsql. When you'd like to pass a SQL statement as

command text and access the results with a memory-efficent DataReader, use the ExecuteReader() method of the NpgsqlCommand object:</p>

<pre class="CodeBox">

using System;

using System.Data;

using Npgsql;

public static class NpgsqlUserManual

{

public static void Main(String[] args)

{

NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=joe;Password=secret;Database=joedata;");

conn.Open();

NpgsqlCommand command = new NpgsqlCommand("select * from tablea", conn);

try

{

NpgsqlDataReader dr = command.ExecuteReader();

while(dr.Read())

{

for (i = 0; i < dr.FieldCount; i++)

{

Console.Write("{0} \t", dr[i]);

}

Console.WriteLine();

}

}

finally

{

conn.Close();

}

}

}

Note that you can 'daisy chain' select statements in a command object's commandtext to retrieve more than one

record set: "select * from tablea; select * from tableb"

<h3> Using parameters in a query </h3>

<p> Parameters let you dynamcially insert values into SQL queries at run-time. Generally speaking, parameter

binding is the best way to build dynamic SQL statements in your client code. Other approaches, such as basic string concatenation,

are less robust and can be vulerable to SQL injection attacks.

To add parameters to your SQL query string, prefix the paramter name with ":". The example below uses a parameter named value1 (see ":value1").</p>

<pre class="CodeBox">

using System;

using System.Data;

using Npgsql;

public static class NpgsqlUserManual

{

public static void Main(String[] args)

{

using(NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=joe;Password=secret;Database=joedata;"))

{

conn.Open();

// Declare the parameter in the query string

using(NpgsqlCommand command = new NpgsqlCommand("select * from tablea where column1 = :value1", conn))

{

// Now add the parameter to the parameter collection of the command specifying its type.

command.Parameters.Add(new NpgsqlParameter("value1", NpgsqlDbType.Integer));

// Now, add a value to it and later execute the command as usual.

command.Parameters[0].Value = 4;

using(NpgsqlDataReader dr = command.ExecuteReader())

{

while(dr.Read())

{

for (i = 0; i < dr.FieldCount; i++)

{

Console.Write("{0} \t", dr[i]);

}

Console.WriteLine();

}

}

}

}

}

}

<p>You can also send a parameterized query to the server using NpgsqlParamenter and NpgsqlParamenterCollection objects.)

This code assumes a table called "tablea" with at least one column named "column1" of type int4.</p>

<h3> Using prepared statements</h3>

<p> The Prepare method lets you optimize the performance of frequently used queries. Prepare() basically "caches"

the query plan so that it's used in subsequent calls.

(Note that this feature is only available in server 7.3+ versions. If

you call it in a server which doesn't support it, Npgsql will silently ignore it.)

Simply call the Prepare() method of the NpgsqlCommand before query execution:</p>

<pre class="CodeBox">

using System;

using System.Data;

using Npgsql;

public static class NpgsqlUserManual

{

public static void Main(String[] args)

{

using(NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=joe;Password=secret;Database=joedata;"))

{

conn.Open();

// Declare the parameter in the query string

using(NpgsqlCommand command = new NpgsqlCommand("select * from tablea where column1 = :column1", conn))

{

// Now add the parameter to the parameter collection of the command specifying its type.

command.Parameters.Add(new NpgsqlParameter("column1", NpgsqlDbType.Integer);

// Now, prepare the statement.

command.Prepare();

// Now, add a value to it and later execute the command as usual.

command.Parameters[0].Value = 4;

using(NpgsqlDataReader dr = command.ExecuteReader())

{

while(dr.Read())

{

for (i = 0; i < dr.FieldCount; i++)

{

Console.Write("{0} \t", dr[i]);

}

Console.WriteLine();

}

}

}

}

}

}

</pre>

This code assumes a table called "tablea" with at least one column named "column1" of type int4.

<h3> Function calling</h3>

<p> To call a function, set the CommandType property of the NpgsqlCommand object to CommandType.StoredProcedure

and pass the name of the function you want to call as the query string (CommandText property).</p>

<pre class="CodeBox">

using System;

using System.Data;

using Npgsql;

// This example uses a function called funcC() with the following definition:

// create function funcC() returns int8 as '

// select count(*) from tablea;

// ' language 'sql';

// Note that the return type of select count(*) changed from int4 to int8 in 7.3+ versions. To use this function

// in a 7.2 server, change the return type from int8 to int4.

public static class NpgsqlUserManual

{

public static void Main(String[] args)

{

NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=joe;Password=secret;Database=joedata;");

conn.Open();

try

{

NpgsqlCommand command = new NpgsqlCommand("funcC", conn);

command.CommandType = CommandType.StoredProcedure;

Object result = command.ExecuteScalar();

Console.WriteLine(result);

}

finally

{

conn.Close();

}

}

}

<p>Adding parameters to a PostgreSQL function is similar to our previous examples. However,

when specifying the CommandText string, you can exclude parameter names. Use only the

function name:</p>

<pre class="CodeBox">

using System;

using System.Data;

using Npgsql;

// This example uses a function called funcC with the following definition:

// create function funcC(int4) returns int8 as '

// select count(*) from tablea where field_int4 = $1;

// ' language 'sql';

public static class NpgsqlUserManual

{

public static void Main(String[] args)

{

NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=joe;Password=secret;Database=joedata;");

conn.Open();

try

{

NpgsqlCommand command = new NpgsqlCommand("funcC", conn);

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add(new NpgsqlParameter());

command.Parameters[0].NpgsqlDbType = NpgsqlDbType.Integer;

command.Parameters[0].Value = 4;

Object result = command.ExecuteScalar();

Console.WriteLine(result);

}

finally

{

conn.Close();

}

}

}

<p> This code assumes a table called "tablea" with at least one field called "field_int4" of type int4.

<h3>Getting full results in a DataSet object: Using refcursors</h3>

<p>Refcursors are one of the most powerful ways to build functions in Postgres that return

large result sets to the client. Using refcursors, a single function can return the results of multiple queries

to the client in a single round-trip. Most Npgsql developers will learn that refcursors are quite easy to use once

you grasp the basic syntax.</p>

<p>This sample returns two result sets from a function using refcursors. With Npgsql's solid refcursor support, you can

get many result sets without having to worry about the internal workings of the refcursor in Postgres.</p>

Consider the following refcursor-based function:</p>

<pre class="CodeBox">

CREATE OR REPLACE FUNCTION testrefcursor(int4) RETURNS SETOF refcursor AS

'DECLARE

ref1 refcursor;

ref2 refcursor;

ref3 refcursor;

BEGIN

OPEN ref1 FOR

SELECT * FROM table1;

RETURN NEXT ref1;

OPEN ref2 FOR

SELECT * FROM table2;

RETURN next ref2;

OPEN ref3 FOR EXECUTE

'SELECT * FROM table3 WHERE keyfield = ' || $1;

RETURN next ref3;

RETURN;

END;'

LANGUAGE plpgsql;

<p>This function returns the full results of three select statements. Notice that the

last select statement is dynamically created on the server.</p>

<p>Now, to call these function and retrieve the data using a DataReader, you should use the

following code:</p>

<pre class="CodeBox">

using System;

using System.Data;

using Npgsql;

using NpgsqlTypes;

public class c

{

public static void Main(String[] args)

{

NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Initial Catalog=eeeeee;User id=npgsql_tests;password=npgsql_tests;");

conn.Open();

NpgsqlTransaction t = conn.BeginTransaction();

NpgsqlCommand command = new NpgsqlCommand("testrefcursor", conn);

command.CommandType = CommandType.StoredProcedure;

NpgsqlDataReader dr = command.ExecuteReader();

while(dr.Read())

{

Console.WriteLine(dr.GetValue(0));

}

dr.NextResult();

while(dr.Read())

{

Console.WriteLine(dr.GetValue(0));

}

dr.Close();

t.Commit();

conn.Close();

}

}

Alternatively, you can retrieve the results into a DataSet object:

<pre class="CodeBox">

using System;

using System.Data;

using Npgsql;

using NpgsqlTypes;

public class c

{

public static void Main(String[] args)

{

DataSet myDS;

NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Initial Catalog=eeeeee;User id=npgsql_tests;password=npgsql_tests;");

conn.Open();

NpgsqlTransaction t = conn.BeginTransaction();

NpgsqlCommand command = new NpgsqlCommand("testrefcursor", conn);

command.CommandType = CommandType.StoredProcedure;

con.Open();

NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);

da.Fill(myDS);

t.Commit();

conn.Close();

}

}

<p> That's it!. One last thing worth noting is that <font color="red"> you have to use a transaction</font> in order for this to work. This is necessary to prevent cursors returned by refcursor function from closing after the implicity transaction is finished (just after you do the function call).</p>

<p> If you have parameters in your function, <font color="red"> assign only the function name </font> to the CommandText property and add parameters to the NpgsqlCommand.Parameters collection as usual. Npgsql will take care of binding your parameters correctly.

<h3> Using output parameters in a query </h3>

<p> Output parameters can be used with Npgsql. Note that Npgsql "simulates" output parameter by parsing the first result set from the execution of a query and translating it to output parameters value. This can be done in two ways: mapped or not. A mapped parsing tries to match the column name returned by resultset into a parameter with the same name. If a match is found, only the output parameters which has a match will be updated. If a map is not found, the output parameters are updated based on the order they were added to command parameters collection. This mapping is automatic. When parsing resultset, Npgsql tries to find a match. <b>Both Output and InputOutput parameter directions are supported</b>.</p>

<pre class="CodeBox">

using System;

using System.Data;

using Npgsql;

public static class NpgsqlUserManual

{

public static void Main(String[] args)

{

NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=joe;Password=secret;Database=joedata;");

conn.Open();

// Send a query to backend.

NpgsqlCommand command = new NpgsqlCommand("select * from tablea where column1 = 2", conn);

// Now declare an output parameter to receive the first column of the tablea.

NpgsqlParameter firstColumn = new NpgsqlParameter("firstcolumn", NpgsqlDbType.Integer);

firstColumn.Direction = ParameterDirection.Output;

command.Parameters.Add(firstColumn);

try

{

command.ExecuteNonQuery();

// Now, the firstcolumn parameter will have the value of the first column of the resultset.

Console.WriteLine(firstColumn.Value);

}

finally

{

conn.Close();

}

}

}

<h3> Working with .NET Datasets </h3>

<p> Npgsql lets you propogate changes to a .NET DataSet object back to the

database. The example below demonstrates the insertion of a record into a DataSet,

followed by a call to update the associated database:</p>

<pre class="CodeBox">

// This method expects the following table in the backend:

//

// create table tableb(field_int2 int2, field_timestamp timestamp, field_numeric numeric);

//

//

void AddWithDataSet(NpgsqlConnection conn)

{

conn.Open();

DataSet ds = new DataSet();

NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tableb", conn);

da.InsertCommand = new NpgsqlCommand("insert into tableb(field_int2, field_timestamp, field_numeric) " +

" values (:a, :b, :c)", conn);

da.InsertCommand.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Smallint));

da.InsertCommand.Parameters.Add(new NpgsqlParameter("b", NpgsqlDbType.Timestamp));

da.InsertCommand.Parameters.Add(new NpgsqlParameter("c", NpgsqlDbType.Numeric));

da.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;

da.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;

da.InsertCommand.Parameters[2].Direction = ParameterDirection.Input;

da.InsertCommand.Parameters[0].SourceColumn = "field_int2";

da.InsertCommand.Parameters[1].SourceColumn = "field_timestamp";

da.InsertCommand.Parameters[2].SourceColumn = "field_numeric";

da.Fill(ds);

DataTable dt = ds.Tables[0];

DataRow dr = dt.NewRow();

dr["field_int2"] = 4;

dr["field_timestamp"] = new DateTime(2003, 03, 03, 14, 0, 0);

dr["field_numeric"] = 7.3M;

dt.Rows.Add(dr);

DataSet ds2 = ds.GetChanges();

da.Update(ds2);

ds.Merge(ds2);

ds.AcceptChanges();

}

<h3> Working with strongly typed datasets </h3>

<p> This example demonstrates the use of a strongly typed dataset generated by XSD.

To start, we need an XSD file specifing the appropiate schema. You can generate this file by hand, or you can use an XSD tool to generate it for you.

In order to let NpgsqlDataAdapter generate XSD, you need to suppy it with an XML file; the XML file allows the inference of an XML schema.

<pre class="CodeBox">

public void GenerateXmlFromDataSet(NpgsqlConnection conn)

{

conn.Open();

NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tablea", conn);

DataSet ds = new DataSet();

da.Fill(ds);

ds.WriteXml("StrongDataSetFeed.xml");

}

<p> The example code results in a file which looks similar to:</p>

<pre class="CodeBox">

&lt;?xml version="1.0" standalone="yes"?&gt;

&lt;NewDataSet&gt;

&lt;Table&gt;

&lt;field_serial&gt;1&lt;/field_serial&gt;

&lt;field_text&gt;Random text&lt;/field_text&gt;

&lt;/Table&gt;

&lt;Table&gt;

&lt;field_serial&gt;2&lt;/field_serial&gt;

&lt;field_int4&gt;4&lt;/field_int4&gt;

&lt;/Table&gt;

&lt;Table&gt;

&lt;field_serial&gt;3&lt;/field_serial&gt;

&lt;field_int8&gt;8&lt;/field_int8&gt;

&lt;/Table&gt;

&lt;Table&gt;

&lt;field_serial&gt;4&lt;/field_serial&gt;

&lt;field_bool&gt;true&lt;/field_bool&gt;

&lt;/Table&gt;

&lt;Table&gt;

&lt;field_serial&gt;5&lt;/field_serial&gt;

&lt;field_text&gt;Text with ' single quote&lt;/field_text&gt;

&lt;/Table&gt;

&lt;/NewDataSet&gt;

<p> The following command uses the file to generate XSD:</p>

<pre class="CodeBox">

xsd StrongDataSetFeed.xml

<p> XSD will produce an XML schema in which all types are specified as string. As a consequence, we need to

change the XSD to specify the correct types, resulting in an XSD file similar to:</p>

<pre class="CodeBox">

&lt;?xml version="1.0" encoding="utf-8"?&gt;

&lt;xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"&gt;

&lt;xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:Locale="pt-BR"&gt;

&lt;xs:complexType&gt;

&lt;xs:choice maxOccurs="unbounded"&gt;

&lt;xs:element name="Table"&gt;

&lt;xs:complexType&gt;

&lt;xs:sequence&gt;

&lt;xs:element name="field_serial" type="xs:int" minOccurs="0" /&gt;

&lt;xs:element name="field_text" type="xs:string" minOccurs="0" /&gt;

&lt;xs:element name="field_int4" type="xs:int" minOccurs="0" /&gt;

&lt;xs:element name="field_int8" type="xs:long" minOccurs="0" /&gt;

&lt;xs:element name="field_bool" type="xs:boolean" minOccurs="0" /&gt;

&lt;/xs:sequence&gt;

&lt;/xs:complexType&gt;

&lt;/xs:element&gt;

&lt;/xs:choice&gt;

&lt;/xs:complexType&gt;

&lt;/xs:element&gt;

&lt;/xs:schema&gt;

<p> Given the above file, the following command generates a strongly typed dataset:</p>

<pre class="CodeBox">

xsd StrongDataSetFeed.xsd /dataset

<p>This command generates a file that compiles into an assembly for the strongly typed dataset.

It's used in the example below:</p>

<pre class="CodeBox">

using System;

using Npgsql;

public class t

{

public static void Main(String[] args)

{