How to avoid two DB calls during page life cycle.

[Peter Rilling]

I have an interesting issues that, although I not blocking me, is not very
performance. Maybe someone can suggest a better way of doing this.

1) I have a page that displays a grid with command links such as Delete.
Obviously if this link will delete the item of the current line.
2) The grid is initialized during the Init event so that the events can be
bound. It is my understanding that events do not get fired unless the
object structure is complete, so I do this in the Init. A call to the
database gets the list of current items.
3) Then the command event fires and I make a call to the database to remove
the item.
4) On PreRender, I then query the database again for the list and build it.
This is because the currently bound data is for the list containing the item
that was just removed. I need to refresh it.

As you can see, I am querying the database twice, when the page first
initializes, then before it renders. Do I have to do it this way, or is
there some way to only query the database one.

 

[Eliyahu Goldin]

Why do you to query in PageLoad on postback? BTW, PreRender event is not
good for getting data. PreRender is meant to be the very last stop before
rendering, when all data is already there.

Eliyahu

 

[Kevin Spencer]

Okay, you fetch data to "get the list of current items." Later, you fetch
the same data to bind to the list. Am I following you so far? And you want
to avoid the second db call?

The Page is a class. A class can have fields and/or properties which are
global to the class, right? So, if you were to fetch the data the first
time, store it in a private field, you should be able to fetch it from that
field the second time, right?

I mean, what you're describing is like getting your morning paper, looking
over the headlines, throwing it away, and then buying a paper on the way to
work so you can read it at lunch. The remedy is not to throw away the paper
in the first place.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.

 

[Peter Rilling]

The list that I am loading is a DataGrid with a Delete command column.
Unless I don't understand the way the server works, I believe that in order
for the events to be dispatched, the grid must be fully populated. I know
this is the case with other controls such as listboxes. If the item does
not exist in the grid when events are handled, the DeleteCommand event will
not fire. That is why I populate it here.

Now the DeleteCommand event if fired, which will go out to the database and
remove the item. The item no longer appears in the database. So, the grid
and the list from the database no longer match.

Now, I update the grid again with the new list of items since there is one
fewer. That is why I repopulate it in the PreRender.

It is not like I am loading the same list twice. I understand that is bad.
I am loading the grid twice with different results (the second has one less
item then the first). Is there a better way of handling this scenario?

 

[Kevin Spencer]

It is not like I am loading the same list twice. I understand that is

bad.
I am loading the grid twice with different results (the second has one
less
item then the first). Is there a better way of handling this scenario?

So, what you're telling me is that the 2 result sets are the same, except
that the second doesn't have one record that is in the first, right?

Now, hitting a database is one of the most expensive operations you can
perform. Hitting it twice to get the same data is just a crime. Note that
you're not getting different data. You're getting the same data, with one
less row.

If you look in the .Net SDK's reference for the DataGrid.DeleteCommand
event, you'll see an example, In the example, the data is fetched one time,
and stored as a DataTable in Session. A Custom DataView is created for
displaying the data. When the DeleteCommand is received, the DataView
removes the record from the DataTable. No repeat visit to the database is
necessary.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.

 

[Peter Rilling]

I see, so basically I would delete it from two places, the DataTable and the
database itself. Makes sense.