How to execute postgres function using dapper

Question

I get an error when I try to call a postgre function using dapper. Where did i do wrong? I'll be glad if you can help me.

Error Message:

 availability_list(facilityId => integer, startDate => timestamp without time zone, endDate => timestamp without time zone) does not exist"

Call postgre function using Dapper:

var func = "public.availability_list";

var result = db.Query<ReportResponse>(
            sql: func,
            param: new { facilityId = request.FacilityId, startDate = 
            DateTime.Now, endDate = DateTime.Now },
            commandType: CommandType.StoredProcedure, 
            commandTimeout: 900) as List<ReportResponse>;

My Postgre Function:

CREATE FUNCTION Availability_List(facilityId int, startDate date, endDate date)
RETURNS report_type[] 
AS 
$$

DECLARE
result_record report_type[];

BEGIN

result_record := array(  
SELECT...
);

RETURN result_record;

 END $$ LANGUAGE plpgsql;

I have the same issue, using the lowercase for parameters name, worked for me. — Artiom
– Artiom, Commented Nov 12, 2020 at 18:17

Roo · Accepted Answer · 2020-06-03 07:40:07Z

2

Try passing function parameters in lowercase, i.e.

var result = db.Query<ReportResponse>(
            sql: func,
            param: new { facilityid = request.FacilityId, startdate = 
            DateTime.Now, enddate = DateTime.Now },
            commandType: CommandType.StoredProcedure, 
            commandTimeout: 900) as List<ReportResponse>;

That worked for me.

answered Jun 3, 2020 at 7:40

Roo

517 bronze badges

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

1 Comment

Artiom Over a year ago

User lowercase for parameters name, that worked for me too.

Margus · Accepted Answer · 2019-06-20 12:32:54Z

1

I would expect you need to specify the parameters, schema of the function and the result should match. Likely following would work and after that you can replace the return type to see if it is mapped correctly.

var result = _connection.Query<dynamic>(
    "SELECT dbo.Availability_List(@facilityId, @startDate, @endDate)", 
    new { 
        facilityId = request.FacilityId, 
        startDate = DateTime.Now, 
        endDate = DateTime.Now 
    },
    commandType: CommandType.Text,
    commandTimeout: 900);

answered Jun 20, 2019 at 12:32

Margus

20.1k15 gold badges59 silver badges104 bronze badges

2 Comments

burak ramazan Over a year ago

Hi Margus, i tried this format but i'm getting same error message. By the way, schema name is public. For this reason, i added public prefix.

Margus Over a year ago

Are you connected to the correct database and if you execute the statement in Sql manager then does it work? Have you granted permissions to execute this query for the user ex: learn.microsoft.com/en-us/sql/t-sql/statements/…

Artog · Accepted Answer · 2019-08-14 08:20:47Z

0

I think this happens because you created the function Availability_List where A and L are capitalized. But then you are calling the function with all lower case. I have faced this issue already, use all in a small case. That will be better...

edited Aug 14, 2019 at 8:20

Artog

1,1321 gold badge13 silver badges28 bronze badges

answered Aug 14, 2019 at 7:34

Vasanth R

2401 gold badge2 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to execute postgres function using dapper

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related