SqlDataReader problem...

  • Thread starter Thread starter Franck Diastein
  • Start date Start date
F

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.Read()){

Code:
*************************************************
public void Export2CSV(){
SqlDataReader _dataR = ExportData();
while(_dataR.Read()){
//using data
}
}
private SqlDataReader ExportData(){
//Data extraction from database
return myDataR;
}
*************************************************
 
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,
 
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.
 
private SqlDataReader ExportData(){
SqlDataReader myDataR = null;
try {
m_DB.DBParamInit();
m_DB.DBParamAdd("@ID", mintID, SqlDbType.BigInt);
myDataR = m_DB.DBExecReaderSP("SP_GetInfo");
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
 
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.ExecuteReader() method (for instance
CommandBehavior.CloseConnection).

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.
 
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
 
I will try that way...

Thank you all :-)
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
Click to expand...
 
Franck Diastein said:
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.Read()){

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

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.
 
Back
Top