PetaPoco "already an open DataReader" in console app

Question

I'm using PetaPoco for the first time, and getting this error :

An unhandled exception of type 'System.InvalidOperationException' occurred in Anonymiser.exe. Additional information: There is already an open DataReader associated with this Command which must be closed first.

According to the following issues and answers given...

1. There is already an open DataReader associated with this Command
2. How to create a DAL using Petapoco

...suggest this is an issue with multiple requests to the same database/resource, but their solution is to use one db connection per request, only I'm not in a complicated webapp, I'm running a single threaded console app, and I imagine a Web Application would need several queries and updates and stuff all in one request (similar to what I'm doing, select, loop results, update on each row).

I've posted the pertinent code below, the first bit is using generics and reflection to basically call "Query" to get all the data out the table (designed to not know the database structure of the data I wish to randomise), then I change the data in each row and "Update" each row after the random data is inserted, like so...

//select all data in a table
// non-generic version :: db.query<db_table>("select * from db_tableName");                        
var typedMethod = queryMethodInfo.MakeGenericMethod(t);
var allRows = typedMethod.Invoke(db, new Object[] { "select * from " + tableName, null });

//then loop through the data
foreach (var item in (allRows as IEnumerable))
{
    string primaryKey;
    if (findPrimaryKey(item, out primaryKey))  // no primary key no update
    {
        // randomize the data here
        DataRandomizer.ProcessObject(item);

        // push data back to the database
        db.Update(tableName, primaryKey, item);     
    }
}

Should I 'close' the connection after every operation? Should I create a new PetaPoco.Database object for each sql operation? That does not seam to be the solution proposed by the answer, it suggests 1 request means 1 connection, with connections shared, and this is a single threaded console app which isn't sharing anything already.

Update: Just tried this... madness, but it works, someone give me a sane solution please

(new PetaPoco.Database("DBConnectString")).Update(tableName, primaryKey, item);

Answer 1

Swap the usage of Query to Fetch. The reason for this is because Fetch does the work up front and returns a fully populated collection, whereas, Query fetches the results as you enumerate the result set.

The reason for the error is that you're using trying to issue another DB request while fetching results, which as the error states, you cannot do because the connection has an open DataReader. This is why you are required to fetch the results up-front.