Motivation

The plugin was created to emulate table-valued parameters (supported in SQL Server but not PostgreSQL) to enhance cross-database compatibility.

Usage

Add a dependency on this package and create a NpgsqlDataSource. Once this is done, you can use DataTable and IDataReader types when interacting with PostgreSQL:

NpgsqlDataSourceBuilder builder = new
NpgsqlDataSourceBuilder
("Host=localhost;Username=postgres;Password=root;Database=postgres");

builder.UseTvp();

// Connect..

DataTable dt = new
DataTable
("schema.compositeType");

dt.Columns.Add(new DataColumn("Column1"));
dt.Columns.Add(new DataColumn("Column2"));
dt.Columns.Add(new DataColumn("Column3"));

dt.Rows.Add("Column1_value", "Column2_value", "Column3_value");
dt.Rows.Add("Column1_value", "Column2_value", "Column3_value");
dt.Rows.Add("Column1_value", "Column2_value", "Column3_value");

NpgsqlCommand cmd = new
NpgsqlCommand
("schema.storedProcedure", connection);

cmd.Parameters.Add(new NpgsqlParameter(nameof(dt), dt));

// Set command type and execute..

The plugin processes the parameter as an array of a composite type that was previously created on the server:

CREATE PROCEDURE schema.storedProcedure(IN dt schema.compositeType[])

Note

Specifying types using NpgsqlParameter.DataTypeName is not supported.

DataTable

Use TableName property.

IDataReader

Use GetSchemaTable method. The table must include a column named BaseTableName.