C# Postgres procedure with parameters

Question

How do you call a pg procedure with parameters from C#?

I tried related examples and they did not work (i.e. this question)

Here is a super simple example:

Table:

    (col1 integer, 
    col2 character varying(20) )

Procedure:

    create procedure proc1 (num int, str varchar(20))
    language plpgsql    
    as $$
    begin  
    insert into test1 (col1, col2) VALUES (num, str);
    commit;
    end;$$;

Code where I attempt to use it:

    using var cmd = new NpgsqlCommand("CALL proc1()", _con);
    cmd.Parameters.AddWithValue("num", NpgsqlDbType.Integer, 2);
    cmd.Parameters.AddWithValue("str", NpgsqlDbType.Varchar, "string");

    cmd.ExecuteNonQuery();

Exception that I am getting:

Npgsql.PostgresException: '42883: procedure proc1() does not exist POSITION: 6'

I also made a proc2 that has NO parameters and I'm able to call that one just fine. I also tried several options to "CALL proc1()" like "CALL proc1(num, str)", but those exceptions make even less sense, as pg can't tell the difference between a parameter and a column:

Npgsql.PostgresException: '42703: column "num" does not exist

Answer 1

Try:

using var cmd = new NpgsqlCommand("proc1", _con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("num", NpgsqlDbType.Integer, 2);
cmd.Parameters.AddWithValue("str", NpgsqlDbType.Varchar, "string");

cmd.ExecuteNonQuery();

Alternatively, you could use this syntax:

using var cmd = new NpgsqlCommand("CALL proc1($1, $2)", _con);

cmd.Parameters.AddWithValue("num", NpgsqlDbType.Integer, 2);
cmd.Parameters.AddWithValue("str", NpgsqlDbType.Varchar, "string");

cmd.ExecuteNonQuery();

https://www.npgsql.org/doc/basic-usage.html#stored-functions-and-procedures