{"Parameter must have its value set"} dealing with possible null values in NPGSQL

Question

I have a parameter that can take can either be a long or null,

here is my code

        public List<blahOject>GetBlahNOde(long? p_test)
        {
                    ...


                    cmd.Parameters.Add(new NpgsqlParameter
                    {
                        ParameterName = "test",
                        Value = p_test
                    });

        }

when i try to run my method i can an error of

{"Parameter 'test' must have its value set"}

not sure how to make this work with possible null values, p_test isnt always null and sometimes contains a value

Answer 1

To represent a null parameter value, set Value to DBNull.Instance, not null.

Answer 2

private static NpgsqlParameter safeNpgsqlParameter<T>( string parameterName, T? data ) where T : struct =>
    data switch {
        decimal => new NpgsqlParameter() { ParameterName = parameterName, NpgsqlDbType = NpgsqlDbType.Numeric, Value = data },
        Enum    => new NpgsqlParameter() { ParameterName = parameterName, Value = data },
        null    => new NpgsqlParameter() { ParameterName = parameterName, Value = DBNull.Value },
        LocalDate => new NpgsqlParameter() {
            ParameterName = parameterName,
            NpgsqlDbType  = NpgsqlDbType.Date,
            Value         = data
        },
        _ => throw new DbUpdateException()
    };

Use like:

// spec.Date is of type LocalDate?
cmd.Parameters.Add( safeNpgsqlParameter( "SomeDateParam", spec.Date ) );

// spec.MyEnum is of type MyEnum?
cmd.Parameters.Add( safeNpgsqlParameter( "MyEnumParam", spec.MyEnum ) );

// spec.Qty is of type decimal?
cmd.Parameters.Add( safeNpgsqlParameter( "SomeNumericParam", spec.Qty ) );

And of course other types could be added to the switch within safeNpgsqlParameter