I'm struggling to find documentation around System.Data.SQLite's behaviour in regards to the various .NET data types.
For example, how does System.Data.SQLite store .NET Booleans in an SQLite database? There are several possible methods:
- Integers 0 and 1
- Integers 0 and –1
- Text 'True' and 'False'
- Text 'T' and 'F'
- Text 'Y' and 'N'
- etc...
And vice-versa – how are Booleans parsed out of SQLite? Does System.Data.SQLite expect a certain format? What is that format?
The lack of documentation around this is frustrating. Maybe I'm not looking in the right places?
NOTE: This is not a question about Booleans specifically. I'm looking for documentation that explains behaviours for all .NET data types.
I suggest you start with the driver-agnostic SQLite documentation on the subject. It explains the way booleans should be stored, and the different datetime serialization schemes, for example.
For more details, System.Data.SQLite is open source, and while a bit crufty around certain edges, is generally quite easy to read.
For example, the
GetValue()
method (part of the ADO.NETIDataReader
interface that's implemented) in SQLiteDataReader.cs calls a method namedGetSQLiteType()
, then does a bit more auto-detection depending on some connection flags.GetSQLiteType()
and friends all lead back to the SQLiteConvert class, which does the actual type conversions and detection. The conversions are all defined there (starting about halfway through, after a lot of date manipulation helpers). Eventually you reach this function which is of particular relevance to your question:In general, integer types will get properly mapped to SQLite's (64-bit) integers and back, and ditto for strings.
byte[]
arrays andGuid
s will work transparently too, though both are stored as blobs. Boolean values are mapped to 1 (true) and 0 (false) integers. And all of the SQLite datetime representations are supported, and more: see theBind_DateTime()
method in SQLite3.cs.