SqlDataReader does not return all records

Question

I am experiencing a problem with ADO.NET SqlDataReader. When I run underlying stored procedure directly in SSMS - it returns 1.7 million of records. When I run a related VB.NET code that fills an ADO.NET DataTable - I also get 1.7 million of records.

But when I run a loop to fill a Generic list like this

While i_oDataReader.Read
   m_aFullIDList.Add(i_oDataReader.GetInt32(0))
End While

It returns a lot less records and that number can vary. At the end of the loop if I check m_aFullIDList.Count it ca be 100000 or 500000 etc. Any idea why and how to fix it? Thanks!

No, the loops runs thru without any errors, just the count at the end is wrong. It's sounds silly but it's almost as if SQL Server cannot catch up with DataReader and the loop is finished before full result is returned. — Comfortably Numb
– Comfortably Numb, Commented Sep 17, 2012 at 17:12
But actually you reference pointed me to the right direction. Adding "CommandBehavior.SequentialAccess" while creating the DataReader fixed the issue! Thanks :) — Comfortably Numb
– Comfortably Numb, Commented Sep 17, 2012 at 17:21

Comfortably Numb · Accepted Answer · 2012-09-17 17:28:51Z

2

Thanks to reference pointed by @Tim Schemlter I found the option "CommandBehavior.SequentialAccess" for DataReader creation. That fixed the issue. E.g. instead of

drReader = oCommand.ExecuteReader();

use

drReader = oCommand.ExecuteReader(CommandBehavior.SequentialAccess);

and it works correctly.

answered Sep 17, 2012 at 17:28

Comfortably Numb

39.9k17 gold badges82 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Comfortably Numb Over a year ago

No, not a blob, plain Int, but a whole lot of those.

Comfortably Numb Over a year ago

And a funny thing - it takes about 14 seconds to execute query directly in SSMS, but only about a second to execute a loop above.

Brian · Accepted Answer · 2012-09-17 17:22:37Z

0

Have you tried using the GetInt64() method instead? I realize that with only 1.7 million records, the GetInt32() method should be large enough; I was more just curious.

Also, if you think that SQL cannot keep up with the DataReader, have you tried adding a wait in the loop to allow it to catch up?

answered Sep 17, 2012 at 17:22

Brian

5,1397 gold badges41 silver badges52 bronze badges

Collectives™ on Stack Overflow

SqlDataReader does not return all records

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related