Dapper Postgres stored procedure with parameter

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.

"but it always error" - what error? and what does param look like here; and presumably sql is the name of a stored procedure, in which case: what parameters are defined on it? — Marc Gravell
– Marc Gravell, Commented Mar 13, 2019 at 9:51
Please edit your question (by clicking on the edit link below it) and add the code of your stored procedure (or function) as formatted text please, no screen shots — user330315
– user330315, Commented Mar 13, 2019 at 9:52
to elaborate - I can almost certainly help here (I'm the primary dapper author/maintainer), but: I'm not psychic. You're going to have to help me here by telling me at least what orig.Message says. Any additional details would also be useful, but orig.Message is the absolute minimum I need to stand a chance here (in a perfect world: orig.GetType().Name would be nice, too, as would some info about what sql and param are - maybe something about what T is too) — Marc Gravell
– Marc Gravell, Commented Mar 13, 2019 at 10:00
@MarcGravell sql is store procedure name and param are parameter of store procedure — Amol Vasaikar
– Amol Vasaikar, Commented Mar 13, 2019 at 10:07
re the edit: It converts into sql a statement SELECT * FROM "GetChildBank"("bankId" := $1) which is wrong - no, that's simply not a thing that dapper does, at all. It doesn't convert anything into anything. So; let's take a step back: what is sql here? presumably sql is "GetChildBank"? and presumably param looks something like new { bankId = 130134 }? Now: what actually happens? What is orig.Message? — Marc Gravell
– Marc Gravell, Commented Mar 13, 2019 at 10:07

jgoday · Accepted Answer · 2019-04-01 17:38:18Z

2

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

answered Apr 1, 2019 at 17:38

jgoday

2,8461 gold badge19 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dapper Postgres stored procedure with parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related