How to reuse sqldatareader?

[Cirene]

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]

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

 

[Cirene]

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
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

 

[Cirene]

Thanks. I have to look into generics and what that is all about. I have NO
clue.