C# How do I prevent data loss from crashes during a long running query?

Question

I have the following code that takes about an hour to run through a few hundred thousand rows:

public void Recording(int rowindex)
    {
        using (OleDbCommand cmd = new OleDbCommand())
        {
            try
            {
                using (OleDbConnection connection = new OleDbConnection(Con))
                {
                    cmd.Connection = connection;
                    connection.Open();
                    using (OleDbTransaction Scope = connection.BeginTransaction(SD.IsolationLevel.ReadCommitted))
                    {
                        try
                        {
                            string Query = @"UPDATE [" + SetupAction.currentTable + "] set Description=@Description, Description_Department=@Description_Department, Accounts=@Accounts where ID=@ID";
                            cmd.Parameters.AddWithValue("@Description", VirtualTable.Rows[rowindex][4].ToString());
                            cmd.Parameters.AddWithValue("@Description_Department", VirtualTable.Rows[rowindex][18].ToString());
                            cmd.Parameters.AddWithValue("@Accounts", VirtualTable.Rows[rowindex][22].ToString());
                            cmd.Parameters.AddWithValue("@ID", VirtualTable.Rows[rowindex][0].ToString());
                            cmd.CommandText = Query;
                            cmd.Transaction = Scope;
                            cmd.ExecuteNonQuery();
                            Scope.Commit();
                        }
                        catch (OleDbException odex)
                        {
                            MessageBox.Show(odex.Message);
                            Scope.Rollback();
                        }
                    }
                }
            }
            catch (OleDbException ex)
            {
                MessageBox.Show("SQL: " + ex);
            }
        }
    }

It works as I expect it to, however today my program crashed while running the query (in a for loop where rowindex is the index of a datatable), the computer crashed, and when I rebooted the program, it said:

Multi-step OleDB operation generated errors: followed by my connection string.

What happened is that database is entirely uninteractable, even microsoft access's recovery methods can't seem to help out here.

I've read that this may be caused when the data structure of the database is altered from what it expected it to be. My question is, how do I prevent this, since I can't really detect whether my program stopped functioning all of a sudden.

There could be a way for me to restructure it somehow, maybe there's a function I don't know about. Perhaps it is sending something of an empty query when the crash happens, but I don't know how to stop it.

Answer 1

The Jet/ACE database engine already attempts to avoid corruption and to automatically recover from catastrophic events (lost connections, computer crashing). Transactions can further protected against inconsistent data by committing (or discarding) multiple operations altogether. But eventually there may be some coincidental system failure which could terminate an operation at some critical write position, thereby creating critical inconsistencies in the database file. Making regular and timely backups is part of an overall solution. For very long operations it might be worth making an automated copy of the entire database file prior to the operation.

Otherwise, an extreme alternative is to

1. Create a second intermediate database into which all data is first inserted. (Only needs to be done once.)
2. In this intermediate database, create linked tables to relevant tables in the permanent, working database.
3. Also in the intermediate database, create an indexed local table that mirrors the linked table structure into which data will be inserted. OR if the intermediate database and table already exist, clear the local table (i.e. delete all rows).
4. Have your current software insert into the local intermediate table.
5. Run a single query which then updates the linked table from the temporary table. Wrap that update in a transaction.
   • Here's where the linked table has the benefit that it can be referenced in an SQL query just like any local table. You only have to explicitly open the intermediate data. In other words, just perform a simple query like UPDATE LocalTable INNER JOIN LinkedTable ON LocalTable.UpdateID = LinkedTable.ID SET LinkedTable.Data = LocalTable.Data

The benefit to this process is that the single query that updates one Access table from another can be very fast, possibly much faster than the multiple update operations in your code. This could reduce the likelihood that errors in your update code will negatively effect your database. This of course doesn't completely eliminate the random computer crash that can effect the database, but reducing the time that multiple connections and update queries are executed might make it less likely.

Answer 2

I think your catch block is wrong, because if you get an exception other than OleDbException, you will not roll back the transaction

try
{
    // ...
    Scope.Commit();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
    Scope.Rollback();
}

That is, Exception instead of OleDbException. Exceptions could come from anywhere and not necessarily Ole DB, and you still want to roll back everything you've done so far in that case.

That being said, if you have a few hundred thousand rows, I would seriously consider batching the update, and processing just a few thousand per iteration with a transaction per iteration

In terms of transactional behavior, the main question would be: Do you really want to roll back everything you have updated so far in case of failure, or just retry/continue where you left off? If answer is that you want to retry/continue then you will likely want to create a BatchUpdateTask table or similar... with all the information you need for each iteration