Dapper Postgres stored procedure with parameter

Question

Dapper Postgres stored procedure with parameter

c# dapper postgresql

Question

try
{
    return Connection.QuerySingleOrDefault<T>(sql, param, _transaction,
        commandType: CommandType.StoredProcedure);
}
catch (Exception orig)
{
    var ex = new Exception($"Dapper proc execution failed!", orig);
    AddDetailsToException(ex, sql, param);
    throw ex;
}

With SQL:

CREATE OR REPLACE PROCEDURE public."GetChildBank"(
    "bankId" integer DEFAULT NULL::integer)
LANGUAGE 'sql'

AS $BODY$

     )
     select * from cte where ParentBank is not null
                and "Id" <> "bankId"
$BODY$;

I am using Dapper with PostgreSQL and using stored procedure to get data but it always throws errors.

It converts into a SQL statement

SELECT * 
FROM "GetChildBank"("bankId" := $1)

which is wrong.

Amol Vasaikar

View more on Stack Overflow

jgoday · Accepted Answer · 4/1/2019 5:38:18 PM

Dapper does not seem to be the problem here. To execute a stored procedure in postgresql with parameters it should be

using (var connection = new NpgsqlConnection("Host=localhost;Username=postgres;Password=root;Database=sample"))
{
    connection.Open();
    var sql = "CALL \"GetChildBank\"(@bankId)";
    var p = new DynamicParameters();
    p.Add("@bankId", 11);
    var res = connection.Execute(sql, p);
}

To create a function that returns a row set and to callit from select, you need to use create function

CREATE OR REPLACE FUNCTION ""GetChildBank"("bankId" integer) RETURNS SETOF cte AS $$
SELECT * FROM cte where "ParentBank" IS NOT NULL AND "Id" <> $1;
$$ LANGUAGE sql;

https://www.postgresql.org/docs/11/sql-createfunction.html

Dapper Postgres stored procedure with parameter

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator