PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to pass List<object> or DataTable to Postgres stored procedure using C#?

I am using C# for CRUD operations and using Postgres as my database. I was using Dapper for my operations. But I am stuck when I want to execute multiple queries. I don’t want to make multiple connections to the database. So I thought of using the stored procedures.
But I am not able to find a way to pass a List or a datatable directly as a parameter in the postgres stored procedure.

Is there any resource where I can read more about this topic?

What are the standard ways to execute multiple queries on a list/array/datatable with postgres?

For simple insertions, I am using this method right now.

try
{
    connection.Execute(
        $@"INSERT INTO amazon.sales (platformproductid, valuationdate, shippedrevenue, shippedunits)
            VALUES ((SELECT Id FROM Test.TableName Where Platformcode = @ASIN and ChannelId = {channelId} and IsActive = true), @valuationDate, @shippedRevenue, @shippedUnits)
            ON CONFLICT (platformproductid, valuationdate) DO UPDATE
            SET shippedrevenue = EXCLUDED.shippedrevenue,
                shippedunits = EXCLUDED.shippedunits;",
        salesData,
        transaction
    );

    transaction.Commit();
    return true;
}
catch (Exception ex)
{
    transaction.Rollback();
    return false;

}

When I am trying to execute this logic. I am facing syntax errors:

connection.Execute(
    $@"
            IF EXISTS (SELECT 1 FROM Amazon.ProductMaster WHERE PlatformCode = @ASIN AND ChannelId = @ChannelId AND IsActive = true) THEN
                INSERT INTO Amazon.Inventory (
                    ValuationDate,
                    PlatformProductId,
                    Quantity
                ) VALUES (
                    @ValuationDate,
                    (SELECT Id FROM Amazon.ProductMaster WHERE PlatformCode = @ASIN AND ChannelId = @ChannelId AND IsActive = true),
                    @Quantity
                ) ON CONFLICT (ValuationDate, PlatformProductId) DO UPDATE SET
                    Quantity = excluded.Quantity;
            ELSE
                INSERT INTO BSC.MissingData (ValuationDate, ProcessId, MissingValue)
                VALUES (
                    @ValuationDate,
                    (SELECT Id FROM BSC.ProcessList WHERE Name = 'Inventory' AND ChannelId = @ChannelId),
                    @ASIN
                );
            END IF;

            ",
     inventoryData,
    transaction
);

I am new to postgres.
To simplify these operations, I tried using stored procedures but I am not able to pass my List as a parameter. How can I handle this in postgres?

Tags:

2

Answers


  1. 0

    To pass a List or DataTable to a PostgreSQL stored procedure, you can use the Npgsql library, which is a .NET data provider for PostgreSQL. The process involves serializing your List or DataTable into a format that PostgreSQL can understand (like JSON) and then passing it to the stored procedure as a parameter.

    string json = JsonSerializer.Serialize(myList);
var parameters = new DynamicParameters();
parameters.Add("json_data", json, DbType.String);
    
connection.Execute("your_procedure_name", parameters, commandType: CommandType.StoredProcedure);
    Login or Signup to reply.
  2. 0

    In PostgreSql you can define a procedure that takes an array of primitive data types as a parameter.
    For example,

    CREATE FUNCTION return_array_as_tbl(names text[])
  RETURNS TABLE(name text)
  LANGUAGE plpgsql
AS
$$
BEGIN
  RETURN QUERY
  SELECT UNNEST(names) AS name;
END
$$;

    You can also define your own composite type in Postgres,
    e.g. CREATE TYPE public.person AS (name text, age int, has_pets bool);
    and then you can use a array of that type as your procedure parameter
    e.g. CREATE PROCEDURE update_people(people person[]).

    As mentioned in the answer from @DomiGeek, the very popular Npgsql ADO.Net library lets you pass in primitive arrays with the NpgsqlDbType.Array parameter type, accomodating a List<T>
    or you can add a few lines to support your custom CompositeType

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search