SQLite not storing decimals correctly

Question

I have a sqlite DB with a table called tbl_invent, on form load it fills the datagridview with what is in the table. The problem is I have field names cost and sell_price which have decimals, and when the form loads it only shows the number not the decimal.

sample:

Table=1.75, DGV=1.00

   Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    connect()
    Dim da As New SQLiteDataAdapter("select * from tbl_Invent", connection)
    Dim ds As New DataSet
    da.Fill(ds, "tbl_Invent")
    DataGridView1.DataSource = ds
    DataGridView1.DataMember = "tbl_Invent"
    DataGridView1.Columns(6).ValueType = GetType(Single)
    DataGridView1.Columns(6).DefaultCellStyle.Format = "N2"
    DataGridView1.Columns(7).ValueType = GetType(Single)
    DataGridView1.Columns(7).DefaultCellStyle.Format = "N2"

    connection.Close()
    da.Dispose()
End Sub

i already check the field type it's correct "Integer", i also tried the "GetType(Single)" and "GetType(Decimal)" but still the same. any one could point me in the right direction? thank you.

---

From comments:

there is no other type in SQLite. there is only "Text", "Integer", "Real" and "Blob" also in SQLite it says integer can have decimals.

Answer 1

You did not indicate which DB provider you were using, but the standard provider (from the SQLite devs) will see Integer and map the data to the NET Int32 type which doesn't allow decimals. Real would save fractionals as would Decimal.

there is no other type in SQLite. there is only "Text", "Integer", "Real" and "Blob"

That's true but it applies to the SQLite DB, not the DB Provider. The standard DB Provider is cleverly written to be able to convert the 4 basic types to a variety of NET types such that the actual storage type/format becomes an implementation detail.

---

The provider code includes a number of steps, look-up tables, sub systems, dictionaries and methods to perform conversions. There is even a way to define custom type names. The following is a generalized explanation of the workings.

Column Type Names Recognized by the SQLite NET Provider

Byte, SByte
INT8, INTEGER8, TINYSINT (SByte) UINT8, UNSIGNEDINTEGER8, TINYINT (Byte)

Integral (short, long, signed, unsigned etc)
BIGINT, BIGUINT, COUNTER, IDENTITY, INT, INT16, INT32, INT64, INTEGER, INTEGER16, INTEGER32, INTEGER64, LONG, SMALLINT, SMALLUINT, UINT, UINT16, UINT32, UINT64, ULONG, UNSIGNEDINTEGER, UNSIGNEDINTEGER16, UNSIGNEDINTEGER32, UNSIGNEDINTEGER64

Boolean
BIT, BOOL, BOOLEAN, LOGICAL, YESNO

Text/String
CHAR, CLOB, LONGCHAR, LONGTEXT, LONGVARCHAR, MEMO, NCHAR, NOTE, NTEXT, NVARCHAR, STRING, TEXT, VARCHAR, VARCHAR2

Numeric
DOUBLE, FLOAT, REAL; SINGLE (Single)

Decimal
CURRENCY, DECIMAL, MONEY, NUMBER, NUMERIC

BLOB
BINARY, BLOB, GENERAL, IMAGE, OLEOBJECT, RAW, VARBINARY

Date/Time
DATE, DATETIME, SMALLDATE, TIME, TIMESTAMP

GUID
GUID, UNIQUEIDENTIFIER

Source: SQLiteDbTypeMap in SQLiteConvert.cs (version 1.0.103; September, 2016).

In essence, the DBProvider stores the data in the appropriate SQLite type, but when it is read back it uses the type you used in the table definition to convert the data back to a NET type. The SQLite provider includes a large SQLiteConvert class to do all the conversions for you.

---

I cannot find this documented in the wild, though it seems to be common knowledge to SQLite devotees. Most sites just reformat the SQLite site content. It might be documented in the help file, but mine has topics with no content. Given the list, it is easy to accidentally use a valid name and discover it works.

The list incorporates the most common notations used by other DBs, plus a few NET types. For example, Boolean can be defined as BIT, BOOL, BOOLEAN, LOGICAL or YESNO. As a result, this table definition is legal and fully functional:

CREATE TABLE LiteColTypes (
    Id        INTEGER     PRIMARY KEY AUTOINCREMENT,
    Name      TEXT,
    ItemDate  DATETIME,
    Char3     CHAR (3),
    UINT32    UINT32,
    Value     INT16,
    VarChar5  VARCHAR (5),
    GCode     GUID,
    Price     DECIMAL,
    ItemImg   IMAGE,
    Active    BOOL,
    NotActive YESNO
);

There are a few things to be aware of and some useful DateTime options.

How It Works

The list comes from this code:

/// <summary>
/// Builds and returns a map containing the database column types
/// recognized by this provider.
/// </summary>
/// <returns>
/// A map containing the database column types recognized by this
/// provider.
/// </returns>
private static SQLiteDbTypeMap GetSQLiteDbTypeMap()
{
return new SQLiteDbTypeMap(new SQLiteDbTypeMapping[] {
    new SQLiteDbTypeMapping("BIGINT", DbType.Int64, false),
    new SQLiteDbTypeMapping("BINARY", DbType.Binary, false),
    new SQLiteDbTypeMapping("BIT", DbType.Boolean, true),
    new SQLiteDbTypeMapping("BLOB", DbType.Binary, true),
    new SQLiteDbTypeMapping("BOOL", DbType.Boolean, false),
    new SQLiteDbTypeMapping("BOOLEAN", DbType.Boolean, false),
    ...
    new SQLiteDbTypeMapping("GUID", DbType.Guid, false),
    new SQLiteDbTypeMapping("IMAGE", DbType.Binary, false)
    ... (many more)

The XML comment was retained because it is illuminating and authoritative:

Builds and returns a map containing the database column types recognized by this provider. (emphasis mine).

The DbType is crucial to the process.

Reading Data

The above SQLiteDbTypeMap associates those many, many column names it recognizes to a DbType which is used to determine the NET data type to return. The list is comprehensive enough that it can convert all but 1 or 2 types for you.

For example, note that GUID and IMAG* are both stored as BLOB, but the GUID type name is associated with a different DbType which allows that BLOB to be returned differently than an IMAGE BLOB.

You can also specify types via the connection object. Space and scope does not permit an explanation, but while a bit tedious, it allows you to provide the data type for custom type names.

Storing Data

When storing data, you need not fret about how it should be stored. The DB Provider will use the DbType passed to look up SQLite type to use (Affinity"). If you use AddWithValue or the (obsolete) Add(object, object) overload, the DBProvider will guess at the type. It's pretty good at guessing, but dont do that.

So, this conversion is not needed:

cmd.Parameters.Add("@g", DbType.Binary).Value = myGuid.ToByteArray();

Use the same sort of code you would/should with any other database:

' // add trailing semicolons for c#
cmd.Parameters.Add("@n", DbType.String).Value = "Ziggy"
cmd.Parameters.Add("@dt", DbType.DateTime).Value = DateTime.Now 
cmd.Parameters.Add("@c3", DbType.StringFixedLength, 3).Value = "XYZ123" '// see notes
cmd.Parameters.Add("@u", DbType.UInt16).Value = 3
cmd.Parameters.Add("@g", DbType.Guid).Value = myGuid
    cmd.Parameters.Add("@p", DbType.Decimal).Value = 3.14D

'// 'ToByteArray()' is an extension method to convert
cmd.Parameters.Add("@img", DbType.Binary).Value = myImg.ToByteArray()
cmd.Parameters.Add("@act", DbType.Boolean).Value = True

Notes:

• Use the DbType which describes the data passed, not how you think it should be saved ( e.g. DbType.Guid, not Binary for a Guid). The provider will perform most conversions.
• There is no DbType.Image so a byte array conversion is needed.
• Specifying a size for a Char()/VarChar() field does not limit the number of characters saved. This seems like a bug because saving more characters than defined can prevent the row from loading.
• A UInt16 works in reverse: trying to pass an out of range value, such as -5 for a UInt16, will result in an Overflow Exception. But it will return 65531 for such a value already stored.
• Size/precision parameters such as Decimal(9,2) for a column doesn't seem to matter. An internal table provides fixed precision and sizes.
• For dates, pass dates and indicate DbType.DateTime. There is no need to pass strings of a particular format ever. The provider Knows Things. (See DateTime Options below.)
• To save the Date only, pass only the date: .Value = DateTime.Now.Date.

Two Different look-up tables are used for saving versus reading data, the one thing they have in common is the DbType which is why it is important. Using the correct one assures that data can make the round trip. Avoid using AddWithValue.

Demo / Results

Data view from a UI Browser

Nothing special is required to load data:

 // Dim SQL = "SELECT * FROM LiteColTypes"   ' for VB
 string SQL = "SELECT * FROM LiteColTypes";      
 ...
 dbCon.Open();
 Dim dt As New DataTable();
 dt.Load(cmd.ExecuteReader());
 dgv.DataSource = dt;

Same data in a DataGridView

A DGV correctly identifies and displays the GUID, Image and Boolean columns. The data types of each DataColumn are as expected:

       Name --->    System.String (maxLen = 2147483647)  
   ItemDate --->  System.DateTime  
      Char3 --->    System.String (maxLen = 3)  
     UINT16 --->    System.UInt16  
   VarChar5 --->    System.String (maxLen = 5)  
      GCode --->      System.Guid  
      Price --->   System.Decimal  
    ItemImg --->    System.Byte[]  
     Active --->   System.Boolean  
  NotActive --->   System.Boolean  

Note that Guid and Image items were both stored as BLOB but are returned differently. Active (BOOL) and NotActive (YESNO) used different type names but return the same data type. Everything works as desired.

DateTime "Issues" and Options

TIME as a column type name doesn't quite work as expected. It does not parse DateTime.Now.TimeofDay (Timespan) to it. The table maps TIME to DbType.DateTime.

Do not use DbType.DateTime2 or .DateTimeOffset. These are missing in converter look-ups so data is stored as Text in an invalid format (version 1.0.103.0).

UTC, Kind and Flags

The SQLite NET Provider does not support just one date format. When saving as UTC, the data includes an indicator. But, whether saved as Local or UTC, the Kind always returns as Unspecified. Part of the remedy for this is to add datetimekind to your connection string:

`...;datetimekind=Utc;`
`...;datetimekind=Local;`   

This will set the Kind for all DateTime values returned but without converting the value.

The remedy for this is to use the (relatively) new BindDateTimeWithKind connection flag. This will convert dates to match the DateTimeKind of the connection when saved:

Private LiteConnStr = "Data Source='C:\Temp\demo.db';Version=3;DateTimeKind=Utc;"
...
Dim dt As New DateTime(2011, 2, 11, 11, 22, 33, 444, DateTimeKind.Local)

Using dbCon = New SQLiteConnection(LiteConnStr)
    dbCon.Flags = SQLiteConnectionFlags.Default Or 
                  SQLiteConnectionFlags.BindDateTimeWithKind
    ...
    cmd.Parameters.Add("@dt", DbType.DateTime).Value = dt

    ' == 2011-02-11 17:22:33.444Z   note the hour

Though a local date was passed, BindDateTimeWithKind results in it being saved as UTC to match the connection. A UTC date is returned due to the "DateTimeKind=Utc;" connection setting.

Note that DateTimeKind works on dates read while BindDateTimeWithKind acts when saving dates. Individually they can seem to make things worse; together the entire database becomes UTC based (or Local) with dates uniformly saved and read as the same Kind -- you need not do anything.

ConnectionFlags can be tedious to work with manually, to specify them in the connection string:

connx = "...;datetimekind=Utc;flags='Default, BindDateTimeWithKind';"

Limitation / Issue

The uniform Kind treatment works well with a DbDataReader and at least with Dapper. But when using a DataTable, the Kind for dates remains Unspecified. This is apparently due to the DateTimeMode property in DataColumn and probably a design decision by Microsoft not to assume all dates in a column will always be the same Kind. This manifests in other DBs as well.

When using a UTC or Local connection, the provider leaves Unspecified alone (this applies to dates in queries as well). So there should not be any undesired extra conversions: a UTC date read and 'disguised' as Unspecified in a DataTable isn't converted again in updates.

Ticks Format

Contrary to 'conventional wisdom' dates are not always and only saved as TEXT; to save a little space, you can save the tick value. Since these cannot have a Time Zone indicator, the Kind related options can be very useful. To enable Ticks, use the DateTimeFormat connection string option:

Private LiteConnStr = "...;datetimekind=Utc;DateTimeFormat=Ticks;..."
'e.g: 634939900800000000

Other DateTimeFormat options include CurrentCulture, ISO8601 (the default), JulianDay and UnixEpoch. There is no need to change the column type name to use one of these formats. It is still a date, the SQLite Provider handles the implementation details based on the connection flags.

UI Browsers

Many SQLite UI Browsers seem to only know about the four canonical types. Perhaps this is intentional, but this limits their usefulness for NET developers and hides the capabilities of the NET provider.

SQLiteStudio (version: 3.1.0) offers a few more, but it doesn't seem to be aware of the complete list, because some very useful ones are missing (e.g. GUID, IMAGE, SINGLE, integer variants).

It does allow you to enter any type name you want, so Profit!

Summary (tl;dr)

• The NET Provider adds functionality to SQLite by supporting a wide variety of type names for columns,
• Each supported name is associated with a DBType which determines the actual return data type
• Using the correct DbType when saving data assures the data makes the round trip
• The NET Provider will perform most conversions for you
• The DateTimeKind and BindDateTimeWithKind options allow for automatic, uniform TimeZone storage of dates

Above all, the NET provider makes the actual storage an implementation detail.