Issue replacing prepared query parameters in npgsql prepared command

Question

Good afternoon,

I'm trying to execute the following query from a piece of c# code:

SELECT \"schema\".\"table\".\"field1\" AS field1, 
     SUM( \"schema\".\"table\".\"num1\" * ( CASE WHEN @type IS NULL THEN 1 WHEN @type = 1 THEN \"schema\".\"table2\".\"value1\" WHEN @type = 2 THEN \"schema\".\"table2\".\"value2\" END) )
FROM ...

from my c# piece of code I replace the parameter @type with a proper value

if (...)
     command.Parameters.AddWithValue("@type", DBNull.Value);
else if(...)
     command.Parameters.AddWithValue("@type", 1);
else
     command.Parameters.AddWithValue("@type", 2);

It result in an exception when it try to replace @type with DBNull.Value, if I use another query condition (e.g. = 0) instead of IS NULL it works.

The underlying database is postgresql and I'm using npgsql.

Anybody knows why this happens?

Thanks in advance

Answer 1

You can declare the datatype. It is always good to specify it to avoid wrong mapping, and it is mandatory when the type cannot be automatically found:

NpgsqlParameter param = new  NpgsqlParameter("@type", System.Data.DbType.Int16);
param.Value = DBNull.Value;

command.Parameters.Add(param);