-1

I'm using PostgreSQL with a .NET project, and I'm facing an issue executing a stored procedure through Dapper when using dynamic parameters to pass a DATE parameter.

The stored procedure is defined as follows:

CREATE OR REPLACE PROCEDURE simplemenuproc(
    appcode IN VARCHAR, 
    myDtTm DATE, 
    ref1 INOUT REFCURSOR
)
LANGUAGE plpgsql
AS $BODY$
-- Procedure body
$BODY$;

Here's the C# code I'm using to call it:

NpgsqlTransaction tran = connection.BeginTransaction();
var parameters = new DynamicParameters();
parameters.Add("appcode", "PPM", direction: ParameterDirection.Input);
parameters.Add("myDtTm", DateOnly.Parse(DateTime.UtcNow.ToString("yyyy-MM-dd")), DbType.Date, direction: ParameterDirection.Input);
parameters.Add("ref1", direction: ParameterDirection.InputOutput);

// Execute the stored procedure
await connection.ExecuteAsync(storedProcedureName, parameters, commandType: CommandType.StoredProcedure);

When I try to execute this, I get the following error:

42883: procedure simplemenuproc(appcode => text, myDtTm => date, ref1 => unknown) does not exist

What I've tried

Changing how the date parameter is passed:

parameters.Add("myDtTm", DateTime.UtcNow.Date, direction: ParameterDirection.Input);

This resulted in:

42883: procedure simplemenuproc(appcode => text, myDtTm => timestamp with time zone, ref1 => unknown) does not exist

Verifying the stored procedure name and parameter types. The myDtTm parameter is defined as DATE, but it seems like the data type is mismatched when passed.

My environment:

  • Database: PostgreSQL
  • ORM: Dapper
  • Driver: Npgsql

Question

  • How do I correctly pass a DATE parameter to a PostgreSQL stored procedure using dynamic parameters in Dapper and Npgsql?

  • Why is the procedure not found even when the parameter types seem to match?

Improve this question
4
  • Why are you using ` DateOnly.Parse(DateTime.UtcNow.ToString("yyyy-MM-dd"))? Dates have no format, they're binary values, both in the database and .NET. Use DateOnly.Fro mDateTime(DateTime.Today)` or DateOnly.FromDateTime(DateTime.UtCNow) to create a DateOnly representing the current date. Commented Dec 18, 2024 at 8:02
  • The error talks about a simplemenuproc procedure while the question contains proc_Name. Are you sure storedProcedureName is correct? Commented Dec 18, 2024 at 8:12
  • @PanagiotisKanavos, Thank you for your response. The name conflict occurred because I forgot to change the name when posting the question. I attempted to set up the parameter as you suggested, but the same error persists. " parameters.Add("myDtTm", DateOnly.FromDateTime(DateTime.UtcNow), DbType.Date, direction: ParameterDirection.Input); " ERROR MessageText: procedure simplemenuproc(appcode => text, myDtTm => date, ref1 => unknown) does not exist Hint: No procedure matches the given name and argument types. You might need to add explicit type casts. Commented Dec 18, 2024 at 11:39
  • Try specifying the type for the third parameter parameters.Add("ref1", NpgsqlDbType.Refcursor).Direction = ParameterDirection.InputOutput; Commented Dec 18, 2024 at 11:43

2 Answers 2

Reset to default
0

You must know the fact that in PostgreSQL, name is insensitive. As your proc declaration, myDtTm parameter name will become mydttm. You need to use lowercase parameter name instead.

parameters.Add("myDtTm".ToLower(), DateOnly.Parse(DateTime.UtcNow.ToString("yyyy-MM-dd")), DbType.Date, direction: ParameterDirection.Input);

As my experience, you should use snake_case for naming, so that reverse engineering can understand and scaffolding Db-Context with names of classes and props are Pascal Case.

Furthermore, as my finding, Npgsql dropped support for refcursors. In the case that you want to read data from refcursors, you need to write raw query to manually fetch all data. If you remove ref1 from DynamicParameters and proc declaration, the call should be fine.

using System.Data;

using Dapper;

using Npgsql;

var connectionString = "Host=localhost:5433;Username=postgres;Password=postgres;Database=postgres";
await using var dataSource = NpgsqlDataSource.Create(connectionString);
var connection = await dataSource.OpenConnectionAsync();
using var transaction = await connection.BeginTransactionAsync();

var storedProcedureName = "simplemenuproc4";
/*
CREATE OR REPLACE PROCEDURE simplemenuproc4(
    appcode IN VARCHAR, 
    myDtTm DATE
)
LANGUAGE plpgsql
AS $BODY$
BEGIN

END;
$BODY$;
*/
var parameters = new DynamicParameters();
parameters.Add("appcode", "PPM", direction: ParameterDirection.Input);
parameters.Add("myDtTm".ToLower(), DateOnly.Parse(DateTime.UtcNow.ToString("yyyy-MM-dd")), DbType.Date, direction: ParameterDirection.Input);

await connection.ExecuteAsync(storedProcedureName, parameters, commandType: CommandType.StoredProcedure);

I implemented successfully that using Dapper to fetch all data from several cursors returned from User-defined functions.

I cannot share the code, but if you are interested in, I will create a minimum repro later.

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

Comments

-3

if you need now you can just do

SELECT NOW() AT TIME ZONE 'UTC' AS utc_now;

in .net

NpgsqlTransaction tran = connection.BeginTransaction();
var parameters = new DynamicParameters();
parameters.Add("appcode", "PPM", direction: ParameterDirection.Input);
parameters.Add("myDtTm", DateTime.UtcNow.Date, DbType.Date, direction: ParameterDirection.Input);
parameters.Add("ref1", direction: ParameterDirection.InputOutput);

// Execute the stored procedure
await connection.ExecuteAsync(storedProcedureName, parameters, commandType: CommandType.StoredProcedure);
Improve this answer

1 Comment

This doesn't answer the question at all.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.