[Dapper.Contrib - Update] SqlDateTime overflow error

[dehghani-mehdi]

Hi,

I'm using Dapper v1.50.5 v2.0.30 and Dapper.Contrib v1.50.5 v2.0.30, here is my model:

public class Foo
{
    public int Id { get; set; }
    public string Title { get; set; }
    public DateTime CreatedDate { get; set; }
}

And here is my code for update single record:

using (var db = new SqlConnection(SQLConnection.ConnectionString))
{
    db.Open();

    db.Update(new Foo{ Id = 8, Title = "222" });
}

I got:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Dapper.Contribmakes SQL happy if I assign some value to CreatedDate, e.g: CreatedDate = DateTime.Now, but it is created date and should not be changed on each update.

[Willberforce]

I think it will be because your datetime properties "CreatedDate" and "LastUpdatedDate" aren't nullable. They will default to the minimum datetime in .Net code, which is January 1, 0001, in the Gregorian calendar.

This is earlier than the minimum allowed by the SqlDateTime type (1/1/1753 12:00:00 AM).

[dehghani-mehdi]

@Willberforce I'm not inserting new record, I'm updating existing one (the exiting record is valid).

[Willberforce]

Have a look at the code - https://github.com/StackExchange/Dapper/blob/master/Dapper.Contrib/SqlMapperExtensions.cs

In particular the method on line 403... public static bool Update<T>(this IDbConnection connection, T entityToUpdate, IDbTransaction transaction = null, int? commandTimeout = null) where T : class

...and line 442... var nonIdProps = allProperties.Except(keyProperties.Union(computedProperties)).ToList();

Although you are calling update - the c# object has a new DateTime on it as it isn't nullable.

The Update extension method cycles through ALL the properties on the type (EC_Category) and will update the CreatedDate and LastUpdatedDate.

[dehghani-mehdi]

Thanks for your time bro. For the record, using Get and then updated returned record with Update works. so yes, there some ways to fix the problem, but the first problem is an issue (I guess), so I opened this issue to help the library.

[dehghani-mehdi]

Any update here?!

[thedeerhunter]

Dapper is converting every property to it's SQL Equivalent, and this happens before any update is even done. In this case, it is converting your property, CreatedDate to a SQL Server DateTime datatype. Unfortunately, the C# default doesn't have an equivalent SQL Sever value, so you get that error.

I don't ever update tables like your doing here, as I always use a stored proc. However, that won't necessarily fix this issue either, but it will give you much more flexibility when it comes to handling something like this.