Insertions during commit are not rolled back after error

Feb 20, 2014 at 9:36 PM
Edited Feb 20, 2014 at 9:38 PM
I've created the following FluentData-based method to insert a list of DataRow's into a SQLite database.
public int InsertNewRows(List<DataRow> rowsList)
{
    try
    {
        VerifyTableName();
        int rowID = -1;

        if (rowsList == null || rowsList.Count < 1) { return -1; }

        // Use a transaction as we're likely to be inserting multiple rows
        // Create a local context as it'll be disposed of due to the transaction
        using (var context = Context().UseTransaction(true))
        {
            string rowNames = GetFieldNamesAsCSV(rowsList[0]);
            string valueParms = GetValueParamsAsCSV(rowsList[0]);

            foreach (DataRow row in rowsList)
            {
                string sql = String.Format("INSERT INTO {0}({1}) VALUES({2});", _tableName,
                    rowNames, valueParms);
                rowID = _context.Sql(sql)
                        .Parameters(GetFieldValuesAsList(row).ToArray())
                        .ExecuteReturnLastId<int>();
            }
            context.Commit();
        }

        SendDbChangedMessage(TableChangeType.Insert);
        return rowID;
    }
    catch { throw; }
}

However, in the event of an error during the insertion loop (such as a DB constraint violation), any changes that were made prior to the error are not rolled back. Watching in the debugger, the code jumps from the errored insertion line straight to the catch and the Commit code is never called (as expected).

According to the FluentData docs, this case should automatically roll back the changes, but that doesn't seem to be the case.

Thoughts?

Jeff
Feb 20, 2014 at 9:42 PM
Guh...

Never mind. While cleaning up the code to paste into the above message, I see that I'm using 2 different contexts (_context and context). Replacing the "_context" reference with "context" seems to have cleared things up. Sorry for the noise.

Jeff
Marked as answer by jgodfrey on 2/20/2014 at 2:42 PM