Handling ADO.NET SQLite INSERT/UPDATE RETURNING RecordsAffected and Column Name Quotation Issues

Understanding RecordsAffected Mismatch and Quoted Column Names in RETURNING Clauses

Issue Overview: INSERT/UPDATE RETURNING Clauses in ADO.NET Yield RecordsAffected = -1 and Quoted Column Names

When using INSERT/UPDATE statements with RETURNING clauses in SQLite via the System.Data.SQLite ADO.NET provider, two distinct anomalies occur:

1. The SQLiteDataReader.RecordsAffected property returns -1 even when rows are successfully inserted or updated.
2. Column names retrieved via SQLiteDataReader contain quotation marks (e.g., "id" instead of id), unlike equivalent SELECT statements.

These issues arise specifically when using the RETURNING clause, which returns a result set containing the modified rows. The RecordsAffected property does not reflect the actual number of modified rows, and the column metadata includes unnecessary quotation marks. This behavior creates challenges in accurately determining the success of data manipulation operations and in programmatically accessing column values by name.

The root of these problems lies in how the System.Data.SQLite provider interprets the RETURNING clause and propagates metadata to the ADO.NET layer. While SQLite itself handles RETURNING correctly (as demonstrated in the SQLite shell), the ADO.NET provider’s implementation introduces inconsistencies in tracking affected rows and processing column names.

Possible Causes: ADO.NET Provider Misalignment with SQLite RETURNING Semantics

1. RecordsAffected Property Misinterpretation

In ADO.NET, the RecordsAffected property is designed to report the number of rows modified by INSERT, UPDATE, or DELETE commands. However, when a RETURNING clause is appended, the command transitions from a non-query (which typically modifies data and reports affected rows) to a query (which returns a result set). The System.Data.SQLite provider may treat such commands as hybrid operations, prioritizing the result set over the RecordsAffected value.

The provider’s internal logic likely fails to update RecordsAffected when a RETURNING clause is present because:

• The command is classified as a SELECT-like operation internally, causing RecordsAffected to default to -1.
• The provider does not account for the fact that RETURNING clauses are part of data modification statements, not pure queries.

2. Column Name Quotation Marks in Metadata

Column names returned by RETURNING clauses inherit the exact identifiers specified in the SQL statement. If the identifiers are quoted (e.g., "id"), the ADO.NET provider includes the quotation marks in the column name metadata. This differs from SELECT statements, where the provider automatically strips quotes from column aliases.

The discrepancy stems from how the provider parses the column names in RETURNING clauses:

• The SQLite engine returns column names as defined in the RETURNING list, preserving quotes if present.
• The ADO.NET provider does not sanitize these names, unlike in SELECT scenarios where it applies normalization.

This behavior suggests that the provider treats RETURNING column names as raw identifiers, whereas SELECT column names undergo additional processing to resolve aliases or remove unnecessary syntax.

Troubleshooting Steps, Solutions & Fixes: Workarounds and Provider-Specific Adjustments

1. Resolving RecordsAffected = -1

Since the RecordsAffected property is unreliable for RETURNING-enabled commands, use alternative methods to determine the number of affected rows:

Solution 1: Count Rows in the Result Set
Each row in the RETURNING result set corresponds to a modified row. Iterate through the result set and count the rows to infer the affected row count:

int affectedRows = 0;
using (var reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        affectedRows++;
        // Process row data...
    }
}

This approach guarantees accuracy because SQLite’s RETURNING clause returns one row per modified record.

Solution 2: Execute Separate Commands
If the result set is not needed, execute the INSERT/UPDATE without RETURNING and use cmd.ExecuteNonQuery(), which correctly populates RecordsAffected:

cmd.CommandText = @"UPDATE test SET name = 'Sam' WHERE id = 1";
int affectedRows = cmd.ExecuteNonQuery(); // Correctly returns 1 if row exists

Solution 3: Use Changes() Function
Execute SELECT changes() after the data modification to retrieve the number of affected rows:

cmd.CommandText = @"
    UPDATE test SET name = 'Sam' WHERE id = 1;
    SELECT changes() AS affected_rows;
";
int affectedRows = Convert.ToInt32(cmd.ExecuteScalar());

2. Eliminating Quotation Marks in Column Names

To avoid quoted column names, adjust the SQL statement or modify how columns are accessed in code:

Solution 1: Use Unquoted Column Names in RETURNING Clauses
Rewrite the SQL command to omit quotes around column names in the RETURNING list:

cmd.CommandText = @"UPDATE test AS a SET name = 'Sam' WHERE id = 1 RETURNING id, name";

This ensures that the provider returns column names without quotes.

Solution 2: Access Columns by Ordinal Position
Bypass column name lookups by using the column’s index:

long id = reader.GetInt64(0); // id is first column
string name = reader.GetString(1); // name is second column

Solution 3: Programmatically Sanitize Column Names
Trim quotation marks from column names when accessing them:

string sanitizedName = reader.GetName(0).Trim('"'); // Removes leading/trailing "
long id = Convert.ToInt64(reader[sanitizedName]);

3. Upgrading or Modifying the Provider Workflow

If the above solutions are insufficient, consider these systemic adjustments:

Option 1: Check for Provider Updates
Verify if newer versions of System.Data.SQLite.Core address these issues. For example, version 1.0.117 (released after the user’s reported 1.0.114.3) includes fixes for edge cases in command parsing.

Option 2: Switch to Microsoft.Data.Sqlite
Migrate to the Microsoft-maintained Microsoft.Data.Sqlite library, which may handle RETURNING clauses more consistently. Test compatibility with this snippet:

using var connection = new SqliteConnection("Data Source=test.db");
connection.Open();

var cmd = connection.CreateCommand();
cmd.CommandText = @"UPDATE test SET name = 'Sam' WHERE id = 1 RETURNING id, name";
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(reader["id"]); // Column names without quotes
}

Option 3: Custom Wrapper for SQLiteDataReader
Create a decorator class that automatically strips quotes from column names and calculates RecordsAffected based on the result set size:

public class SanitizedReader : IDisposable
{
    private readonly SQLiteDataReader _reader;
    public int RecordsAffected { get; private set; }

    public SanitizedReader(SQLiteDataReader reader)
    {
        _reader = reader;
        RecordsAffected = 0;
    }

    public bool Read()
    {
        if (_reader.Read())
        {
            RecordsAffected++;
            return true;
        }
        return false;
    }

    public object this[string columnName] => _reader[columnName.Trim('"')];

    public void Dispose() => _reader.Dispose();
}

// Usage:
using (var reader = new SanitizedReader(cmd.ExecuteReader()))
{
    while (reader.Read())
    {
        long id = Convert.ToInt64(reader["id"]);
    }
    int actualAffected = reader.RecordsAffected;
}

4. Deep Dive into System.Data.SQLite Internals

For advanced users, decompile or review the provider’s source code to diagnose why RecordsAffected remains -1. The SQLiteDataReader class internally sets RecordsAffected in Step() and Finalize() methods. If the command includes a RETURNING clause, these methods may skip updating RecordsAffected because the statement is categorized as a read operation.

Modifying the provider’s source code (if legally permissible) to explicitly set RecordsAffected for RETURNING commands would resolve the issue permanently. However, this requires significant expertise and is not recommended for most users.

By implementing these solutions, developers can work around the limitations of the System.Data.SQLite provider when using RETURNING clauses. The key takeaways are:

1. Avoid relying on RecordsAffected for commands that include RETURNING; use the result set row count instead.
2. Sanitize column names by omitting quotes in SQL or trimming them in code.
3. Consider alternative libraries or provider versions if the issues persist.

These strategies ensure robust data manipulation workflows while leveraging SQLite’s powerful RETURNING clause in ADO.NET applications.