• These type libraries include:

  • NodaTime - the recommended way to interact with PostgrSQL date/time types

  • Json.NET - allows Npgsql to use the NewtonSoft Json.NET library when reading and writing JSON data (both json and jsonb)

  • NetTopologySuite - allows Npgsql to map PostGIS spatial types directly to the NetTopology suite types (the leading spatial library in .NET)

  • GeoJSON - allows Npgsql to read and write PostGIS spatial types as GeoJSON types via the GeoJSON.NET library

• Setup the plugin in your application simply by adding a dependency on the plugin (this should have been done automatically when installed to the project) and set it up. See the following code snippet for an example of setting up the Npgsql.NodaTime plugin:

  	using Npgsql;
  	// Place this at the beginning of your program to use NodaTime everywhere (recommended)
  	NpgsqlConnection.GlobalTypeMapper.UseNodaTime();
  	// Or to temporarily use NodaTime on a single connection only:
  	conn.TypeMapper.UseNodaTime();

  Once the plugin is setup, you can read and write NodaTime objects as per the code snippet below:

  	// Write NodaTime Instant to PostgreSQL "timestamp without time zone"
  	using (var cmd = new NpgsqlCommand(@"INSERT INTO mytable (my_timestamp) VALUES (@p)", conn))
  	{
  	    cmd.Parameters.Add(new NpgsqlParameter("p", Instant.FromUtc(2011, 1, 1, 10, 30)));
  	    cmd.ExecuteNonQuery();
  	}
  	
  	// Read timestamp back from the database as an Instant
  	using (var cmd = new NpgsqlCommand(@"SELECT my_timestamp FROM mytable", conn))
  	using (var reader = cmd.ExecuteReader())
  	{
  	    reader.Read();
  	    var instant = reader.GetFieldValue<Instant>(0);
  	}

• To apply the type plugin updates, do one of the following:

  • After uninstalling the type plugin (Refer to "4.5.2 Uninstalling .NET Data Provider Type Plugins"), setup the type plugin (Refer to "4.2.2 Setting Up .NET Data Provider Type Plugins").

  • Remove the type plugin directory from the packages directory of the solution, and then restore it using the nuget restore command.

• When you deploy an application with a type plugin, the type plugin is included in the distribution. Therefore, after applying the type plugin updates, you must rebuild the application and deploy the updated application.

Describe notes about each type plugin.

NodaTime

Describes the mapping of PostgreSQL data types to NodaTime data types.

Mapping Table

PostgreSQL Type	Default NodaTime Type	Additional NodaTime Type	Note
timestamp with time zone	Instant	ZonedDateTime(*1), OffsetDateTime(*1)	UTC timestamp in database. Only UTC ZonedDateTime and OffsetDateTime are supported.
timestamp without time zone	LocalDateTime(*2)		A timestamp in an unknown or implied timezone.
date	LocalDate		A simple date with no timezone or offset information.
time without time zone	LocalTime		A simple time with no timezone or offset information.
time with time zone	OffsetTime		A type that stores a time and an offset. Generally not recommended for use.
interval	Period	Duration	A time interval from fractional seconds to years. NodaTime Duration is supported for days and smaller intervals, but not for years or months (because they have no absolute duration). Duration can be used in any interval unit.
tszrange	Interval	NpgsqlRange<Instant> ,etc	The time interval between two time instances (start and end).
strange	NpgsqlRange<LocalDateTime>		The time interval between two timestamps in unknown or implied timezones.
daterange	DateInterval	NpgsqlRange<LocalDate> ,etc	The interval between two dates.

*1: When Npgsql.EnableLegacyTimestampBehavior is enabled, writing or reading ZonedDateTime or OffsetDateTime is automatically converted to UTC.

*2: When Npgsql.EnableLegacyTimestampBehavior is enabled, timestamp without time zone is mapped to Instant instead of LocalDateTime by default.

Json.NET

Once the JSON plugin has been setup, users can transparently read and write CLR objects as JSON values and the plugin will automatically serialize/deserialize them

See the code snippet below:

// Write arbitrary CLR types as JSON
using (var cmd = new NpgsqlCommand(@"INSERT INTO mytable (my_json_column) VALUES (@p)", conn))
{
    cmd.Parameters.Add(new NpgsqlParameter("p", NpgsqlDbType.Jsonb) { Value = MyClrType });
    cmd.ExecuteNonQuery();
}

// Read arbitrary CLR types as JSON
using (var cmd = new NpgsqlCommand(@"SELECT my_json_column FROM mytable", conn))
using (var reader = cmd.ExecuteReader())
{
    reader.Read();
    var someValue = reader.GetFieldValue<MyClrType>(0);
}

NetTopologySuite (spatial)

By default the plugin handles only ordinates provided by the DefaultCoordinateSequenceFactory of GeometryServiceProvider.Instance. If GeometryServiceProvider is initialized automatically the X and Y ordinates are handled. To change the behavior specify the handleOrdinates parameter like in the following example:

conn.TypeMapper.UseNetTopologySuite(handleOrdinates: Ordinates.XYZ);

To process the M ordinate, you must initialize GeometryServiceProvider.Instance to a new NtsGeometryServices instance with coordinateSequenceFactory set to a DotSpatialAffineCoordinateSequenceFactory. Or you can specify the factory when calling UseNetTopologySuite.

// Place this at the beginning of your program to use the specified settings everywhere (recommended)
GeometryServiceProvider.Instance = new NtsGeometryServices(
    new DotSpatialAffineCoordinateSequenceFactory(Ordinates.XYM),
    new PrecisionModel(PrecisionModels.Floating),
    -1);

// Or specify settings for Npgsql only
conn.TypeMapper.UseNetTopologySuite(
    new DotSpatialAffineCoordinateSequenceFactory(Ordinates.XYM));

Reading and Writing Geometry Values

When reading PostGIS values from the database, Npgsql will automatically return the appropriate NetTopologySuite types: Point, LineString, and so on. Npgsql will also automatically recognize NetTopologySuite's types in parameters, and will automatically send the corresponding PostGIS type to the database. The following code demonstrates a roundtrip of a NetTopologySuite Point to the database:

var point = new Point(new Coordinate(1d, 1d));
conn.ExecuteNonQuery("CREATE TEMP TABLE data (geom GEOMETRY)");
using (var cmd = new NpgsqlCommand("INSERT INTO data (geom) VALUES (@p)", conn))
{
    cmd.Parameters.AddWithValue("@p", point);
    cmd.ExecuteNonQuery();
}

using (var cmd = new NpgsqlCommand("SELECT geom FROM data", conn))
using (var reader = cmd.ExecuteReader())
{
    reader.Read();
    Assert.That(reader[0], Is.EqualTo(point));
}

You may also explicitly specify a parameter's type by setting NpgsqlDbType.Geometry.

Geography (geodetic) Support

PostGIS has two types:geometry (for Cartesian coordinates) and geography (for geodetic or spherical coordinates). You can read about the geometry/geography distinction in the PostGIS docs. In a nutshell, geography is much more accurate when doing calculations over long distances, but is more expensive computationally and supports only a small subset of the spatial operations supported by geometry.

Npgsql uses the same NetTopologySuite types to represent both geometry and geography - the Point type represents a point in either Cartesian or geodetic space. You usually don't need to worry about this distinction because PostgreSQL will usually cast types back and forth as needed. However, it's worth noting that Npgsql sends Cartesian geometry by default, because that's the usual requirement. You have the option of telling Npgsql to send geography instead by specifying NpgsqlDbType.Geography:

using (var cmd = new NpgsqlCommand("INSERT INTO data (geog) VALUES (@p)", conn))
{
    cmd.Parameters.AddWithValue("@p", NpgsqlDbType.Geography, point);
    cmd.ExecuteNonQuery();
}

If you prefer to use geography everywhere by default, you can also specify that when setting up the plugin:

NpgsqlConnection.GlobalTypeMapper.UseNetTopologySuite(geographyAsDefault: true);

GeoJSON (spatial)

Using the GeoJSON plugin is the same as the NetTopologuSuite.

• Prefix named parameters with "@".

• Uppercase object names cannot be used, even when enclosed in double quotation marks.
  To use uppercase object names enclosed in double quotation marks, include them in SQL statements and enter these in the [Generate the SQL statements] window rather than in the Query Builder.

• SQL statements cannot be correctly generated if the SQL statement specified in Filter matches any of the conditions below:

  • It uses PostgreSQL intrinsic operators such as << or ::.

  • It uses functions with keywords such as AS, FROM, IN, OVER.

    Example: extract(field from timestamp), RANK( ) OVER

  • It uses functions with the same names as those prescribed in SQL conventions, but that require different arguments.

• The CommandBehavior.KeyInfo argument must be specified if executing ExecuteReader before obtaining metadata using GetSchemaTable.

  Example

  NpgsqlDataReader  ndr=cmd.ExecuteReader(CommandBehavior.KeyInfo);
  DataTable dt = dr.GetSchemaTable();

• If the SQL statement includes a query (which cannot be updated) that matches any of the conditions below, an update-type SQL statement will be generated (note that it may not be possible to execute this SQL statement in some cases):

  • It includes derived tables

  • It includes the same column name as the select list

    
    

  Update-type SQL statements will be automatically generated in the following cases:

  • If update statements are obtained using NpgsqlCommandBuilder

  • If data is updated using NpgsqlDataAdapter

• Applications using transaction scope can use distributed transactions by linking with Microsoft Distributed Transaction Coordinator (MSDTC). In this case, note the following:

  • Ensure that the value of max_prepared_transactions is greater than max_connection, so that "PREPARE TRANSACTION" can be issued for each transaction that simultaneously connects to the database server.

  • If each transaction in the transaction scope accesses the same resource using different connections, the database server will perceive it as requests from different applications, and a deadlock may occur. By configuring a timeout value for the transaction scope beforehand, the deadlock can be broken.