System.Data.SQLite Always Returns Integers as Int64: Causes and Solutions

Understanding System.Data.SQLite’s Integer Type Mapping Behavior

SQLite Storage Semantics and .NET Type System Interactions

SQLite employs a dynamic typing system where columns have type affinity rather than rigid data types. When a column is declared as INTEGER, it accepts values stored in variable-length formats (0, 1, 2, 3, 4, 6, or 8 bytes) depending on the magnitude of the integer. For example:

• Values 0 and 1 are stored in 0 bytes (using header flags).
• Values between -32768 and 32767 (Int16 range) use 2 bytes.
• Values between -2147483648 and 2147483647 (Int32 range) use 4 bytes.
• Larger integers (up to 64-bit) use 8 bytes.

The .NET ecosystem, however, relies on static typing. System.Data.SQLite—a .NET wrapper for SQLite—must map SQLite’s flexible storage to .NET’s fixed-width types (Int16, Int32, Int64). Developers expect methods like IDataReader.GetInt32(column) to return exactly the type specified.

The disconnect arises because SQLite’s storage details are abstracted away. System.Data.SQLite cannot dynamically return Int16/Int32/Int64 based on the stored byte size without violating .NET interface contracts. For example, a column might contain 4-byte integers today but 8-byte integers tomorrow. If the wrapper returned Int32 initially, future 64-bit values would cause overflows or require breaking changes.

Statically Typed Interfaces and Type Safety Constraints

System.Data.SQLite implements the IDataReader interface, which defines explicit methods for retrieving data as specific types:

short GetInt16(int column);  
int GetInt32(int column);  
long GetInt64(int column);  

These methods must return their declared types. The default GetValue(column) method returns a long (Int64) because:

1. Backward Compatibility: Early SQLite versions stored all integers as 64-bit.
2. Safety: Promoting smaller integers to Int64 avoids data loss. For example, if a column contains a mix of 2-byte and 8-byte integers, returning Int64 ensures all values are representable.
3. Interface Uniformity: The SQLiteDataReader cannot infer the "correct" .NET type at runtime without additional schema metadata.

When a developer writes:

var value = reader.GetValue(0);  

The runtime type of value is long (Int64), even if the stored integer is 4 bytes. This behavior is intentional—not a bug—to guarantee type consistency across all rows.

COM Interop and Legacy System Limitations

The problem intensifies when System.Data.SQLite is used in COM-visible assemblies consumed by 32-bit clients like Excel VBA. These environments often lack full support for Int64:

• Excel VBA’s Long type is 32-bit (Int32).
• Passing Int64 values via COM triggers type mismatch errors.
• Workarounds require explicit casting, which introduces overhead or risks overflows.

Developers working in hybrid .NET/COM ecosystems face a dilemma: Use GetInt32() and risk exceptions for large values, or accept Int64 and handle COM limitations.

Root Causes of Int64-Only Integer Returns

Cause 1: SQLite’s Type-Agnostic Storage Model

SQLite does not preserve the original declared type of integer values. Declaring a column as INT32 during CREATE TABLE only sets its affinity—it does not enforce storage size. The actual storage format is determined by the value’s magnitude at insertion time. For example:

CREATE TABLE Data (Value INT32);  
INSERT INTO Data VALUES (42);      -- Stored as 1-byte integer  
INSERT INTO Data VALUES (420000);  -- Stored as 4-byte integer  

System.Data.SQLite has no runtime visibility into the physical storage size of integers. It sees only the logical value (e.g., 42), not whether it was stored in 1, 2, or 4 bytes. Thus, it cannot map values to Int16/Int32 retroactively.

Cause 2: .NET Interface Contracts Require Fixed Return Types

The IDataReader interface enforces strict return types for its methods. If GetInt32() returned a value stored in 2 bytes, it would still work, but GetInt64() would require widening. Conversely, if a 4-byte integer is later replaced with an 8-byte integer, GetInt32() would throw an InvalidCastException. To avoid this unpredictability, System.Data.SQLite defaults to the widest type (Int64) for all integers when using GetValue().

Cause 3: Lack of Schema Metadata for Storage Details

SQLite’s sqlite3_column_bytes() C API function returns the storage size of a value, but this metadata is not exposed in System.Data.SQLite’s high-level APIs. Without access to storage size, the wrapper cannot conditionally return Int16/Int32/Int64.

Strategies for Handling Int64 Mapping and COM Interop

Solution 1: Explicit Casting in C# Code

For COM interoperability, cast Int64 values to Int32 before exposing them:

using (var reader = command.ExecuteReader()) {  
  while (reader.Read()) {  
    long value64 = reader.GetInt64(0);  
    int value32 = checked((int)value64); // Throws OverflowException if out of range  
    // Pass value32 to COM clients  
  }  
}  

Advantages:

• Simple implementation.
• checked keyword ensures safety.

Drawbacks:

• Manual casting adds boilerplate.
• Requires ensuring values never exceed Int32.MaxValue.

Solution 2: Use SQL CAST in Queries

Modify SQL queries to cast integers to smaller types:

var command = new SQLiteCommand("SELECT CAST(Value AS INTEGER) AS Value FROM Data", connection);  

Note: This does not force 4-byte storage but signals the wrapper to attempt Int32 conversion. Results vary based on the driver’s implementation.

Solution 3: Custom Data Reader Wrapper

Create a wrapper class that delegates to SQLiteDataReader while performing automatic downcasting:

public class SafeInt32DataReader : IDataReader {  
  private readonly SQLiteDataReader _reader;  
  public SafeInt32DataReader(SQLiteDataReader reader) { _reader = reader; }  

  public int GetInt32(int i) {  
    long value = _reader.GetInt64(i);  
    return checked((int)value);  
  }  
  // Implement other IDataReader methods...  
}  

Advantages:

• Centralizes casting logic.
• Avoids modifying existing code.

Solution 4: Schema-Based Type Mapping

For columns known to contain Int32-safe values, use GetInt32() directly:

int value = reader.GetInt32(0);  

Caution: This throws an InvalidCastException if the actual value exceeds Int32 range. Ensure database constraints or application logic prevents out-of-range values.

Solution 5: COM Interop Marshaling

Apply marshaling attributes in COM-visible interfaces to force 32-bit integers:

[ComVisible(true)]  
public interface IDataService {  
  [return: MarshalAs(UnmanagedType.I4)] // Marshals as 32-bit integer  
  int GetValue();  
}  

Advantages:

• No changes required to database code.
• Transparently converts Int64 to Int32 for COM clients.

Drawbacks:

• Silent truncation if values exceed Int32 range.

Solution 6: Modify System.Data.SQLite (Advanced)

Fork the System.Data.SQLite source code to alter type mapping logic:

1. Override SQLiteDataReader.GetValue() to inspect integer magnitudes.
2. Return Int16/Int32 for values within their ranges.
3. Recompile the wrapper.

Sample Modification:

public override object GetValue(int i) {  
  var type = GetFieldType(i);  
  if (type == typeof(long)) {  
    long value = GetInt64(i);  
    if (value >= short.MinValue && value <= short.MaxValue) return (short)value;  
    if (value >= int.MinValue && value <= int.MaxValue) return (int)value;  
    return value;  
  }  
  return base.GetValue(i);  
}  

Risks:

• Breaks compatibility with code expecting Int64.
• Introduces maintenance overhead.

Summary of Recommendations

1. Prefer Explicit Casting for simplicity and safety in COM scenarios.
2. Use GetInt32() when confident in value ranges.
3. Leverage COM Marshaling to avoid pervasive code changes.
4. Avoid Schema Illusions: Declaring INT32 in SQLite does not enforce storage size or .NET type.

By understanding SQLite’s storage mechanics and .NET’s type system requirements, developers can adopt pragmatic strategies to bridge the Int64-Int32 gap while maintaining robustness.