PC Review


Reply
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average.

DataReader Limits data returned!

 
 
TY
Guest
Posts: n/a
 
      26th Jan 2006
I have a stored procedure that returns xml data (using For XML), it returns
about 9000 records. I use a datareader to write the data to a XML file.
For some reason, the datareader doesn't write all data, it seems like the
data returned has some size limit.

here is my code, my code is based on a msdn example for using getBytes were
it writes data to the stream by chunks based on the buffer size:

'----some code here to fill in the stored procedure name and
parameters.
Dim mydatareader As SqlDataReader
connectionObj.Open()
mydatareader = CommandObj.ExecuteReader

Dim fs As FileStream
Dim bw As BinaryWriter
Dim bufferSize As Integer = 100
Dim outChar(bufferSize - 1) As Char
Dim retval As Long
Dim startindex As Long = 0

If mydatareader.Read Then

fs = New FileStream("c:\test.xml", FileMode.OpenOrCreate,
FileAccess.Write)
bw = New BinaryWriter(fs)
retval = mydatareader.GetChars(0, startindex, outChar, 0, 100)

' Continue reading and writing while there are bytes beyond the
size of the buffer.
Do While retval = bufferSize
'bw.Write(outByte)
bw.Write(outChar)
bw.Flush()

' Reposition the start index to the end of the last buffer
and fill the buffer.
startindex += bufferSize
retval = mydatareader.GetChars(0, startindex, outChar, 0,
bufferSize)
Loop

'Write the remaining buffer.
bw.Write(outChar, 0, CType(retval - 1, Integer))
bw.Flush()

' Close the output file.
bw.Close()
fs.Close()
End If
connectionObj.Close()
connectionObj.Dispose()
CommandObj.Dispose()
 
Reply With Quote
 
 
 
 
W.G. Ryan - MVP
Guest
Posts: n/a
 
      26th Jan 2006

"TY" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have a stored procedure that returns xml data (using For XML), it returns
> about 9000 records. I use a datareader to write the data to a XML file.
> For some reason, the datareader doesn't write all data, it seems like the
> data returned has some size limit.
>
> here is my code, my code is based on a msdn example for using getBytes
> were
> it writes data to the stream by chunks based on the buffer size:
>
> '----some code here to fill in the stored procedure name and
> parameters.
> Dim mydatareader As SqlDataReader
> connectionObj.Open()
> mydatareader = CommandObj.ExecuteReader
>
> Dim fs As FileStream
> Dim bw As BinaryWriter
> Dim bufferSize As Integer = 100
> Dim outChar(bufferSize - 1) As Char
> Dim retval As Long
> Dim startindex As Long = 0
>
> If mydatareader.Read Then
>
> fs = New FileStream("c:\test.xml", FileMode.OpenOrCreate,
> FileAccess.Write)
> bw = New BinaryWriter(fs)
> retval = mydatareader.GetChars(0, startindex, outChar, 0, 100)
>
> ' Continue reading and writing while there are bytes beyond the
> size of the buffer.
> Do While retval = bufferSize
> 'bw.Write(outByte)
> bw.Write(outChar)
> bw.Flush()
>
> ' Reposition the start index to the end of the last buffer
> and fill the buffer.
> startindex += bufferSize
> retval = mydatareader.GetChars(0, startindex, outChar, 0,
> bufferSize)
> Loop
>
> 'Write the remaining buffer.
> bw.Write(outChar, 0, CType(retval - 1, Integer))
> bw.Flush()
>
> ' Close the output file.
> bw.Close()
> fs.Close()
> End If
> connectionObj.Close()
> connectionObj.Dispose()
> CommandObj.Dispose()



 
Reply With Quote
 
 
 
 
W.G. Ryan - MVP
Guest
Posts: n/a
 
      26th Jan 2006

"TY" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have a stored procedure that returns xml data (using For XML), it returns
> about 9000 records. I use a datareader to write the data to a XML file.
> For some reason, the datareader doesn't write all data, it seems like the
> data returned has some size limit.
>
> here is my code, my code is based on a msdn example for using getBytes
> were
> it writes data to the stream by chunks based on the buffer size:
>
> '----some code here to fill in the stored procedure name and
> parameters.
> Dim mydatareader As SqlDataReader
> connectionObj.Open()
> mydatareader = CommandObj.ExecuteReader
>
> Dim fs As FileStream
> Dim bw As BinaryWriter
> Dim bufferSize As Integer = 100
> Dim outChar(bufferSize - 1) As Char
> Dim retval As Long
> Dim startindex As Long = 0
>
> If mydatareader.Read Then
>
> fs = New FileStream("c:\test.xml", FileMode.OpenOrCreate,
> FileAccess.Write)
> bw = New BinaryWriter(fs)
> retval = mydatareader.GetChars(0, startindex, outChar, 0, 100)
>
> ' Continue reading and writing while there are bytes beyond the
> size of the buffer.
> Do While retval = bufferSize
> 'bw.Write(outByte)
> bw.Write(outChar)
> bw.Flush()
>
> ' Reposition the start index to the end of the last buffer
> and fill the buffer.
> startindex += bufferSize
> retval = mydatareader.GetChars(0, startindex, outChar, 0,
> bufferSize)
> Loop
>
> 'Write the remaining buffer.
> bw.Write(outChar, 0, CType(retval - 1, Integer))
> bw.Flush()
>
> ' Close the output file.
> bw.Close()
> fs.Close()
> End If
> connectionObj.Close()
> connectionObj.Dispose()
> CommandObj.Dispose()

--Ty, is the problem that the records aren't all getting written or that no
one record is getting written completely?


 
Reply With Quote
 
New Member
Join Date: May 2012
Posts: 1
 
      16th May 2012
I know this is an old thread but I'm having the same problem. I am using OracleDataReader to read a table containing upwards of 20,000 records. For me the limit happens to be 9002; any attempt to obtain a field value from any record 9003 and beyond results in a severe crash of the application -- not even a stack trace to help debugging, and try/catch blocks do not work. Application memory is not the problem, as I can populate the arrays with dummy data for elements beyond 9002.

I'll keep looking for an answer. In the meantime my strategy will be to use DataSet objects, rather than DataReader. If I find a useful answer to the DataReader issue I'll post it here.
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to process datareader nulls coming from datareader? JB Microsoft C# .NET 4 3rd Nov 2008 01:56 AM
Re: Retrieving DataReader data William \(Bill\) Vaughn Microsoft ADO .NET 0 27th Jul 2004 06:57 PM
Posting Data - Limits to amount of data posted Jim Douglas Microsoft Dot NET 0 26th Mar 2004 01:09 PM
Binding data from DataReader to DataGrid Makarand Microsoft ADO .NET 2 28th Oct 2003 05:06 PM
Large Data Sets using DataReader Charlie Microsoft ADO .NET 1 19th Sep 2003 01:20 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 09:25 AM.