System.Data.SQLite

Integers are being returned from queries as 32 bit integers even through the value is too big.  I am not sure if you intend for the integers to be returned as Int32 or Int64.  I expected them all to be returned as Int64, but I cast them Int32 to get the first 3 Asserts to pass.  The 4th Asserts fails and does not have the correct value.



        [TestMethod]
        public void Int_Sqlite()
        {
            SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
            builder.DataSource = ":memory:";
            builder.FailIfMissing = false;
            builder.JournalMode = SQLiteJournalModeEnum.Memory;
            builder.SyncMode = SynchronizationModes.Off;

            byte testValue8 = byte.MaxValue;
            short testValue16 = short.MaxValue;
            int testValue32 = int.MaxValue;
            long testValue64 = long.MaxValue;


            using (SQLiteConnection connection = new SQLiteConnection(builder.ConnectionString))
            {
                connection.Open();

                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = "CREATE TABLE TestValues (Value8 INT NOT NULL, Value16 INT NOT NULL, Value32 INT NOT NULL, Value64 INT NOT NULL)";
                        command.Transaction = transaction;

                        command.ExecuteNonQuery();
                    }

                    transaction.Commit();
                }

                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = "INSERT INTO TestValues (Value8, Value16, Value32, Value64) VALUES (@Value8, @Value16, @Value32, @Value64)";
                        command.Transaction = transaction;

                        command.Parameters.AddWithValue("@Value8", testValue8);
                        command.Parameters.AddWithValue("@Value16", testValue16);
                        command.Parameters.AddWithValue("@Value32", testValue32);
                        command.Parameters.AddWithValue("@Value64", testValue64);

                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }


                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = "SELECT * from TestValues";
                        command.Transaction = transaction;

         
                        using (SQLiteDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                Assert.AreEqual((int)testValue8, reader[0]);
                                Assert.AreEqual((int)testValue16, reader[1]);
                                Assert.AreEqual((int)testValue32, reader[2]);
                                Assert.AreEqual((long)testValue64, reader[3]);
                            }
                        }
                    }
                    transaction.Commit();
                }
            }
        }

The root cause of the issue you are seeing is the declared type names of the
table columns.

The SQLite core library itself does not enforce formal column types.

That being said, System.Data.SQLite does enforce formal column types using
several mechanisms.

In this case, the "INT" type name is being interpreted to mean "this must be
a 32-bit integer, truncate accordingly".

Using "INT64", "INTEGER", or "INTEGER64" will do what you probably intend.