Problem about SQLite's integer and int datatype

Question

I meet a problem about: Object of type 'System.Int64' cannot be converted to type 'System.Int32' when run the SubSonic.Examples.SimpleRepo project base on SQLite provider.

I fond that the data type for table Categories's column CategoryID is 'integer', and the 'integer' in SQLite will be return as Int64, in the same time the CategoryID's data type in the Class Category is int, the above error is happened.

I checked the SubSonic's source code: \SubSonic.Core\SQLGeneration\Schema\SQLiteSchema.cs and find the following codes:

else if (column.IsPrimaryKey && column.DataType == DbType.Int32
    || column.IsPrimaryKey && column.DataType == DbType.Int16
    || column.IsPrimaryKey && column.DataType == DbType.Int64
    )
    sb.Append(" integer ");

Who can tell me the purpose for these codes? How to solve the data type convert error?

Answer 1

Funny I just read the sqlite3 documentation on this about an hour ago. So you're in luck :)

See the doc yourself (Scroll to the bottom, 64-bit ROWIDs section).

Here's the excerpt:

To minimize storage space, the 64-bit rowid is stored as a variable length integer. Rowids between 0 and 127 use only a single byte. Rowids between 0 and 16383 use just 2 bytes. Up to 2097152 uses three bytes. And so forth. Negative rowids are allowed but they always use nine bytes of storage and so their use is discouraged. When rowids are generated automatically by SQLite, they will always be non-negative.

Answer 2

The PrimaryKey is set to int 64 (long) in SQLite - so you need to set yours to long as well.