Pass datatable as parameter in PostgreSQL using Npgsql

[rsimbu-36]

Hello Team,

I'm trying to run a function that has multiple parameters, including DataTable, integer, integer, and refcursor, using NpgSql to connect to a Postgres database. Here is how my function looks,

CREATE OR REPLACE FUNCTION ************(p_deallocationsla schema.udtt_deallocationservicelevel[], p_dealid integer, p_userid integer, result refcursor)
RETURNS refcursor
LANGUAGE plpgsql
AS $function$

and this is the.net code I used to call the function

public async Task *********(int dealId, DataTable dealServiceLevelData, int userId)
{
IEnumerable dsSaveResponse = new List();
var parameters = new PgParam();
var json = JsonConvert.SerializeObject(dealServiceLevelData);
parameters.Add("deallocationsla", dealServiceLevelData, NpgsqlTypes.NpgsqlDbType.?);
parameters.Add("p_dealid", dealId, NpgsqlTypes.NpgsqlDbType.Integer);
parameters.Add("p_userid", userId, NpgsqlTypes.NpgsqlDbType.Integer);
dsSaveResponse = await _postGresDapperBaseRepository.GetAllAsync(DatabaseName.SMPSPostGres, DbConstants.UpsertDealServiceLevelInfo.ToLower(), parameters);
return dsSaveResponse.FirstOrDefault();
}

I would like to know what NpgsqlDbType we should provide for the dataTable (schema.udtt_deallocationservicelevel[]).

We are utilizing SqlDbType.Structured in MSSQL with SqlParameter, but it's missing from NpgsqlDbType.

Could you kindly check and let us know the answer?

[Emill]

EF6 is extremely limited in terms of data types supported. Unless you can workaround it by setting it as string in your EF data model, and writing/reading it as a string (by serializing it to a string manually according to postgres rules, see https://www.npgsql.org/doc/dev/type-representations.html under record, I furthermore see that array seems to be missing there), you should move to EF Core.

If you are talking about raw Npgsql (not EF), please post an issue in that repo (https://github.com/npgsql/npgsql).

[roji]

@rsimbu-36 your question doesn't seem to have anything to do with Entity Framework - you seem to be using DataTable.

In any case, PostgreSQL doesn't support table-valued parameters similar to how MSSQL supports them (via SqlDbType.Structured). One possible alternative is to send an array of a composite type corresponding to your table type, or an array parameter for each column you want transferred, etc.