CODEWITHSUNDEEP
c#dappernpgsql
juliano.net
juliano.net

Reputation: 8177

Execute function using Npgsql 7

I'm running PostgreSQL 15 and added both Dapper and Npgsql 7.0 to my project. I need to run the following function:

CREATE OR REPLACE FUNCTION get_all_posts(
    start_date date default null,
    end_date date default null,
    user_id integer default null,
    take_rows integer default 10,
    skip_rows integer default 0)
 RETURNS SETOF post_info
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY SELECT .....
END;
$function$
;

Then I tried the following piece of C# code to execute this query and return the resultset:

using (var reader = await connection.ExecuteReaderAsync("get_all_posts", 
                   new
                   {
                       start_date = postRequest.StartDate as object ?? DBNull.Value,
                       end_date = postRequest.EndDate as object ?? DBNull.Value,
                       user_id = postRequest.UserId as object ?? DBNull.Value,
                       take_rows = take,
                       skip_rows = skip,
                   }, commandType: CommandType.StoredProcedure))
{
// ...
}

This causes the error saying that get_all_posts is not a procedure (Npgsql 7.0 stopped accepting the CommandType.StoredProcedure for functions).

And then tried to use the SELECT statement like this:

using (var reader = await connection.ExecuteReaderAsync("SELECT * FROM get_all_posts($1, $2, $3, $4, $5)", 
                   new
                   {
                       start_date = postRequest.StartDate as object ?? DBNull.Value,
                       end_date = postRequest.EndDate as object ?? DBNull.Value,
                       user_id = postRequest.UserId as object ?? DBNull.Value,
                       take_rows = take,
                       skip_rows = skip,
                   }, commandType: CommandType.Text))
{
// ...
}

But in this case the error was bind message supplies 0 parameters, but prepared statement "" requires.

How's the correct way of getting Npgsql and Dapper to execute PostgreSQL function?

Upvotes: 3

Views: 2658

Answers (2)

Mesut Tasci
Mesut Tasci

Reputation: 11

I'm using dapper too. After 2 day, i found this solution:

AppContext.SetSwitch("Npgsql.EnableStoredProcedureCompatMode", true);

Upvotes: 0

Shay Rojansky
Shay Rojansky

Reputation: 16672

CommandType.StoredProcedure has been changed to invoke stored procedures in Npgsql 7.0, see the release notes.

Upvotes: 1

Related Questions