Preventing Automatic Rounding in System.Data.SQLite Queries Due to Data Type Inference

Data Type Inference in System.Data.SQLite Leading to Unintended Rounding

Issue Overview: Mismatched Data Representation Between SQLite Storage and .NET DataTable Loading

The core problem revolves around unexpected rounding of decimal values when querying a SQLite database using the System.Data.SQLite library in .NET. The user observed that when the first row of a query result contains an integer value (e.g., 7), subsequent rows with decimal values (e.g., 4.10, 5.20, 6.90) are rounded to integers (4, 5, 7). Conversely, if the first row contains a decimal value (e.g., 7.49), subsequent rows retain their decimal precision. This behavior was traced to the interaction between SQLite’s flexible type system and the .NET DataTable.Load() method’s schema inference logic.

Key observations:

1. SQLite’s Storage Model: SQLite does not enforce column types rigidly. Values are stored as INTEGER, REAL, TEXT, or BLOB. The [decimal](18, 2) declaration in the table schema is a type affinity hint, not a strict constraint. SQLite will store numbers as INTEGER if they have no fractional part or REAL otherwise.
2. .NET DataTable’s Schema Inference: The DataTable.Load(IDataReader) method infers column data types based on the first row’s values. If the first value is an integer, the DataTable configures the column as System.Int32, forcing subsequent rows to cast values to integers, truncating decimals. If the first value is a decimal, the column becomes System.Decimal or System.Double, preserving fractional parts.

Example of problematic code:

SQLiteDataReader reader = cmdread.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader); // Schema inferred from first row

In this scenario, the Price column’s data type in the DataTable is determined by the first row’s Price value. If the first Price is 7 (integer), all subsequent Price values are coerced to integers, discarding decimal places. This is not a SQLite engine issue but a consequence of how the .NET DataTable processes results.

Possible Causes: Misalignment Between SQLite Type Handling and .NET Data Loading

1. SQLite’s Type Affinity vs. .NET’s Strong Typing:

   • SQLite does not enforce the [decimal](18, 2) declaration. A value like 4.10 is stored as REAL (floating-point), but 7 is stored as INTEGER.
   • When retrieving data via System.Data.SQLite, the library maps SQLite’s INTEGER to .NET’s long or int, and REAL to double. The DataTable then infers the column type based on the first row’s .NET type.

2. DataTable.Load()’s Schema Inference:

   • The Load() method uses IDataReader.GetFieldType(ordinal) for the first row to determine the column’s data type. If the first Price is 7 (stored as INTEGER in SQLite), GetFieldType returns System.Int32, and all subsequent values are forced to this type, truncating decimals.

3. Misuse of DataTable for Mixed-Type Columns:

   • The DataTable is not designed to handle columns with varying data types (e.g., integers and decimals). This is a limitation of DataTable’s schema inference, not SQLite.

4. Legacy Code Assumptions:

   • Older versions of System.Data.SQLite or .NET might have handled type conversions differently, leading to unexpected behavior after upgrades.

Troubleshooting Steps, Solutions & Fixes: Ensuring Consistent Decimal Handling

Step 1: Validate the Actual Data Stored in SQLite
Use the SQLite CLI to inspect the stored data directly, bypassing .NET:

sqlite3 automaticroundingissue.sqlite
> SELECT ItemId, Quantity, Price FROM SaleItems WHERE SaleId = 1;

If the CLI shows 4.10, 5.20, etc., the rounding is occurring in .NET, not SQLite.

Step 2: Bypass DataTable’s Schema Inference
Replace DataTable.Load() with explicit type handling using SQLiteDataReader:

SQLiteDataReader reader = cmdread.ExecuteReader();
while (reader.Read())
{
  // Use GetDecimal or GetFloat to preserve precision
  Console.WriteLine(reader.GetDecimal(reader.GetOrdinal("Price")));
}

• GetDecimal: Retrieves the value as System.Decimal, preserving exact decimal precision (ideal for financial data).
• GetFloat/GetDouble: Retrieves the value as floating-point (less precise but faster for non-financial data).

Step 3: Adjust Column Type Affinity in SQLite
Modify the Price column’s affinity to REAL to discourage integer storage:

CREATE TABLE [SaleItems](
  ...
  [Price] REAL NULL,  -- Instead of [decimal](18, 2)
  ...
);

This ensures that even values like 7 are stored as REAL (7.0), forcing .NET to treat them as decimals.

Step 4: Standardize on a Decimal-Preserving Data Type
For financial data, avoid floating-point types. Instead:

1. Store prices as integers representing cents (e.g., 7.90 → 790).
2. Format values during retrieval:

   SELECT ItemId, Quantity, (Price / 100.0) AS Price FROM SaleItems;
   

3. Use GetInt32 in .NET and divide by 100.0 for display.

Step 5: Configure DataTable to Allow Decimal Types
Force the DataTable to use System.Decimal for the Price column:

DataTable dt = new DataTable();
dt.Columns.Add("Price", typeof(decimal)); // Explicitly set type
while (reader.Read())
{
  DataRow row = dt.NewRow();
  row["Price"] = reader.GetDecimal(reader.GetOrdinal("Price"));
  dt.Rows.Add(row);
}

Step 6: Update System.Data.SQLite and .NET Framework
Ensure you’re using the latest versions of both libraries to mitigate historical bugs:

• System.Data.SQLite: Upgrade to >= 1.0.117.
• .NET Framework: Use 4.8 or migrate to .NET Core/5+ for improved SQLite support.

Step 7: Use Parameterized Queries for Insertion
Ensure decimal values are inserted as REAL by explicitly parameterizing them:

using (SQLiteCommand cmd = new SQLiteCommand(
  "INSERT INTO SaleItems (SaleId, ItemId, Price) VALUES (@saleId, @itemId, @price)", conn))
{
  cmd.Parameters.AddWithValue("@price", 4.10m); // Decimal type
  cmd.ExecuteNonQuery();
}

Step 8: Review SQLite’s Type Affinity Documentation
Understand how SQLite handles type conversions:

• SQLite Datatype Documentation
• Declaring [decimal](18, 2) has no effect; SQLite treats it as NUMERIC affinity, which is resolved to INTEGER or REAL based on the value.

Final Recommendation:
For financial applications, avoid floating-point types entirely. Store monetary values as integers (cents/pennies) and convert them to decimals during retrieval. This avoids rounding errors and ensures consistency across all rows, regardless of the first row’s value. Example:

// Insertion
cmd.Parameters.AddWithValue("@price", (int)(7.90m * 100)); // 790

// Retrieval
decimal price = reader.GetInt32(reader.GetOrdinal("Price")) / 100.0m;