Reputation: 31
I'm trying to pass a user-defined table type (UDT) to a PostgreSQL stored procedure using C# and Npgsql.
Here are the details - PostgreSQL composite type definition:
CREATE TYPE public.udt_associateprofile AS
(
employeeid character varying(100),
type character varying(100),
startdate date,
enddate date,
notes character varying(1000)
);
PostgreSQL stored procedure:
CREATE OR REPLACE FUNCTION my_stored_procedure(IN par_username Varchar, IN par_associatedata udt_associateprofile[])
RETURNS VOID AS $$
BEGIN
-- Your logic here
END;
$$ LANGUAGE plpgsql;
C# code to pass data:
using Npgsql;
using System;
using System.Collections.Generic;
public void Test_Method(DataTable datatable)
{
NpgsqlParameter[] parameters =
{
new NpgsqlParameter("@UserName", NpgsqlDbType.Varchar) { Value = userContext.UserName ?? (object)DBNull.Value },
// How to pass the above datatable as the second parameter.
}
FormattableString str = FormattableStringFactory.Create(
$"CALL public.\"{spInfo.Name}\"(@UserName, @utAssociateProfileImport)", parameters);
await iapContext.Database.ExecuteSqlInterpolatedAsync(str);
}
I have tried many ways like converting the datatable to json and etc.., but it did not workout. Also, I cannot change the stored procedure definition.
Upvotes: 0
Views: 41