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
DbTypewhich describes the data passed, not how you think it should be saved ( e.g.DbType.Guid, notBinaryfor aGuid). The provider will perform most conversions. - There is no
DbType.Imageso 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
UInt16works in reverse: trying to pass an out of range value, such as -5 for a UInt16, will result in anOverflow Exception. But it will return65531for 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
DBTypewhich determines the actual return data type - Using the correct
DbTypewhen saving data assures the data makes the round trip - The NET Provider will perform most conversions for you
- The
DateTimeKindandBindDateTimeWithKindoptions allow for automatic, uniform TimeZone storage of dates
Above all, the NET provider makes the actual storage an implementation detail.