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 DbDataReader types when interacting with PostgreSQL:

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

builder.UseTvp();

NpgsqlDataSource dataSource = builder.Build();

NpgsqlConnection connection = dataSource.CreateConnection();

await connection.OpenAsync();

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");

using (NpgsqlCommand cmd = new NpgsqlCommand("schema.storedProcedure", connection))
{
    cmd.CommandType = CommandType.StoredProcedure;

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

    await cmd.ExecuteNonQueryAsync();
}

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 via NpgsqlParameter.DataTypeName is not supported. For DataTable, use TableName; for DbDataReader, implement GetSchemaTable.