SQLiteDataReader OverflowException using SQLiteDataReader on decimal.MaxValue

Question

Problem

This is partially me being my own worst enemy. I have unit tests that verify my ability to write and then retrieve all different base data types to/from a SQLite database. Among my tests I verify several different values for each data type including (but not limited to) <datatype>.MinValue and <datatype>.MaxValue.

When I write a decimal.MaxValue to the database, then try to retrieve it, I get an Overflow Exception (thanks to rounding within the database itself).

Note: I have stripped my actual classes to the bare-bones and placed them inside a test method so I could show everything more easily.

private static SQLiteConnection connection;

[TestMethod()]
public void WriteDecimal()
{
  using (var cmd = new SQLiteCommand(connection))
  {
    cmd.CommandText = $"INSERT INTO foo(name, value) VALUES('bar', {decimal.MaxValue})";
    cmd.ExecuteNonQuery();

    cmd.CommandText = "SELECT * FROM foo;";

    using (SQLiteDataReader rdr = cmd.ExecuteReader())
    {
      while (rdr.Read())
      {
        Console.WriteLine($"{rdr.GetInt32(0)} {rdr.GetString(1)} {rdr.GetValue(2)}");
      }
    }
  }
}

#region Setup/Cleanup
[ClassInitialize()]
public static void Setup(TestContext context)
{
  FileInfo dbFile = new FileInfo(Path.Combine(Environment.GetEnvironmentVariable("temp"), @"\Sqlite\myDb.db"));
  dbFile.Directory.Create();
  dbFile.Delete();
  string connectionString = $"Data Source={dbFile?.FullName ?? ":memory:"}";
  connection = new SQLiteConnection(connectionString);
  connection.Open();

  using (var cmd = new SQLiteCommand(connection))
  {
    cmd.CommandText = @"CREATE TABLE foo(id INTEGER PRIMARY KEY, name TEXT, value Number)";
    cmd.ExecuteNonQuery();
  };
}

[ClassCleanup()]
public static void Cleanup()
{
  connection.Close();
}
#endregion

Output:

  Message: 
    Test method WriteDecimal threw exception:
    System.OverflowException: Value was either too large or too small for a Decimal.
  Stack Trace: 
    Number.ThrowOverflowException(TypeCode type)
    DecCalc.VarDecFromR8(Double input, DecCalc& result)
    IConvertible.ToDecimal(IFormatProvider provider)
    Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
    SQLite3.GetValue(SQLiteStatement stmt, SQLiteConnectionFlags flags, Int32 index, SQLiteType typ)
    SQLiteDataReader.GetValue(Int32 i)
    DatabaseDirect.WriteDecimal() line 54

Workaround

I found a workaround (I just don't like it). Essentially, I let it fail, then go back and try to grab it as a Double; then convert it to what I need; because it overflowed I know it has to either be the max value or the min value:

using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
  while (rdr.Read())
  {
    decimal newVal;
    try
    {
      newVal = (decimal)rdr.GetValue(2);
    }
    catch (OverflowException)
    {
      double val = rdr.GetDouble(2);
      Type t = rdr.GetFieldType(2);
      newVal = val > 0 ? decimal.MaxValue : decimal.MinValue;
    }
    Console.WriteLine($"{rdr.GetInt32(0)} {rdr.GetString(1)} {newVal}");
  }
}

Bigger Issue (as I see it)

This isn't the only place I encounter this issue. It also happens with decimal.MinValue and ulong.MaxValue. I'm not exactly a fan of my solution simply because I just assume that if there's an overflow I need the max/min value. I'd also like to generalize it so it doesn't hard-code the min/max values I may need. Again, I found a solution; but again, it is ugly (a function that passes in the type to convert the value to and then do a switch on it...yucky).

Answer 1

You might not be able to do this but when I got a decimal overflow error, I tried changing my sqlite column type from decimal to real and that eliminated the error.