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(B oolean
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" <ci****@nowhere.comwrote in message
news:eQ**************@TK2MSFTNGP02.phx.gbl...

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(B oolean
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 Rae [MVP]]

"Cirene" <ci****@nowhere.comwrote in message
news:eQ**************@TK2MSFTNGP02.phx.gbl...

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(B oolean
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!

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...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Cirene]

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

"Mark Rae [MVP]" <ma**@markNOSPAMrae.netwrote in message
news:Oi*************@TK2MSFTNGP06.phx.gbl...

"Cirene" <ci****@nowhere.comwrote in message
news:eQ**************@TK2MSFTNGP02.phx.gbl...

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

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...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Cirene]

Great ideas. Thanks.

"Mark Fitzpatrick" <ma******@fitzme.comwrote in message
news:OW**************@TK2MSFTNGP04.phx.gbl...

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" <ci****@nowhere.comwrote in message
news:eQ**************@TK2MSFTNGP02.phx.gbl...

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