dataset question - to preserve DB resources

[Chris]

I have a procedure which runs a heavy SQL query, loops through a SQLreader
and closes the reader and connection.

Within the loop, there is a lot of code/processing that must remain. I'd
like to open the DB connection, move the data to a dataSet or something to
get the data in memory, and then immediately close the DB reader/connection
to free up DB resources. Then I can loop through the dataSet and perform
all the necessary work, without holding up the database for other users.

My question, using ASP.NET 1.1, can someone provide an example of this
approach?

Thanks in advance.

 

[Marina]

Don't bother with the SqlDataReader. Get the data straight into the dataset
(or datatable) using the SqlDataAdapter's Fill method, then do your
processing.

 

[Chris]

That helped a lot.. Thanks.
My profiler trace shows 7 second queries were reduced to 1 second. Not bad.
Used a DataTable.