Redundant ASP.NET SQL command executions.

[John Walker]

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)
"John Walker" <John Wa****@discussi ons.microsoft.c om> wrote in message
news:41******** *************** ***********@mic rosoft.com...

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

[John Walker]

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

Thanks,
John

"Hermit Dave" wrote:

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)
"John Walker" <John Wa****@discussi ons.microsoft.c om> wrote in message
news:41******** *************** ***********@mic rosoft.com...

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]

You are welcome. Good luck with that.

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)
"John Walker" <John Wa****@discussi ons.microsoft.c om> wrote in message
news:C3******** *************** ***********@mic rosoft.com...

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

Thanks,
John

"Hermit Dave" wrote:

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)
"John Walker" <John Wa****@discussi ons.microsoft.c om> wrote in message
news:41******** *************** ***********@mic rosoft.com...

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

[John Walker]

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" wrote:

You are welcome. Good luck with that.

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)
"John Walker" <John Wa****@discussi ons.microsoft.c om> wrote in message
news:C3******** *************** ***********@mic rosoft.com...

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

Thanks,
John

"Hermit Dave" wrote:

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)
"John Walker" <John Wa****@discussi ons.microsoft.c om> wrote in message
news:41******** *************** ***********@mic rosoft.com...
> 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]

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)
"John Walker" <John Wa****@discussi ons.microsoft.c om> wrote in message
news:0F******** *************** ***********@mic rosoft.com...

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" wrote:

You are welcome. Good luck with that.

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)
"John Walker" <John Wa****@discussi ons.microsoft.c om> wrote in message
news:C3******** *************** ***********@mic rosoft.com...

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

Thanks,
John

"Hermit Dave" wrote:

> 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)
> "John Walker" <John Wa****@discussi ons.microsoft.c om> wrote in message > news:41******** *************** ***********@mic rosoft.com...
> > 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
> >
>
>
>

[John Walker]

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" wrote:

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)
"John Walker" <John Wa****@discussi ons.microsoft.c om> wrote in message
news:0F******** *************** ***********@mic rosoft.com...

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" wrote:

You are welcome. Good luck with that.

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)
"John Walker" <John Wa****@discussi ons.microsoft.c om> wrote in message
news:C3******** *************** ***********@mic rosoft.com...
> Dear Hermit,
> Thanks for the suggestion; I look into what I can do with DataSets...
>
> Thanks,
> John
>
> "Hermit Dave" wrote:
>
> > 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)
> > "John Walker" <John Wa****@discussi ons.microsoft.c om> wrote in message > > news:41******** *************** ***********@mic rosoft.com...
> > > 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]

no problems mate.. good luck with that.

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)
"John Walker" <Jo********@dis cussions.micros oft.com> wrote in message
news:92******** *************** ***********@mic rosoft.com...

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" wrote:

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)
"John Walker" <John Wa****@discussi ons.microsoft.c om> wrote in message
news:0F******** *************** ***********@mic rosoft.com...

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" wrote:

> You are welcome. Good luck with that.
>
> --
>
> Regards,
>
> Hermit Dave
> (http://hdave.blogspot.com)
> "John Walker" <John Wa****@discussi ons.microsoft.c om> wrote in

message > news:C3******** *************** ***********@mic rosoft.com...
> > Dear Hermit,
> > Thanks for the suggestion; I look into what I can do with DataSets... > >
> > Thanks,
> > John
> >
> > "Hermit Dave" wrote:
> >
> > > 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)
> > > "John Walker" <John Wa****@discussi ons.microsoft.c om> wrote in

message

> > > news:41******** *************** ***********@mic rosoft.com...
> > > > 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
> > > >
> > >
> > >
> > >
>
>
>