SqlDataReader does not read all records

Question

I have a very weird problem in the most simple piece of VB.NET code:

Public Sub ReadIDs(i_oDataReader As Data.SqlClient.SqlDataReader)

    m_aFullIDList = New Generic.List(Of Integer)

    While i_oDataReader.Read
        If i_oDataReader.IsDBNull(0) = False Then
            m_aFullIDList.Add(i_oDataReader.GetInt32(0))
        End If
    End While

    m_iTotalNumberOfRecords = m_aFullIDList.Count

End Sub

Basically the procedure accepts a DataReader as a parameter, loops thru all the records in the reader, adding them to a Generic list of integer.

When SQL Stored Procedure that is behind the reader returns relatively small amount of records (below 100K) reader reads all the records and the final count matches the resultset. But when the number of records is over 1 million - results become unpredictable.

E.g. When I know that SP rerturns 1.5M of records - final count of records in Generic list could be 200K, 500K etc.

Any idea what is causing it and how to fix it? Thanks!

What kind of application, ASP.NET or other multi-threading type? — Tim Schmelter
– Tim Schmelter, Commented Nov 3, 2012 at 22:12
Thanks for the prompt response. ASP.NET as a matter of fact. — Comfortably Numb
– Comfortably Numb, Commented Nov 3, 2012 at 22:14
Is it reproducable on debugging or only in production? Is anything static(shared) like the connection? Do you get an exception? — Tim Schmelter
– Tim Schmelter, Commented Nov 3, 2012 at 22:16
First thing to do is to run the code in isolation - perhaps cut a paste to a test program. Check if it succeeds. If it does then the error is likely be some interaction with another part of the program. If it's the same then you need to go deeper and isolate the the point (number of rows) at which it causes an error. Then try finding out which part of the infrastructure is broken - by replacing parts eg.g reader with dataset, the db engine? etc etc.. Also put try catches in to see if exceptions are raised to help you. — Preet Sangha
– Preet Sangha, Commented Nov 3, 2012 at 22:17
Behaves the same way in isolated code. I noticed if I use simple i_oDataReader(0) instead of i_oDataReader.GetInt32(0) I get correct count more often, but every now and then reader throws a NullReferenceException for some reason. — Comfortably Numb
– Comfortably Numb, Commented Nov 3, 2012 at 22:59

Tim Schmelter · Accepted Answer · 2012-11-03 22:26:39Z

1

It may help to use the ExecuteReader overload that allows you to specify a CommandBehavior, and pass CommandBehavior.SequentialAccess as recommended in this MSDN topic:

Retrieving Large Data (ADO.NET).

answered Nov 3, 2012 at 22:26

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

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

3 Comments

Comfortably Numb Over a year ago

Already using ExecuteReader(CommandBehavior.CloseConnection + CommandBehavior.SequentialAccess) doesn't seem to help. About breaking into chunks - how would I do this? I do not control the SP

Tim Schmelter Over a year ago

I've only just seen that you're the one: stackoverflow.com/a/12464309/284240 So it dos not work correctly.

Comfortably Numb Over a year ago

Sorry about duplicate, decided to get a fresh view. That was a strange thing - it seemed to work for a while, and then had no more effect. Have no idea why.

Collectives™ on Stack Overflow

SqlDataReader does not read all records

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related