How to save c# (asp.net core) datetime to PostgreSQL date

Question

I am using the npgsql package to pass parameters to a postgresql function. But I am getting exception saying that a function with the specified number/type of arguments is not defined. I went throught the trouble of testing all the parameters and I am sure the one that is causing the problem is the c# datetime parameters that is passed to a postgresql date data type.

I orginally tried this:

  //here BirthDate is Datetime, as it doesn't seem
   // to have another built-in date type in asp.net core
  cmd.Parameters.AddWithValue("@birth_date", cliente.BirthDate);

I read some post here in SO and they said that using the property Date would solve but it didn't work for me.

  //doesn't work either
  cmd.Parameters.AddWithValue("@birth_date", cliente.BirthDate.Date);

Someone posted a comment but removed it, it said for me to use cmd.Parameters.Add() and it worked. cmd.Parameters.Add("@birth_date", NpgsqlTypes.NpgsqlDbType.Date, 20).Value = cliente.BirthDate.Date; — Diego Alves
– Diego Alves, Commented Feb 25, 2018 at 16:45

Hambone · Accepted Answer · 2018-03-03 16:01:11Z

NpgSql's AddWithValue does its best to map the datatype of the C# property to the PostgreSQL field's datatype, but the issue is that PostgreSQL has two primary datatypes for date:

date timestamp

But C# has just the one -- System.DateTime. So even the .Date Property of a DateTime object yields a DateTime type.

In other words, NpgSql has to make a choice -- and it renders the PostgreSQL datatype as a timestamp. If your DB type is actually a date, the execution will fail due to datatype mismatch.

For strings, integers, decimals and doubles AddWithValue always seems to work exactly as expected, but if your date is a [PostgreSQL] DATE then you need to be explicit with your parameter declaration's datatype. In general, this is a good practice anyway:

cmd.Parameters.Add(new NpgsqlParameter("@birth_date", NpgsqlTypes.NpgsqlDbType.Date));
cmd.Parameters[0].Value = cliente.BirthDate;

This is definitely advantageous over AddWithValue if you are doing multiple transactions like this:

cmd.Parameters.Add(new NpgsqlParameter("@birth_date", NpgsqlTypes.NpgsqlDbType.Date));
cmd.Parameters.Add(new NpgsqlParameter("@user_id", NpgsqlTypes.NpgsqlDbType.Integer));

foreach (User u in Users)
{
    cmd.Parameters[0].Value = u.Birthday;
    cmd.Parameters[1].Value = u.UserId;
    cmd.ExecuteNonQuery();
}

For a single query or transaction, you can also use the Shorthand:

cmd.Parameters.Add(new NpgsqlParameter("@birth_date", NpgsqlTypes.NpgsqlDbType.Date)).
    Value = cliente.BirthDate;

As a final alternative, you can "fix" the dbtype after the fact:

cmd.Parameters.AddWithValue("@birth_date", cliente.BirthDate);
cmd.Parameters[0].NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date;

I don't think that's any easier than the other options, but it is an option that will work.

Collectives™ on Stack Overflow

How to save c# (asp.net core) datetime to PostgreSQL date

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related