Error in calling Postgres stored procedure using C#

Question

I am trying to update/insert data through a stored procedure with a table valued parameter.

Here is the classA:

public class classA
{
    public DateTime Param1 { get; set; }
    public string Param2 { get; set; }
    public string Param3 { get; set; }
    public int Param4 { get; set; }
    public int Param5 { get; set; }
    public decimal Param6 { get; set; }
    public int Param7 { get; set; }
    public string Param8 { get; set; }
    public bool Param9 { get; set; }
    public bool Param10 { get; set; }
    public bool Param11 { get; set; }
    public string Param12 { get; set; }
    public string Param13 { get; set; }
    public string Param14 { get; set; }
    public string Param15 { get; set; }
    public string Param16 { get; set; }
    public string Param17 { get; set; }
    public int Param18 { get; set; }
    public int Param19 { get; set; }
    public int Param20 { get; set; }
    public int Param21 { get; set; }
    public int Param22 { get; set; }
    public string Param23 { get; set; }
    public string Param24 { get; set; }
    public string Param25 { get; set; }
    public string Param26 { get; set; }
    public string Param27 { get; set; }
    public string Param28 { get; set; }
    public string Param29 { get; set; }
    public string Param30 { get; set; }
    public string Param31 { get; set; }
    public string Param32 { get; set; }
    public string Param33 { get; set; }
    public string Param34 { get; set; }
}

Here is the code I am using to call the stored procedure:

public static void ExecuteStoredProcedureWithSingleTableTypeParameter<T>(string connectionString, string storedProcedureName, List<T> parameters)
{
    using (var connection = new NpgsqlConnection(connectionString))
    {
        connection.Open();

        using (var command = new NpgsqlCommand(storedProcedureName, connection))
        {
            command.CommandType = CommandType.StoredProcedure;

            // Assuming "p_data" is the parameter name in your stored procedure
            var parameter = new NpgsqlParameter
            {
                ParameterName = "p_data",
                NpgsqlDbType = NpgsqlDbType.Jsonb,
                Direction = ParameterDirection.Input,
                Value = JsonConvert.SerializeObject(parameters, new JsonSerializerSettings
                {
                    ContractResolver = new LowercaseContractResolver(),
                    Formatting = Formatting.None // You can adjust formatting as needed
                })
            };

            command.Parameters.Add(parameter);

            command.ExecuteNonQuery();
        }
    }
}

This is the stored procedure:

CREATE OR REPLACE PROCEDURE Test.SP1(
    p_data Test.TableType1
)
LANGUAGE plpgsql
AS $$
BEGIN
    --STATEMENTS
END;
$$;

I am calling the ExecuteStoredProcedureWithSingleTableTypeParameter method from a different class like this

DBRepository.ExecuteStoredProcedureWithSingleTableTypeParameter(connectionString, "test.sp1", listingData);

Details of the error in Visual Studio:

Hint: No procedure matches the given name and argument types. You might need to add explicit type casts.

42883: procedure test.sp1(p_data => jsonb) does not exist

POSITION: 6

Added TableType:

    CREATE TYPE Test.TableType1 AS (
   Param1 Date,
   Param2 varchar(50),
   Param3 varchar(30),
   Param4 int,
   Param5 int,
   Param6 decimal(3,1),
   Param7 int,
   Param8 varchar(255),
   Param9 boolean,
   Param10 boolean,
   Param11 boolean,
   Param12 varchar(50),
   Param13 varchar(30),
   Param14 varchar(150),
   Param15 varchar(500),
   Param16 varchar(255),
   Param17 varchar(500),
   Param18 int,
   Param19 int,
   Param20 int,
   Param21 int,
   Param22 int,
   Param23 varchar(50),
   Param24 varchar(30),
   Param25 varchar(255),
   Param26 varchar(50),
   Param27 varchar(30),
   Param28 varchar(255),
   Param29 varchar(255),
   Param30 varchar(10),
   Param31 varchar(10),
   Param32 varchar(30),
   Param33 varchar(100),
   Param34 varchar(255)
);

I am new to Postgresql. How can I fix this issue?

Answer 1

AFAIK you cannot unfortunately do what you want. Npgsql does not have an equivalent of the SqlDbType.Structured, which would allow you to pass a DataTable. Instead as you rightly do, you must serialize your data to json. However this means that your stored procedure has to have a parameter of type jsonb, not Test.TableType1.

So you need to rewrite your stored procedure accordingly. Assuming you are inserting the data into a table, it would look something like this:

CREATE OR REPLACE PROCEDURE test.sp1(
    p_data jsonb)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    INSERT INTO test.table1 
    select (j.p_data->>'param1')::date, 
         j.p_data->>'param2', 
         j.p_data->>'param3', 
         (j.p_data->>'param4')::int, 
         (j.p_data->>'param5')::int, 
         (j.p_data->>'param6')::numeric(3,1), 
         (j.p_data->>'param7')::int, 
         j.p_data->>'param8', 
         (j.p_data->>'param9')::boolean, 
         (j.p_data->>'param10')::boolean, 
         (j.p_data->>'param11')::boolean, 
         j.p_data->>'param12', 
         j.p_data->>'param13', 
         j.p_data->>'param14', 
         j.p_data->>'param15', 
         j.p_data->>'param16', 
         j.p_data->>'param17', 
         (j.p_data->>'param18')::int, 
         (j.p_data->>'param19')::int, 
         (j.p_data->>'param20')::int, 
         (j.p_data->>'param21')::int, 
         (j.p_data->>'param22')::int, 
         j.p_data->>'param23', 
         j.p_data->>'param24', 
         j.p_data->>'param25', 
         j.p_data->>'param26', 
         j.p_data->>'param27', 
         j.p_data->>'param28', 
         j.p_data->>'param29', 
         j.p_data->>'param30', 
         j.p_data->>'param31', 
         j.p_data->>'param32', 
         j.p_data->>'param33', 
         j.p_data->>'param34' 
    from jsonb_array_elements(p_data) as j(p_data);
END;
$BODY$;

This assumes that all fields in the table are being filled in the correct order, otherwise you need to specify the fields in parentheses after the table name.

The procedure can be simplified, if you have an exact mapping between your table field names and your class property names (including case sensitivity in the jsonb). Then all you need is something like this:

CREATE OR REPLACE PROCEDURE test.sp1(
    p_data jsonb
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO test.table1
    select (jsonb_populate_record(null::test.table1, j.p_data)).*
    from jsonb_array_elements(p_data) as j(p_data);
END;
$$;