PetaPoco Error : “There is already an open Data Reader associated with this Command”

In case you have not heard about PetaPoco, it is an awesome Micro ORM framework for accessing database.

While using PetaPoco with our Windows Service project we starting having issues where PetaPoco would throw the exception “There is already an open DataReader associated with this Command” whenever we called a specific method. Our first thought was this could be due to the Windows service host, as we have been using PetaPoco in our Web applications for a long time without encountering anything like this. So we made sure in our

IoC container we are not creating any type of singleton PetaPoco database object. With some amount of debugging we realized that the error was thrown by the ADO.Net framework and it happens because a DataReader was open on a connection and new request was being made to open another DataReader. This was the culprit code.

[gist]https://gist.github.com/chandermani/11132420[/gist]

As you can see the first query (planEmployees line 1) call from PetaPoco is not realized fully and a foreach loop is used to iterate over the results which keeps the DataReader open. Within this foreach we again make a second request (this.LeavesService.GetCredits(e.ID) line 6) using the same PetaPoco DB object which again tries to create a DataReader to iterate over another result set but fails.

The fix was simple, just retrieving the complete result set using the ToList() method in the first statement before starting the foreach loop. The code now looked like

[gist]https://gist.github.com/chandermani/11132531[/gist]

The abstraction came to bite us, and without understanding of how queries get realized it would have been difficult to understand what is happening and how fix the issue. We looked at the flowing threads to get some idea around this issue

http://stackoverflow.com/questions/9511669/cant-figure-out-exception-message

PetaPoco Issue 103

I hope this post helps others who face with this issue in future.