How does System.Data.SQLite deal with .NET Data Types?

Question

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.

Answer 1

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.NET IDataReader interface that's implemented) in SQLiteDataReader.cs calls a method named GetSQLiteType(), 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:

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 get properly mapped to SQLite's (64-bit) integers and back, and ditto for strings. byte[] arrays and Guids 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 the Bind_DateTime() method in SQLite3.cs.