Updating DateTime to database is throwing SqlTypeException - SqlDateTime overflow

Question

Executing following line of code:

conn.Update(CashInItem)

throws an exception:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM (line 465 in Contrib var updated = connection.Execute(sb.ToString(), entityToUpdate, commandTimeout: commandTimeout, transaction: transaction);)

I am testing Dapper.Contrib.
I have a table in SQL Server that has a few DateTime columns - some of them allow NULL values.
I created an object with properties to match the columns in the table. For the DateTime columns, the properties are nullable.

Here is an example of one of the properties:

public DateTime? ReconciledOn { get; set; }

I first use IDbConnection.Query method to get a record from the SQL table. This runs OK and the object mapping is fine. When I check on of the nullable DateTime values it shows null.

I then, make a simple change to string parameter and call the following:

static bool Update(CashIn CashInItem)
{
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString))
    {
        return conn.Update(CashInItem); //Error on this line
    }
}

How can I fix this issue?

Answer 1

After cleaning up my code it worked.

At first, I was testing just Dapper and then added Dapper.Contrib. I had references to both. I now believe that the reason was that the object was loaded using Dapper.Query which was then used in Dapper.Contrib Update. After cleaning my code looks like that

static void Main(string[] args)
{
  string dump = "";
  using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AchieveDB"].ConnectionString)){
  conn.Open();
  CashIn oCashIn = conn.Get<CashIn>(59458);
  dump = ObjectDumper.Dump(oCashIn);
  Console.WriteLine(dump);
  Console.WriteLine("Updating");
  Console.WriteLine("=========");

  oCashIn.ReconciledOn = DateTime.Now;
  dump = ObjectDumper.Dump(oCashIn);
  Console.WriteLine(dump);
  conn.Update <CashIn>(oCashIn);
}

References: using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Diagnostics; using Dapper.Contrib; using Dapper.Contrib.Extensions; using ObjectDumping;

The SQL statements Contrib sent to the database: For the Get exec sp_executesql N'select * from CashIn where CashInId = @id',N'@id int',@id=59458

For the Update exec sp_executesql N'update CashIn set [ -- then all Fields = matching param then list of params and values.

Answer 2

You are using SQL datatype DateTime to store value in SQL Server. The valid range of values for this datatype is:

January 1, 1753, through December 31, 9999

To represent this column in your code, you are using C# datatype DateTime. The valid range of values for this datatype is:

The DateTime value type represents dates and times with values ranging from 00:00:00 (midnight), January 1, 0001 Anno Domini (Common Era) through 11:59:59 P.M., December 31, 9999 A.D. (C.E.) in the Gregorian calendar.

Your C# property is nullable; but that is not a problem.

Problem is, somewhere in your code, you are instantiating the property to new DateTime() which then holds its default value 01-Jan-0001 12:00:00 AM which ultimately is out of the valid range of SQL datatype and hence the exception.

You have not provided enough code in question; so you have to debug this yourself where this assignment is happening.