If you use a datareader then it theoretically only reads one row at a
time. So the entire 100mb will not be in memory in your .NET
application in the first place.
The data reader may do some prefetching depending on the
implementation, the socket connection may do some buffering, and the
database most likely will do caching, so more than one record will be
in memory, but it's generally safe to think of a datareader as a
one-record-in-memory data access method.
When you close the data reader the one record it cached will be
released and available for collection. The cursor in the db will also
be released so the db can release whatever memory it was using.
Yes you can continue using that connection and not worry about memory
management in .NET. Just make sure you use the using() {} pattern
every time so you are sure to close the readers even on error. Don't
hold open the connection longer than needed though--connection pooling
makes resuing connections less important than would otherwise be the
case.
Note that a DataSet / DataTable would hold the entire result set in
memory and keep it in memory until all references are removed (DataSet
does have Dispose(), but it's only there due to it's object hierarchy
and it's Dispose method actually doesn't do anything).
HTH,
Sam
------------------------------------------------------------
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.
On Thu, 1 Mar 2007 13:14:40 -0500, "Mike" <ga**********@hotmail.com>
wrote:
>Assume i have a db connection and a datareader for that connection. Now I
perform a query that retrieves 100MB of data. I know that the data reader
gets 1 row at a time. However, there is a result set out there that is
100MB is size.
If I close the dataReader but keep the DB connection open, will the 100MB be
freed up after closing the DataReader?
Can I continue reusing the same DB connection and creating and destroying
datareaders assigned to that connect and trust that the memory after the
datareader close will be freed?
Thanks