OleDB Queries not Returning All Results

Question

What I am expecting my code to do is gather the top ten records that have an 'I' in the SQLMigrationFl field, and then process those ten records by removing that 'I' value. Below is the portion of code that handles this.

string inProgressQuery = "SELECT TOP 10 IDType, ID, Program, Date, Body, 
                          AcctNo, ANPACAcctID, ANPACClientID, TEAMID, 
                          ImportDate, AnnualReview, TeamGUID, 
                          ANPACClientLastName, ANPACClientFirstName, " +
                          "PolicyNumber, AccountOwnerLastName, 
                           AccountOwnerFirstName, SCRACF, SCDateTime, NoteID 
                           FROM NoteTable WHERE SQLMigrationFl = ?";

command = new OleDbCommand(inProgressQuery, connection);
command.Parameters.AddWithValue("SQLMigrationFl", "I");
reader = command.ExecuteReader();

if(reader.HasRows)
{
    while(reader.Read())
    {
        //clear the In Progress flag
        query = "UPDATE NoteTable SET SQLMigrationFl = ? WHERE 
                 NoteTable.NoteID = " + reader[19].ToString();

        command = new OleDbCommand(query, connection);
        command.Parameters.AddWithValue("SQLMigrationFl", DBNull.Value);
        reader = command.ExecuteReader();
    }
}

What I am finding is that the query returns one value, and processes it. Then in five seconds it finds another record and reprocesses that one. *Five seconds is just a delay we have set in code to check for more records to be processed. It processes one record at a time, rather than grabbing ten and processing those at once within the same while loop. Is something wrong with my code or query?

Thanks for the help.

Answer 1

The culprit is your reassigning of the data reader using reader = command.ExecuteReader();. That will now return 1 result and your next loop will be over that 1 result. Regardless for non SELECT queries use ExecuteNonQuery instead.

Other places that you can update your code to be "better" are

1. Use parameters whenever you have values that you want to use in your sql statements.
2. Always specify the type for all your parameters.
3. Always wrap your types instances that implement IDisposable in using blocks to ensure resources are cleaned up.

I also recommend you not share connection instances, below it seems that there might be a static connection somewhere. It would be best to not share one and create/open one when you need it and then close/dispose it.

string inProgressQuery = "SELECT TOP 10 IDType, ID, Program, Date, Body, 
                      AcctNo, ANPACAcctID, ANPACClientID, TEAMID, 
                      ImportDate, AnnualReview, TeamGUID, 
                      ANPACClientLastName, ANPACClientFirstName, " +
                      "PolicyNumber, AccountOwnerLastName, 
                       AccountOwnerFirstName, SCRACF, SCDateTime, NoteID 
                       FROM NoteTable WHERE SQLMigrationFl = ?";

using(var command = new OleDbCommand(inProgressQuery, connection))
{
    // I guessed on the type and length
    command.Parameters.Add(new OleDbParameter("SQLMigrationFl", OleDbType.VarChar, 10)).Value = "I";
    using(var reader = command.ExecuteReader())
    {
        while(reader.Read())
        {
            //clear the In Progress flag
            const string UpdateQuery = "UPDATE NoteTable SET SQLMigrationFl = ? WHERE NoteTable.NoteID = ?";
            using(var commandUpdate = new OleDbCommand(UpdateQuery, connection))
            {
                commandUpdate.Parameters.Add(new OleDbParameter("SQLMigrationFl", OleDbType.VarChar, 10)).Value = DBNull.Value;
                commandUpdate.Parameters.Add(new OleDbParameter("NoteId", OleDbType.Int)).Value = reader[19];
                commandUpdate.ExecuteNonQuery();
            }
        }
    }
}