C # Parallel loop error

Question

Can anybody help? When I write this code and run. The program shows me that the error "There is already an open DataReader associated with this Command, which must be closed first."

This is my code.

Parallel.For(0, MthRange, i => { PSUpfrontFeeForMonth[i] = CommissionSummary .Where(s => s.TransDate == oReportCommonFilter.fromDate.AddMonths(i)) .Sum(s => s.PSUpfrontFee); if (!PSUpfrontFeeForMonth[i].HasValue) { PSUpfrontFeeForMonth[i] = 0; } }); 

Thanks.

Regards, Jane

Answer 1

A parallel database query is completely incorrect for the following reasons:

• A query is issued against sql from each processor so much data readers will open → 'error'
• Achieving performance is not achieved, in fact, the program becomes slower because each processor is trying to connect to the database and the lack of parallelism processing is actually performed, since all query processing is performed in sql! So a regular serial query runs faster this case.

Answer 2

If you really need to open multiple database connections at the same time (others say it is not necessarily a good idea), you can usually indicate in the connection line that this is necessary.

A typical scenario in which I used this is to use a DataReader to stream rows from a database table (since I don’t know how many rows I need in the extended one), and where then do I need to make additional queries on other database tables. I am doing this, not just one query, as this will require several complex joins, and my application has a good level of caching to reduce database queries.

For Microsoft SQL Server, you add MultipleActiveResultSets=True; to the connection string

Answer 3

My guess would be related to the way PSUpfrontFeeForMonth works internally, using data readers.

Since I have no idea how this works, the first thing I would try to initialize is PSUpfrontFeeForMonth in a loop. Perhaps this will provide a dedicated data reader for each iteration.