Can't figure out exception message

Question

I am having the following error message and I am using petaPOCO. Why am I having this error message and What am I doing wrong to have this message:

{"There is already an open DataReader associated with this Command which must be closed first."}

This is what I have been able to copy for the exception message.

System.InvalidOperationException was caught Message=There is already an open DataReader associated with this Command which must be closed first. Source=System.Data StackTrace: at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command) at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command) at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteScalar() at PetaPoco.Database.Insert(String tableName, String primaryKeyName, Boolean autoIncrement, Object poco) in C:\Dev\Code\API\Models\PetaPoco.cs:line 1243 InnerException:

show your code where the DataReader is being used – Andy Mar 01 '12 at 07:01 — Andy, Mar 01 '12 at 07:01
Are you using the db.Query method? – patmortech Mar 01 '12 at 11:12 — patmortech, Mar 01 '12 at 11:12
please add some data access logic code. – Shoaib Shaikh Mar 06 '12 at 08:36 — Shoaib Shaikh, Mar 06 '12 at 08:36

score 10 · Answer 1 · answered Mar 09 '12 at 16:25

I know this is old, but I wanted to add something that may help the next person searching for this. This error happens if you use the Query method. The Query method doesn't load everything in memory. If you need to have it loaded and then have the connection closed, you need to use Fetch.

This is from the website:

Query vs Fetch

The Database class has two methods for retrieving records Query and Fetch. These are pretty much identical except Fetch returns a List<> of POCO's whereas Query uses yield return to iterate over the results without loading the whole set into memory.

Hope this helps someone else.

Thanks, for the hint. The answer is partially correct with new PetaPOCO. I think now Fetch also calls Query. The better way to fix it would be to call `ToList()` or similar method that will fetch the complete dataset and close the reader. — Chandermani, Mar 26 '14 at 09:04

score 4 · Accepted Answer · answered Mar 06 '12 at 08:34

Here is an excellent explanation why this exception is raised:

http://blogs.msdn.com/b/spike/archive/2009/08/20/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-closed-first-explained.aspx

The conclusion is as following:

Because the SqlDataReader keeps the memory stream (resultset) available until you explicitly close the SqlDataReader you can get this exception if you try to create a new reader without closing the previous one.

Alter your code to have a using statement whenever you create a SqlDataReader:

SqlCommand cmd = new SqlCommand(sql, con);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
  while (rdr.Read())
  {
    Console.WriteLine("cid: {0}, ctext: {1}", rdr[0].ToString(), rdr[1].ToString());
  }
}

Using will automatically call dispose() (which closes the reader) when the closure (ending } ) is reached.

If this exception is raised in petaPOCO then there is a bug in their code or you are using the code in an unspecified way.

score 4 · Answer 3 · answered Mar 06 '12 at 08:40

Your ORM (or your usage pattern of the ORM) expects the underlying ADO.NET provider to allow for multiple open DataReaders on a single Connection. SQL Server Provider you seem to use can do that, but you must add MultipleActiveResultSets=True to the connection string you use to connect to the database.

score 0 · Answer 4 · answered Apr 16 '12 at 18:10

0

This exception can also be thrown in PetaPoco if your poco doesn't have a parameterless constructor of either public or protected visibility.

answered Apr 16 '12 at 18:10

David Montgomery

1,618
15
28

Can't figure out exception message

4 Answers4