How does System.Data.SQLite relate to .NET data types?
I am trying to find documentation on the behavior of System.Data.SQLite with respect to various .NET data types.
For example, how does System.Data.SQLite store .NET Booleans in a SQLite database? There are several possible ways:
- Integers 0 and 1
- Integers 0 and -1
- Text "True" and "False"
- Text 'T' and 'F'
- Text 'Y' and 'N'
- etc...
Conversely - how are Booleans parsed from SQLite? Does System.Data.SQLite assume a specific format? What is this format?
The lack of documentation around this is frustrating. Maybe I'm not looking in the right places?
NOTE: This is not a boolean question. I'm looking for documentation that explains the behavior of all .NET data types.
source to share
I suggest you start with the SQLite doc-agnostic documentation on the subject . It explains how booleans should be stored, and for example the various datetime serialization schemes.
For more details, System.Data.SQLite is open source and while it is a bit steep around certain edges, it is usually quite easy to read.
For example, a method GetValue()
(part of an implemented ADO.NET interface IDataReader
) in SQLiteDataReader.cs calls a named method GetSQLiteType()
, then performs slightly more automatic discovery based on some of the connection flags.
GetSQLiteType()
and all friends will go back to the SQLiteConvert class which does the actual type conversions and discovery. Conversions are all defined there (starting around the middle, after a lot of date manipulation helpers). Eventually you will reach this function, which is of particular relevance to your question:
internal static TypeAffinity TypeToAffinity(Type typ)
{
TypeCode tc = Type.GetTypeCode(typ);
if (tc == TypeCode.Object)
{
if (typ == typeof(byte[]) || typ == typeof(Guid))
return TypeAffinity.Blob;
else
return TypeAffinity.Text;
}
return _typecodeAffinities[(int)tc];
}
private static TypeAffinity[] _typecodeAffinities = {
TypeAffinity.Null, // Empty (0)
TypeAffinity.Blob, // Object (1)
TypeAffinity.Null, // DBNull (2)
TypeAffinity.Int64, // Boolean (3)
TypeAffinity.Int64, // Char (4)
TypeAffinity.Int64, // SByte (5)
TypeAffinity.Int64, // Byte (6)
TypeAffinity.Int64, // Int16 (7)
TypeAffinity.Int64, // UInt16 (8)
TypeAffinity.Int64, // Int32 (9)
TypeAffinity.Int64, // UInt32 (10)
TypeAffinity.Int64, // Int64 (11)
TypeAffinity.Int64, // UInt64 (12)
TypeAffinity.Double, // Single (13)
TypeAffinity.Double, // Double (14)
TypeAffinity.Double, // Decimal (15)
TypeAffinity.DateTime, // DateTime (16)
TypeAffinity.Null, // ?? (17)
TypeAffinity.Text // String (18)
};
In general, integer types will map correctly to and from SQLite (64-bit) integers, as well as strings. Arrays byte[]
and Guid
will work transparently, although both are saved as blobs. Boolean values map to 1 (true) and 0 (false) integers. And all SQLite datetime views are supported, and more: see method Bind_DateTime()
in SQLite3.cs .
source to share