Npgsql 4.0 Parameters and Null Values

Question

Passing a null value using Npgsql looks something like this:

using (NpgsqlCommand cmd = new NpgsqlCommand("insert into foo values (:TEST)", conn))
{
    cmd.Parameters.Add(new NpgsqlParameter("TEST", NpgsqlDbType.Varchar));
    cmd.Parameters[0].Value = DBNull.Value;

    cmd.ExecuteNonQuery();
}

Which works fine.

The new Npgsql 4.0 documentation recommends declaring parameters with a strong datatype, like this:

using (NpgsqlCommand cmd = new NpgsqlCommand("insert into foo values (:TEST)", conn))
{
    cmd.Parameters.Add(new NpgsqlParameter<string>("TEST", NpgsqlDbType.Varchar));
    cmd.Parameters[0].Value = DBNull.Value;

    cmd.ExecuteNonQuery();
}

When the DBNull.Value is passed, a general exception is thrown:

Unable to cast object of type 'System.DBNull' to type 'System.String'.

Everything still works with the new boxing-less parameters, but the new syntax seems to make sense, and we want to use it... but how to resolve this datatype disconnect?

The example above is with a string. I assume this will also impact numerics and dates.

Answer 1

I faced with this problem, and now I don't get exceptions with it

Here how I declare parameters for Postgres functions:

string test;
using (NpgsqlCommand cmd = new NpgsqlCommand("insert into foo values (:TEST)", conn))
{
    cmd.Parameters.AddWithValue("TEST", NpgsqlTypes.NpgsqlDbType.Varchar, (object)test?? DBNull.Value);
    cmd.ExecuteNonQuery();
}

Answer 2

The new generic parameter API indeed has an issue - it should be accepting regular .NET null (and not DBNull.Value), I've opened this issue to track this, it will be fixed in 4.0.3.

Note that as the documentation note says, the whole point of the generic API is to avoid using the Value property, which is of type object. If you use the generic NpgsqlParameter<int> but assign Value, your int will be boxed, defeating the purpose of the API. You should be assigning to TypedValue, which is of type int and will not box. This is also why you cannot assign DBNull.Value to indicate a null value (it's a different .NET type).

Some notes on whether this new generic API should be used:

• If you're writing lots of value types (e.g. int, DateTime...) this will remove all boxing allocations. Whether this is going to be significant depends on your application - profile carefully.
• Generic APIs in general should always be preferred to non-generic ones when the type is known at compile-time. This allows the compiler to check type correctness early and makes your code clearer - we use List<string> rather than ArrayList as a matter of good coding even when performance isn't an issue
• The main (only?) drawback of the generic API is that it's Npgsql-specific, making your code non-portable to other database drivers (although an issue exists for making this (or something similar) part of ADO.NET).