I have created a user type in the postgresql:
CREATE TYPE usertimelogtype AS (
employeeno TEXT,
facilityid INTEGER,
activitydate DATE,
duration INTEGER);
I have created a procedure/function that is taking usertimelogtype[]
as its input
CREATE OR REPLACE FUNCTION bulk_upsert_logs(userlogs usertimelogtype[]
How do I call this function from .NET 8 using ADO.NET?
public async Task AddOrUpdateUserTimeCards(List<UserTimeCardLog> userTimeCardLogs)
{
using (var connection = new NpgsqlConnection(_connectionString))
{
//Insert the code to make a call to postgresql procedure bulk_upsert_logs
}
}
Here is my UserTimeCardLog
class
public class UserTimeCardLog
{
public string EmployeeNo { get; set; }
public int FacilityId { get; set; }
public DateOnly ActivityDate { get; set; }
public int Duration { get; set; }
}
I am new to ADO.NET and couldn't figure how to make this procedure call. I have tried chatgpt code but couldn't solve it as the code it is providing is trying to use npgsql.composite which is not available npgsqldatatype
bulk_upsert_logs
to accept a JSONB argument and call it with a JSON array of object.