SqlDataReader problem...

[Franck Diastein]

Hi, when I call ExportData I have this error:

Invalid attempt to Read when reader is closed.

Telling me that there's a problem with this line:
while(_dataR.Re ad()){

Code:
*************** *************** *************** ****
public void Export2CSV(){
SqlDataReader _dataR = ExportData();
while(_dataR.Re ad()){
//using data
}
}
private SqlDataReader ExportData(){
//Data extraction from database
return myDataR;
}
*************** *************** *************** ****

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

It seems fine to me, as long as you do not close the datareader in
ExportData() it should be fine.
Post the code inside ExportData to see if there is a problem there.

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Franck Diastein" <fd*******@eusk altel.net> wrote in message
news:e0******** ******@TK2MSFTN GP11.phx.gbl...

Hi, when I call ExportData I have this error:

Invalid attempt to Read when reader is closed.

Telling me that there's a problem with this line:
while(_dataR.Re ad()){

Code:
*************** *************** *************** ****
public void Export2CSV(){
SqlDataReader _dataR = ExportData();
while(_dataR.Re ad()){
//using data
}
}
private SqlDataReader ExportData(){
//Data extraction from database
return myDataR;
}
*************** *************** *************** ****

[Anders Borum]

You are probably closing the underlying connection associated with the
SqlDataReader before returning the SqlDataReader from the ExportData method.
Please post a snippet of code with the implementation of that particular
method.

--
venlig hilsen / with regards
anders borum
--

[Franck Diastein]

private SqlDataReader ExportData(){
SqlDataReader myDataR = null;
try {
m_DB.DBParamIni t();
m_DB.DBParamAdd ("@ID", mintID, SqlDbType.BigIn t);
myDataR = m_DB.DBExecRead erSP("SP_GetInf o");
return myDataR;

}
catch( SqlException e ) {
throw new Exception( e.Message );
}
finally {
myDataR.Close() ;
myDataR = null;
}
}

I want to be able to close and destruct myDataR even if an error ocurred...

How can I do it ?

TIA

Franck Diastein wrote:

Hi, when I call ExportData I have this error:

Invalid attempt to Read when reader is closed.

Telling me that there's a problem with this line:
while(_dataR.Re ad()){

Code:
*************** *************** *************** ****
public void Export2CSV(){
SqlDataReader _dataR = ExportData();
while(_dataR.Re ad()){
//using data
}
}
private SqlDataReader ExportData(){
//Data extraction from database
return myDataR;
}
*************** *************** *************** ****

[Marcin Grzębski]

Hi Franck,

As Ignacio and Anders write before, you were closing data
reader before you want to use it.

There's no sense to return closed IDataReader.
If you don't want to keep it open then return
whole records as a DataTable or an ArrayList.

Regards
Marcin

private SqlDataReader ExportData(){
SqlDataReader myDataR = null;
try {
m_DB.DBParamIni t();
m_DB.DBParamAdd ("@ID", mintID, SqlDbType.BigIn t);
myDataR = m_DB.DBExecRead erSP("SP_GetInf o");
return myDataR;

}
catch( SqlException e ) {
throw new Exception( e.Message );
}
finally {
myDataR.Close() ;
myDataR = null;
}
}

I want to be able to close and destruct myDataR even if an error ocurred...

How can I do it ?

TIA

Franck Diastein wrote:

Hi, when I call ExportData I have this error:

Invalid attempt to Read when reader is closed.

Telling me that there's a problem with this line:
while(_dataR.Re ad()){

Code:
*************** *************** *************** ****
public void Export2CSV(){
SqlDataReader _dataR = ExportData();
while(_dataR.Re ad()){
//using data
}
}
private SqlDataReader ExportData(){
//Data extraction from database
return myDataR;
}
*************** *************** *************** ****

[Anders Borum]

Hello!

The SqlDataReader is a low level provider that works by retrieving one row
at a time on a active connection to the database. This allows the
SqlDataReader to work with really large resultsets, because it doesn't have
to move all the data from the datastore to the client before processing can
start (as is the case with the DataSet, which in fact is populated with the
DataAdapter that uses a DataReader internally).

Closing the SqlDataReader is therefore not an option, if you need to pass it
from a method. I see your frustration in the lack of control of how and when
the resources associated with the SqlDataReader are disposed.

When creating the SqlDatader, pass an appropriate CommandBehavior
enumeration value to the sqlCommand.Exec uteReader() method (for instance
CommandBehavior .CloseConnectio n).

From the MSDN:
When the command is executed, the associated Connection object is closed
when the associated DataReader object is closed.

This means that once the consumer of the SqlDataReader reached the last row
and closes the SqlDataReader, the underlying connection is closed as well
and returned to the pool.

Exposing a SqlDataReader allows for low level access to the database but
comes with a price. Using the enumeration as described above can help with
the resource management. I also advice you to wrap the SqlDataReader in a C#
"using" statement, by which the disposing of the SqlDataReader is correctly
handled.

--
venlig hilsen / with regards
anders borum
--

[Franck Diastein]

I will try that way...

Thank you all :-)

Marcin Grzębski wrote:

Hi Franck,

As Ignacio and Anders write before, you were closing data
reader before you want to use it.

There's no sense to return closed IDataReader.
If you don't want to keep it open then return
whole records as a DataTable or an ArrayList.

Regards
Marcin

private SqlDataReader ExportData(){
SqlDataReader myDataR = null;
try {
m_DB.DBParamIni t();
m_DB.DBParamAdd ("@ID", mintID, SqlDbType.BigIn t);
myDataR = m_DB.DBExecRead erSP("SP_GetInf o");
return myDataR;

}
catch( SqlException e ) {
throw new Exception( e.Message );
}
finally {
myDataR.Close() ;
myDataR = null;
}
}

I want to be able to close and destruct myDataR even if an error
ocurred...

How can I do it ?

TIA

Franck Diastein wrote:

Hi, when I call ExportData I have this error:

Invalid attempt to Read when reader is closed.

Telling me that there's a problem with this line:
while(_dataR.Re ad()){

Code:
*************** *************** *************** ****
public void Export2CSV(){
SqlDataReader _dataR = ExportData();
while(_dataR.Re ad()){
//using data
}
}
private SqlDataReader ExportData(){
//Data extraction from database
return myDataR;
}
*************** *************** *************** ****

[Jon Skeet [C# MVP]]

Franck Diastein <fd*******@eusk altel.net> wrote:

Hi, when I call ExportData I have this error:

Invalid attempt to Read when reader is closed.

Telling me that there's a problem with this line:
while(_dataR.Re ad()){

Code:
*************** *************** *************** ****
public void Export2CSV(){
SqlDataReader _dataR = ExportData();
while(_dataR.Re ad()){
//using data
}
}
private SqlDataReader ExportData(){
//Data extraction from database
return myDataR;
}
*************** *************** *************** ****

We'll need to see more code than that.

Could you post a short but complete program which demonstrates the
problem?

See http://www.pobox.com/~skeet/csharp/complete.html for details of
what I mean by that.

--
Jon Skeet - <sk***@pobox.co m>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too