I am writing a general repository class that has generic types in it. For every domain object(which implements an interface called IEntity) I have ins, upd and del procedures in my PostgreSql database.

I want to call those procedures with dapper and get values of inout parameters of the called procedure.

I tried to use Dapper ‘ExecuteAsync’ extension method on ‘IDbConnection’ with ‘DynamicParameters’. But since ‘DbType’ doesn’t have uuid or json, it didn’t work.

Procedure Definition:

CREATE OR REPLACE PROCEDURE <schema_name>.<entity_name>_ins(
    IN i_config json,
    IN i_row json,
    INOUT o_result integer DEFAULT 1,
    INOUT o_status_id integer DEFAULT 0,
    INOUT o_status_message text DEFAULT 'OK'::text,
    INOUT o_id bigint DEFAULT NULL::bigint,
    INOUT o_rowid uuid DEFAULT NULL::uuid,
    INOUT o_row json DEFAULT NULL::json)

My Code for calling it:

public async Task<bool> InsertAsync(T entity, ConfigObject configObject)
{
    _dbConnection.Open();
    try
    {
        var iConfig = JsonSerializer.Serialize(configObject, _serializerOptions);
        var iRow = JsonSerializer.Serialize(entity, _serializerOptions);
        var parameters = new DynamicParameters();
        parameters.Add("@i_config", iConfig, direction: ParameterDirection.Input);
        parameters.Add("@i_row", iRow, direction: ParameterDirection.Input);
        parameters.Add("@o_result", direction: ParameterDirection.InputOutput);
        parameters.Add("@o_status_id", direction: ParameterDirection.InputOutput);
        parameters.Add("@o_status_message", direction: ParameterDirection.InputOutput);
        parameters.Add("@o_id", direction: ParameterDirection.InputOutput);
        parameters.Add("@o_rowid", direction: ParameterDirection.InputOutput);
        parameters.Add("@o_row", direction: ParameterDirection.InputOutput);
        var procName = $"{T.ISchemaName}.{T.IEntityName}_ins";
        await _dbConnection.ExecuteAsync(procName, parameters, commandType: CommandType.StoredProcedure);
        int oResult = parameters.Get<int>("@o_result");
        int oStatusId = parameters.Get<int>("@o_status_id");
        string oStatusMessage = parameters.Get<string>("@o_status_message");
        long oId = parameters.Get<long>("@o_id");
        Guid oRowId = parameters.Get<Guid>("@o_rowid");
        string oRow = parameters.Get<string>("@o_row");
        if(oResult == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    catch (Exception e)
    {
        _logger.LogError("Get error: {error}", e);
        return false;
    }
    finally
    {
        _dbConnection.Close();
    }
}

The error I get with my code:

Npgsql.PostgresException: '42883: procedure db_info.entity_ins(i_config => text, i_row => text, o_result => unknown, o_status_id => unknown, o_status_message => unknown, o_id => unknown, o_rowid => unknown, o_row => unknown) does not exist

How can I call this types of procedures with uuid and json parameters with Dapper ? Is there a way for this or do I have to use NpgSql classes and methods to achieve this ?