1

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

3
  • 1
    I would suggest a platform-neutral approach - rewrite bulk_upsert_logs to accept a JSONB argument and call it with a JSON array of object. Commented Nov 29, 2024 at 14:37
  • Unfortunately JSONB is not platform-neutral. It is strictly a Postgres data type (at least Oracle, MySql/MariaDB, nor MS Sql Server have only JSON).
    – Belayer
    Commented Nov 30, 2024 at 15:56
  • @Belayer I was having in mind platform neutrality on the calling side. Pass JSON as an argument and receive a JSON result. Query builders and alike become redundant and more or less useless. Pls. look at the use of JSONB rather than JSON on the database side as an implementation detail. Commented Dec 1, 2024 at 17:42

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.