System.Data.SQLite

I have a table with a field of type string. I access the data using a DataReader and retrieve the value using the GetString() method. If the value happends to be a a string representation of an integer, eg "17500", then GetString() throws an InvalidCastException. An easy workaround is to use dr.GetValue(0).ToString() but it still seems to me that this should not happen. Here is a copy of commands and output typed in the Visual Studio immediate window at a breakpoint just before this problem occurs:

? dr.GetValue(0)
"17500"
? dr.GetDataTypeName(0)
"String"
? dr.GetString(0)
'dr.GetString(0)' threw an exception of type 'System.InvalidCastException'
    Data: {System.Collections.ListDictionaryInternal}
    HResult: -2147467262
    HelpLink: null
    InnerException: null
    Message: "Specified cast is not valid."
    Source: "System.Data.SQLite"
    StackTrace: "   at System.Data.SQLite.SQLiteDataReader.VerifyType(Int32 i, DbType typ)\r\n   at System.Data.SQLite.SQLiteDataReader.GetString(Int32 i)"
    TargetSite: {System.Data.SQLite.TypeAffinity VerifyType(Int32, System.Data.DbType)}

It would be very helpful to know the type of the column in question, as declared
in the schema.

Also, as a temporary workaround, you can add the "NoVerifyTextAffinity"
connection flag in the connection string.

Many thanks for looking at this.

The Type of the field in the schema is String. In this case it is also the Primary Key for the table but I don't think this makes a difference.

The type name in the database schema is literally "String"?  Also, what is an
example of a query where this issue is seen?

Here's some quick code that reproduces the error (on 64-bit Windows). The directory c:\test must exist.

System.Data.SQLite.SQLiteConnection SQLiteConn = new System.Data.SQLite.SQLiteConnection(@"Data Source=c:\test\test.db;Version=3;Pooling=True;Max Pool Size=100;");
            System.Data.SQLite.SQLiteCommand cmd = SQLiteConn.CreateCommand();
            SQLiteConn.Open();
            IDataReader dr = null;
            string s;
            try
            {
                cmd.CommandText = "Create Table  test (Code String NOT NULL DEFAULT '')";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO TEST (Code) VALUES ('Test')";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO TEST (Code) VALUES ('174500')";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT CODE FROM TEST";
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    s = dr.GetString(0); //invalid cast exception here on 2nd row
                    Console.WriteLine(s);
                }

            }
            finally
            {
                dr.Close();
                cmd.CommandText = "DROP table test";
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SQLiteConn.Close();
                SQLiteConn.Dispose();
            }

I'm able to reproduce the exception here.  The root cause of this issue is that
the SQLite core library returns 'integer' affinity for the second value, partly
due to its not considering the declared column type ('String') to have textual
affinity.

Everything appears to be working as designed.  There are two potential solutions
to this issue:

1. Change the column type to be 'TEXT' instead of 'String', which should cause
   the SQLite core library to return 'TEXT' affinity for the column value, even
   if it looks like an integer.

2. Use the connection flag 'NoVerifyTextAffinity', which will prevent exceptions
   from being thrown by the System.Data.SQLite type conversion subsystem.

The #1 solution is preferred in this case, if possible, because it reduces
the amount of unnecessary type conversion work performed by the SQLite core
library and the System.Data.SQLite code.