ravikiran reddy
ravikiran reddy

Reputation: 31

How to pass a user-defined table type (UDT) to a PostgreSQL stored procedure using C# and Npgsql?

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

Answers (0)

Related Questions