Motivation

The plugin was created to emulate table-valued parameters (supported in SQL Server but not PostgreSQL) to improve code portability.

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"); // TableName is required!

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($"CALL schema.storedProcedure(@{ nameof(dt) })", connection))
{
    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 params schema.compositeType[])

Related Projects

• Simple.DbExtensions.Tvp