System.Data.SQLite

It's a core feature of SQLite to allow storing values of any type in any column. However, SQLiteDataReader doesn't always correctly handle this. See failed tests below.


SQLiteConnection connection = ...;

new SQLiteCommand("CREATE TABLE test_1 (col INTEGER);", connection).ExecuteNonQuery();
new SQLiteCommand("INSERT INTO test_1 VALUES ('my string value');", connection).ExecuteNonQuery();
Assert.AreEqual("my string value", new SQLiteCommand("SELECT col FROM test_1;", connection).ExecuteScalar());
// FAIL - Expected: "my string value" - Actual: (long)0

new SQLiteCommand("CREATE TABLE test_2 (col REAL);", connection).ExecuteNonQuery();
new SQLiteCommand("INSERT INTO test_2 VALUES ('my string value');", connection).ExecuteNonQuery();
Assert.AreEqual("my string value", new SQLiteCommand("SELECT col FROM test_2;", connection).ExecuteScalar());
// FAIL - Expected: "my string value" - Actual: (double)0

new SQLiteCommand("CREATE TABLE test_3 (col BLOB);", connection).ExecuteNonQuery();
new SQLiteCommand("INSERT INTO test_3 VALUES (-1);", connection).ExecuteNonQuery();
Assert.AreEqual((long)-1, new SQLiteCommand("SELECT col FROM test_3;", connection).ExecuteScalar());
// FAIL - Expected: ((long)-1) - Actual: byte[] { 0x2D, 0x31 }


This also affects the SQLiteDataReader functions GetValue(int i), GetValues(), this[int i], and this[string name].

If you want to be able to store data of the "wrong type" in a column, you can
disable the type sanity checking using one of several connection flags, e.g.:

/// <summary>
/// When returning column values, always return them as though they were
/// plain text (i.e. no numeric, date/time, or other conversions should
/// be attempted).
/// </summary>
GetAllAsText = 0x100,

/// <summary>
/// When returning column values as a <see cref="String" />, skip
/// verifying their affinity.
/// </summary>
NoVerifyTextAffinity = 0x200000000,

/// <summary>
/// When returning column values, skip verifying their affinity.
/// </summary>
NoVerifyTypeAffinity = 0x4000000000,

Ah, OK. Sorry for this false bug report then.

No problem.  I may need to enhance the documentation to make these kinds of data
type affinity issues a bit easier to deal with.

Thanks for the report.