Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsFree MagazinesWhite PapersSubmit Content
Discussion GroupsASP.NETWindows FormsLanguages.NET FrameworkVisual Studio.NET
Articles.NET FrameworkASP.NETToolsWindows Forms
.NET DirectoryOpen Source ProjectsUser GroupsWeb Resources
Related Topics
Visual Basic 6SQL ServerMS AccessOther DB ProductsMS Server ProductsMore Topics ...

.NET Forum / ASP.NET / General / May 2008

Tip: Looking for answers? Try searching our database.

How to reuse sqldatareader?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Cirene - 27 May 2008 01:15 GMT
I have a sqldatareader that I use to read some data.  Later I do a dr.close.

In the same sub I later to "dr = MyCommand.ExecuteReader" because I'm trying
to reuse the var with a totally different stored proc.

When the code runs I get this error on the 2nd dr.Read:
System.InvalidOperationException: Invalid attempt to call Read when reader
is closed. at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean
setTimeout) at System.Data.SqlClient.SqlDataReader.Read() at
customer_usNewAppt.Page_Load(Object sender, EventArgs e) in
F:\work-related\websites\MySite\customer\ucNewAppt.ascx.vb:line 92

Any ideas why?  Thanks!
Mark Fitzpatrick - 27 May 2008 01:47 GMT
After you do your dr.Close() try also calling Dispose and setting it to
null. That should help ensure that the object is definitely not using the
previous one.

In this case though, I don't think you'll gain anything by re-using the same
variable. You'll still end up using separate data connections so you
shouldn't really get any benefit.

If these items are at all similar and you really wanted to re-use resources,
you could see if you can combine them into one stored procedure that can
call the other two stored procedures. This lets you get the first data, then
use the NextResultset method to attempt to load the second set of data. This
re-uses the connection and reduces the roundtrip. The NextResultset will
return a true if there is a next resultset and a false otherwise so it lets
you test to ensure that the data was really returned before you attempt to
read it. This may not be at all what you are trying to do, but since you
were eager to re-use resources I thought I'd mention it. It's a great little
trick especially when attempting to load similar data, such as when you're
trying to populate lists of data for a particular form (such as states,
countries, etc.).

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - Expression

> I have a sqldatareader that I use to read some data.  Later I do a
> dr.close.
[quoted text clipped - 10 lines]
>
> Any ideas why?  Thanks!
Cirene - 27 May 2008 01:58 GMT
Great ideas.  Thanks.

> After you do your dr.Close() try also calling Dispose and setting it to
> null. That should help ensure that the object is definitely not using the
[quoted text clipped - 36 lines]
>>
>> Any ideas why?  Thanks!
Mark Rae [MVP] - 27 May 2008 01:48 GMT
> I have a sqldatareader that I use to read some data.  Later I do a
> dr.close.
[quoted text clipped - 10 lines]
>
> Any ideas why?  Thanks!

A DataReader is a forward-only read-only set of data. You have only one
opportunity to make use of it. Once you've read to the end, that's it - you
can't go back... This has the effect of making the DataReader object very
light and, generally, speaking, it will out-perform other types of data
sets...

Therefore, if you need anything other than a one-time-only read of the data,
then a DataReader is the wrong ADO.NET object.

Try a DataSet instead...

Alternatively, if the amount of data in the DataReader is (relatively)
small, you could always read it into another object, maybe a generic...

Signature

Mark Rae
ASP.NET MVP
http://www.markrae.net

Cirene - 27 May 2008 01:59 GMT
Thanks.  I have to look into generics and what that is all about.  I have NO
clue.  :)

>> I have a sqldatareader that I use to read some data.  Later I do a
>> dr.close.
[quoted text clipped - 25 lines]
> Alternatively, if the amount of data in the DataReader is (relatively)
> small, you could always read it into another object, maybe a generic...

Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.