Redundant ASP.NET SQL command executions.

[Guest]

Hi,
In order to have my application give the best performance, I'm trying to
design my ASP.NET page where I will only have to populate my DataReader once.
It is a simple application where we accept date range from the user, and any
records in our SQL table, called SHIPMENTS, which has a value in a date
field, called EXPORT_DATE, which falls in that date range will be deleted
from the table. But the user would like to see a preview of the records
which will be deleted before he confirms that they should be deleted. So,
right now, the series of events are:

1. User enters date range and clicks OK.
2. Use DataReader and Command to generate the data which is to be displayed
for the user.
3. User clicks 'confirm' for the records to be deleted.
4. Do Step #2 again to get the data, and then iterate through the DataReader
and execute a stored procedure on each record's key to do the delete and
perform other miscellaneous operations.

Is it really necessary to execute the Command object twice, or can't the
DataReader be stored in memory and be ready and waiting for when we'll need
to access it the second time?

Thanks,
John

 

[Hermit Dave]

the preferred approach is to open the connection, execute whatever needs to
be executed and closing the connection.
ie keeping the connection open for the least possible time. The connection
is returned to pool on .close().

The problem with datareader is that it keeps the connection open unless
explicity closed.
if you need to preview the data then you should use a disconnected object ie
get a dataset.
if you want to save the number of queries you do to the database you could
write some funky sql code
say pass the delete stored proc a comma seperate string of all the items
that need to be deleted.

that way you have the connection open for least possible time.

keeping the reader in memory is not advisable and you could run into
connection pooling issues.

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

 

[Guest]

Dear Hermit,
Thanks for the suggestion; I look into what I can do with DataSets...

Thanks,
John

 

[Hermit Dave]

You are welcome. Good luck with that.

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

 

[Guest]

I tried using a DataSet instead of a DataReader but I'm still running into
the same problem where, when I do a PostBack, the DataSet is re-declared with
"Dim" and the data is lost. Is it necessary to always re-declare the DataSet
object on a PostBack?

Thanks,
John

 

[Hermit Dave]

any variable you declare within the codebehind class is released when the
server is done processing.

if the data is a part of datagrid then you should iterate the datagrid
during the postback and then fire away whatever functions you need to call.

yes even the dataset will be released when the page is finish processing. if
you want it in the memory then consider using

A. Session (if the data is user specific)

B Cache or Application (if it is non user specific data)

you could also store the data in viewstate but i would advise it as a large
record would mean a lot of traffic between the client and the server.

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

 

[Guest]

Dear Hermit,
Thanks again; this particular data the user doesn't need to see, but I'll
just make the DataGrid invisible so that I can still reference it for the
data.

Thanks,
John

 

[Hermit Dave]

no problems mate.. good luck with that.

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)