Considering that the maximum size of a data type supported by SQLite is 8 bytes, can I store a Decimal.MinValue, Decimal.MaxValue in a NUMERIC column. I am using the ADO.NET provider for SQLite by phxsoftware. Does the driver internally store the value as a string and do the conversion both ways?
I do get a System.OverflowException while trying to retrieve the Decimal.MinValue/MaxValue.
No. The System.Decimal type has a much wider range of values (-79,228,162,514,264,337,593,543,950,335 :: 79,228,162,514,264,337,593,543,950,335) than the SQLite 8-byte INTEGER (-9,223,372,036,854,775,808 :: 9,223,372,036,854,775,807) and requires more precision than the SQLite REAL (IEEE-754) can provide.
From http://www.sqlite.org/datatype3.html:
A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. For conversions between TEXT and REAL storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved. If the lossless conversion of TEXT to INTEGER or REAL is not possible then the value is stored using the TEXT storage class. No attempt is made to convert NULL or BLOB values.
A string might look like a floating-point literal with a decimal point and/or exponent notation but as long as the value can be expressed as an integer, the NUMERIC affinity will convert it into an integer. Hence, the string '3.0e+5' is stored in a column with NUMERIC affinity as the integer 300000, not as the floating point value 300000.0.
If you compare -7.92281625142644e+28 to -79,228,162,514,264,337,593,543,950,335, you'll see that the floating point value has been rounded down to a value (-79,228,162,514,264,400,000,000,000,000) that is outside the bounds of what System.Decimal can store. Your ADO.NET provider is doing the best job it can, but because of the data conversions, it just won't work. If you really need to store the full range of System.Decimal, then you're going to have to store it as a text, and resign yourself to not being able to operate on the value as a number within SQLite.