Sql objConnection woes

[Guest]

I have a class method that returns a DataReader

SqlDataReader parentReader = class.GetResults("55");
if(parentReader.Read())
{
..
..
}


public SqlDataReader GetResults(string County)
{
SqlDataReader myreader= My SQL statements
..
..
..
objConnection.Dispose(); //Problem is here
return myReader
}

The prob is if the parant code attempts to read from myReader, it says it
cant b/c the datareader is closed. Yet at this point the fact that the
connection created in the child class was disposed shouldnt affect a
datareader thats already loaded. The connection was local to the child class,
why should the parent datareader care? Whats the problem? If I remove the
dispose method, everything works, but I run the risk of running out of
connections in the SQL pool b/c the code is called many times.

 

[Kevin Spencer]

A DataReader maintains a constant connection to the DataStore, as it only
fetches one row at a time. Therefore, as long as you are using the
DataReader, the Connection must remain opened.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Show me your certification without works,
and I'll show my certification
*by* my works.

 

[Patrice]

See :
http://msdn.microsoft.com/library/d...ystemdatasqlclientsqldatareaderclasstopic.asp

The DataReader is actually connected and needs the connection to be open.

 

[John Bailo]

The prob is if the parant code attempts to read from myReader, it says it
cant b/c the datareader is closed. Yet at this point the fact that the
connection created in the child class was disposed shouldnt affect a
datareader thats already loaded. The connection was local to the child
class, why should the parent datareader care? Whats the problem? If I
remove the dispose method, everything works, but I run the risk of running
out of connections in the SQL pool b/c the code is called many times.

You should serialize the data either to XML ( ExecuteXML ) or store the
contents to a DataSet.